Combining worksheets

[kardypaine]

I want to take multiple Excel worksheets and combine the data in them,
keyed off of a particular column entry (an id #). For instance, if I
have a worksheet with columns for i.d.# and name, and another
worksheet with columns for i.d.# and age, I would like to be able to
combine them so that the age column of data is added to the first
worksheet. Obviously, if each worksheet contained the same list of
i.d.#'s, then I could just sort and cut and paste. However, the
second sheet might contain extra i.d. numbers and/or not have i.d.
numbers that the first sheet has.

For instance, Worksheet 1:

1 Dave
2 Sally
3 Mark
4 Jack

Worksheet 2

1 35
2 24
4 29

I want to combine these and end up with

1 Dave 35
2 Sally 24
3 Mark
4 Jack 29


Does this make sense? Is it possible to do this? Thanks!

 

[Pete_UK]

Yes, you can do this, but you said that you might have IDs in Sheet2
which are not in Sheet1, so in that case it is probably better to
combine the data from the two existing sheets into a third sheet.

First of all, obtain a combined list of unique IDs. To do this, copy
the first list of IDs into column A of the new sheet. You also need to
have a header, so if you don't have one then insert a new row 1 and
put "ID#" in A1. Then copy the list of IDs from the second sheet
immediately below the list from Sheet1, so that they are all now in
column A. Then highlight all the IDs, including the header, and click
on Data | Filter | Advanced Filter, and in the panel that pops up you
should click on Unique Records Only, Copy to another location, and
enter C1 as the destination. Click OK, and you will have your unique
list in column C. You can delete columns A and B, and you might like
to sort the unique IDs (now in column A). Put "Name" in B1 and "Age"
in C1 as headers, and then these formulae in the row below:

B2: =IF(ISNA(VLOOKUP(A2,Sheet1!A:B,2,0)),"",VLOOKUP(A2,Sheet1!A:B,
2,0))

C2: =IF(ISNA(VLOOKUP(A2,Sheet2!A:B,2,0)),"",VLOOKUP(A2,Sheet2!A:B,
2,0))

Notice that the first formula is getting data from Sheet1 and the
second one from Sheet2. All you need to do now is copy the two
formulae down columns B and C of the new sheet for as many rows as are
necessary. If any of the data is missing for a particular ID, then you
will have a blank cell there.

Hope this helps.

Pete

 

[kardypaine]

Pete -

That is a great help. Thanks!

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete