0

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!

1
  • @nbk thank you for editing to present the tables properly, looked fine in the preview.
    – Ad Astm
    Commented Apr 6, 2021 at 14:25

1 Answer 1

0

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.

1
  • Thank you @muhammad-jamali! With the "join" term I found how to do this in Excel too. There are many online guides for using PowerQuery to merge tables together. For older versions of Excel, PowerQuery is available as a free add-in from Miscrosoft.
    – Ad Astm
    Commented Apr 7, 2021 at 15:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.