I have an uniform ID system throughout all the files, except with multiple entries and differing data named columns. I would like to create a master excel sheet with specific data columns from each excel file. How does one go about doing this?
-
1What have you researched or attempted so far and where have you gotten stuck?– CharlieRBJul 2, 2014 at 16:32
-
possible duplicate of How can I merge hundreds of Excel spreadsheet files?– CharlieRBJul 2, 2014 at 16:36
-
@Charlie I believe that it’s not a duplicate. I’m having trouble understanding this question, but I believe that it’s saying that he has one sheet with columns “Country”, “Population”, “Exports”, and “GNP”, and another with columns “Country”, “Population”, “Flag Colors”, and “Capital City”. So he has to merge the sheets, using the ID (Country) column as a merge key (I believe the other question was just a concatenation of disjoint, non-overlapping sheets) and create a union (superset) of all the columns (definitely not a part of the other question, where all the files were formatted the same).– Scott - Слава УкраїніJul 2, 2014 at 23:38
Add a comment
|
1 Answer
I would use the Power Query Add-In. I would import the data from each Excel files into separate Queries, use the "Merge" command to join them together, probably using your common ID column.
http://office.microsoft.com/en-au/excel-help/merge-queries-HA104149757.aspx?CTT=5&origin=HA103993872
Power Query has lots of other data-massaging features that might come in handy along the way.
I would drop the final Query result (only) into an Excel table.
answered Jul 3, 2014 at 1:02
