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

%3CLINGO-SUB%20id%3D%22lingo-sub-2216240%22%20slang%3D%22en-US%22%3EConsolidating%20data%20from%20multiple%20worksheets%20into%20new%20work%20book%20based%20on%20unique%20identifiers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2216240%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20have%20an%20excel%20with%204%20different%20tabs%20with%20dates%20and%20names%20and%20amounts.%20I%20need%20to%20consolidate%20data%20based%20on%20unique%20identifiers%20from%20each%20sheet%20into%20new%20workbook.%20I%20need%20the%20entire%20row%20to%20be%20in%20the%20new%20work%20book.%20Please%20see%20the%20attached%20file.%26nbsp%3B%20The%20unique%20identifiers%20are%20Entries%20in%20Column%20%22SITE%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2216240%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Contributor

Hello,

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

@ravikumarnv60 

Spoiler
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.
https://drive.google.com/open?id=0Bw_...    (find file exambles, have also main file with vba code  included ).
Video Link (with VBA): https://www.youtube.com/watch?v=l4b9pFu9Px8&t=105s
Video Link (with Power Query):  https://www.youtube.com/watch?v=wKy4kT98tJY
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.


Nikolino

I know I don't know anything (Socrates)

 

 

Mehrere Excel Dateien zusammenführen ohne jede Datei zu öffnenTestdaten herunterladen: https://drive.google.com/open?id=0Bw_8Ozc4aKi9ZU9oWDBNUGxRczQCode:http...
In diesem Video zeige ich dir anhand einem Beispiel wie man in Excel automatisch Daten aus unterschiedlichen Quellen zu einer einzelnen Tabelle zusammenführe...