2

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

Improve this question
0

4 Answers 4

Reset to default
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.

Improve this answer
2
  • Thanks. I will try this now. basically the result i want is like this... ablebits.com/office-addins-blog/2014/02/06/merge-rows-excel/…
    – user3368180
    Aug 26, 2014 at 17:32
  • Vlookup should work. The trick is that every possible value for Column A needs to exist in the new worksheet. You could copy all of them from the larger sheet, and then use a countif function on the smaller sheet to see if there are any extra values that it has that they larger sheet doesn't. Then do the vlookup.
    – JNevill
    Aug 26, 2014 at 18:49
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 ...

Improve this answer
1
  • 1
    Yes it is very cool ad-ins. Thanks!
    – Learner
    Sep 21, 2015 at 7:31
2

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.

Improve this answer
0

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.

Improve this answer
2

Not the answer you're looking for? Browse other questions tagged .