Excel - How to merge rows from 2 tables based on a common column? [duplicate]
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
Solution 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.
Solution 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 ...
Solution 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.