Consolidate / sort / combine / merge data for dummies

All we need is an easy explanation of the problem, so here it is.

I am looking for a non-scripting solution to combine data from 3 different tables (preferably using spreadsheet software like Microsoft Excel but a database would also work e.g. Microsoft Access). I have tried searching for an answer but have not managed to find anything useful.

So, how do I take these 3 tables…

Table 1: 4 most popular past 1 day

|Label    |Frequency|Rank|
|---------|---------|----|
|Apples   |2        |4   |
|Pears    |5        |2   |
|Oranges  |10       |1   |
|Kiwis    |3        |3   |

Table 2: 4 most popular past 7 days

|Label    |Frequency|Rank|
|---------|---------|----|
|Apples   |20       |3   |
|Pears    |50       |2   |
|Plums    |100      |1   |
|Oranges  |10       |4   |

Table 3: 4 most popular past 30 days

|Label    |Frequency|Rank|
|---------|---------|----|
|Apples   |550      |4   |
|Pears    |650      |3   |
|Plums    |1000     |1   |
|Mangoes  |990      |2   |

…and automatically merge them into…

Table 4: combined table

|Label    |30 days  |    |7 days   |    |1 days   |    |
|---------|---------|----|---------|----|---------|----|
|         |Frequency|Rank|Frequency|Rank|Frequency|Rank|
|Apples   |550      |  4 | 20      |3   |2        |4   |
|Pears    |650      |  3 | 50      |2   |5        |2   |
|Plums    |1000     |   1| 100     |1   |1        |    |
|Kiwis    |         |    |         |    |3        |3   |
|Mangoe   |990      |2   |         |    |         |    |
|Oranges  |10       |    | 10      |4   |10       |1   |

As, you can see, Table 4 comprises the columns from Tables 1, 2, and 3. No mathematical operations are performed.

Where there is an entry that does not have data, the cell is left blank, for example, I just got some kiwis in the shop to sell yesterday and they sold very well and got a rank but because I only just got the kiwis yesterday there is no data for them selling over the past 7 days and past 30 days. Similarly, there have been no mangoes in the shop to sell in the past 1 day or 7 days but actually they have sold very well over the past 30 days.

Please not, as far as I can tell the "Consolidation" routine in Microsoft Excel does not do what I am looking for.

Thank you!

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You want to merge the data, in database terms it is called joining tables. If you want it non-script please refer here
to get a understanding how to do it non-scripted on MS-Access.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply