Merge multiple Excel workbooks based on key column

Question

I get seven workbooks (xlsx files) from different clients, each with one sheet.

Each sheet has at least one common ID column (UNIQ, PK). One of the workbook contains list of all possible ids. Others may not have record for all ids, but each row has id value defined.

I need to make a final workbook, with first column ID and then union of all remaining columns from each file.

Then I need to send the final.xlsx via email, so its independent of the source files (I'm not sure if its possible to dereference it while retaining the constant values).

Can we do this using VLOOKUP or built-in Excel functions?

Answer 1

If you are going to perform this operation only once. You can do it like this:

1. Go to the workbook which contains all the IDs(Workbook0)
2. Apply vlookup in Workbook0 to fetch all the values you need from workbook1
3. Repeat the process to fetch values from workbook2...3..& so on
4. Eventually you should have a workbook (workbook0) which will have all the data you want, with ID in first column & rest of the data in further columns
5. Now to de-reference it, select all the cells which contain formula & press Ctrl+C
6. Without deselecting, go to Paste -> Paste Special & select Paste Values. This will overwrite the formula cells with only values
7. Then go to Data -> Connections
8. Break any connections with other workbooks (if exists)
9. Save & you are ready to email an independent file

Also, if this is something you have to do very frequently & the steps remain same. I'd suggest that before starting, you click on Record Macro, then perform above steps, tweak that code a little, save it in some other file for future & from next time you won't have to do it manually.

Answer 2

In case you would like to try different approach, take a look at the "Excel Query Wizard" https://superuser.com/questions/722348/merge-and-combine-in-excel/747430#747430