2

I was wondering what is the best way to merge two spreadsheets which have the name id's e.g:

One sheet has:

apple   5
dog     3
cat     2

Second sheet:

dog     4
apple   7
cat     3

I would like to merge the two so that I have

apple   5    7
dog     4    3
cat     2    3

They're not in order, and sorting them in Excel didn't help to fix the problem.

Improve this question

1 Answer 1

Reset to default
2

This is easiest done in a database using Joins, but you can do it in excel using VLOOKUP.

First create a third sheet, and paste the 'dog, cat, etc' columns from sheet one and two into a single column in sheet 3. The use Excel 2007's Remove Duplicates feature to remove duplicates.

Then in the second column, specify the formula =VLOOKUP(A1, Sheet1!A$1:B$100, 2, false) where A1 is the cell to the left of the cell youu're editing (on sheet 3) and A1:B100 is the region on sheet 1 containing data.

In the third column you specify a similar formula, but pointing to Sheet 2, =VLOOKUP(A1, Sheet2!A$1:B$100, 2, false).

You may need to sort the data in sheet 1 and 2, and the names on sheet 3.

Improve this answer
1
  • If you’re using an Excel version before 2007 "Remove Duplicates" is not available, but you can achieve the same effect from the Data -> Filter -> Advanced Filter control by getting it to copying to a new location and ticking "Unique records only".
    – DMA57361
    Commented Jan 24, 2011 at 15:18

You must log in to answer this question.

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