Excel - How to merge rows from 2 tables based on a common column?

Question

I am trying to merge 2 worksheets where only one col matches in content.

Column"A" in both worksheets have matching values but all other fields are different.

Worksheet1:

Col"A" colb colc cold

(worksheet1 has 25,000 rows)

Worksheet2:

Col"A" cole colf colg

(worksheet2 has 22,000 rows - so some rows are missing)

NEW COMBINED WORKSHEET NEEDS TO LOOK LIKE THIS:

Col"A" colb colc cold cole colf colg (new worksheet has 25,000 rows)

How can I do this?

example illustration here: http://www.ablebits.com/office-addins-blog/2014/02/06/merge-rows-excel/#merge-matching-rows

Answer 1

You need to use =vlookup() for that. Copy column A from the bigger sheet into Column A of a new sheet. Then us =vlookup(A1, [Worksheet1.xls]tab1!$A$1:$D$25000, 2, false) to build Column B. Rinse and repeat for the remaining columns changing up values in the vlookup function as needed.

Answer 2

I would use the Power Query Add-In for this. It has a Merge command that can handle this requirement without writing a single formula or any code.

http://office.microsoft.com/en-au/excel-help/merge-queries-HA104149757.aspx?CTT=5&origin=HA103993872

I hope it's not too late to switch - only 5 hours have passed so you are probably still editing vlookup formulas ...

Answer 3

You can use Query from Excel Files :

• Define name for primary table dataset - Worksheet1 (Formulas tab -> Define name)
• Define name for secondary table dataset - Worksheet2
• Go to Data tab, select "From Other Sources", and from the dropdown, select "From Microsoft Query"
• Select your workbook file and confirm that you want to merge the columns manually
• In the following window "Query from Excel Files", drag&drop the Col "A" of first dataset into the Col "A" of second dataset - a link between these columns will be created
• Go to File menu, click "Return Data to MS Office Excel", an Import Data dialog will pop up
• Select the sheet into which you would like the matched data to be imported
• Click OK -> you should see matched data with columns from both tables

Or if you don't mind uploading your files to an online service, you can use for example http://www.gridoc.com/join-tables and merge the tables using drag&drop (Disclaimer: I am author of the tool).

Hope this helps.

Answer 4

Not sure if this is what you're after, but I would copy and paste the entire contents of both worksheets into one worksheet or new workbook, sort, and then use Excel's delete duplicates function, checking the column labels you want Excel to use for removing duplicates.