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
> 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
[quoted text clipped - 27 lines]
>
> Does this make sense? Is it possible to do this? Thanks!
kardypaine - 07 Mar 2008 15:30 GMT
Pete -
That is a great help. Thanks!
Pete_UK - 07 Mar 2008 15:54 GMT
You're welcome - thanks for feeding back.
Pete
> Pete -
>
> That is a great help. Thanks!