Forum Discussion
Solved
Consolidation Table from Different Excel Tabs with Different Format
Hi all! I have 5 (source) tabs with tables with data but in different formats. They all have common data but column headers are named differently. I want to have a separate tab (consolidation) that ...
- Almost certainly this is possible.
One solution may be to use Power Query to add the tables to the data model, link the appropriate columns and then create an output table.
Another option is to use Dynamic Array formulas to combine all the information
A third and possible best option is to create a master table with all the data and then have those other 5 tables actually pulled out from the master table based on some criteria (either with pivot table or DA formulas).
It is hard to know more of the details without a sample file.
Almost certainly this is possible.
One solution may be to use Power Query to add the tables to the data model, link the appropriate columns and then create an output table.
Another option is to use Dynamic Array formulas to combine all the information
A third and possible best option is to create a master table with all the data and then have those other 5 tables actually pulled out from the master table based on some criteria (either with pivot table or DA formulas).
It is hard to know more of the details without a sample file.
One solution may be to use Power Query to add the tables to the data model, link the appropriate columns and then create an output table.
Another option is to use Dynamic Array formulas to combine all the information
A third and possible best option is to create a master table with all the data and then have those other 5 tables actually pulled out from the master table based on some criteria (either with pivot table or DA formulas).
It is hard to know more of the details without a sample file.
mtarlerThanks for the response. The tabs are stand alone tabs with different source of input and therefore cannot be used as one table.
Here is the link to the onedrive folder, filename is Sample.
In the sample I am using 4 source tables. Consolidation table (consolidation tab) must be instantly updated every time there is a change in any of the source tables.
https://1drv.ms/u/s!Aqv4zBSFNKaymTqUazTSHXO-2KcH?e=SPJv7z
Powerquery cannot be used as it is not allowed by the organization.
Thanks,
Alisher
- Interesting that you say Power Query is not allowed. I’m not aware of any way it can be blocked as it’s built in to Excel. Unless you have Excel 2010 or earlier ?
It is the simplest solution otherwise you need VBAWyn Hopkins that is a good point, I don't know how they would block it. but it is possible using formulas. this isn't the prettiest or most efficient but seems to work.
