Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

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.

share|improve this answer
    
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 Jan 24 '11 at 15:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.