Consolidating data from multiple worksheets into new work book based on unique identifiers

Occasional Contributor


I have an excel with 4 different tabs with dates and names and amounts. I need to consolidate data based on unique identifiers from each sheet into new workbook. I need the entire row to be in the new work book. Please see the attached file.  The unique identifiers are Entries in Column "SITE".


2 Replies
try power query, it will append all data, then you can clean the data


As was already offered to you by chahine as a proposed solution, pivot query.
Pivot cross is the most elegant solution.
But it also works with VBA.
Here links and files as an example with the possibility.    (find file exambles, have also main file with vba code  included ).
Video Link (with VBA):
Video Link (with Power Query):
Videos are in German.

P.S. The third-party products that this article discusses are manufactured by companies that are independent of me. I makes no warranty, implied or otherwise, about the performance or reliability of these products.

I would be happy to know if I could help.


I know I don't know anything (Socrates)