Forum Discussion
Solved
Combining data from multiple tables according to item name
I have multiple tables containing data on exports of items in each year. I want to be able to create a consolidated table which has the item name in row while the data for exports in a particular year is in the column. My problem is the tables in each year is of a different size, since not every item is traded in all years. How do I do this?
sankalpa280 Obviously, you are already working with structured tables and Power Query. Create a query that connects to all relevant tables in the current workbook. Expand the content column and group by item. Finally create a pivoted table in PQ or a regular pivot table in Excel.
A simplified example is attached.
sankalpa280 Obviously, you are already working with structured tables and Power Query. Create a query that connects to all relevant tables in the current workbook. Expand the content column and group by item. Finally create a pivoted table in PQ or a regular pivot table in Excel.
A simplified example is attached.
- Thank you!
I am not sure I follow 100% so make a suggestion based on my guess of what you are doing. I assume you have your consolidated results in Sheet1 and all your trade records for one year in Sheet2, Sheet3, ... Suppose each trade sheet was named the same as the column header in Sheet1 in which you are trying to consolidate the result. You can then address each sheet via INDIRECT by reference to the column header. Assuming that all trading records share the same format - perhaps |Item|Quantity|Date| or similar - then the fact that the sheets contain different number of record don't need to stop you - just oversize the range. So you can write =SUMIFS(INDIRECT(colName&"!B1:B1000;colName&"!A1:A1000;rowItem).
