Consolidation Table from Different Excel Tabs with Different Format

[alex_n]

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 consolidates all but certain columns only: WBS code, WBS description, source type, location, source description, quantity, total price. I want the consolidation table to be updated instantly with every single update that happens to each of the 5 tables.

My preference is to have it formula driven as i want to have readily available in a tab without running macros. But if there is no other options, then VBA is the way.

Thank you in advance,
A.

[#2]

@alex_n 

Hello! You've posted your question in the Tech Community Discussion space, which is intended for discussion around the Tech Community website itself, not product questions. I'm moving your question to the Microsoft Excel space - please post Excel questions here in the future. 

[#3 · Accepted solution]

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.

[#4]

@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

[#5]

Thank you, Eric. Will do.

[#6]

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 VBA

[#7]

@Wyn 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.

[#8]

Hah! That's some crazy fancy formula writing @mtarler  :beaming_face_with_smiling_eyes:, nice use of LET and Dynamic Array functionality.  Also with the benefit it will work in Excel online.

Hopefully @alex_n and any other user of the file has the Dynamic Array version of Excel with LET

Personally I'd recommend the Power Query Approach if at all possible from a simplicity and robustness viewpoint.  You could then add a a simple Macro that triggers a refresh when consolidation page is selected.

[#9]

@Wyn Hopkins  and @mtarler  you are right, power query is the way here. Our client is very sensitive for their proposal formats. It is not blocked, but rather not allowed by our regulations.

I will find out from the corporate if we can use power query.

I will mark your answers as Answer to my question.

Thanks,

A.