Combine to Sheets...

[JFALK]

I have two spreadsheets. One has a list of names in one column and an ID number in another column. The other sheet has a list of names in one column and a second ID number in the other.

I need to get the names in one column, the first ID number in the second, and the other ID in the third column. Here comes the problem. There are more than 2000 rows in the first sheet and more than 6500 rows in the second, so there are going to be names that won't get both numbers. Some will get only the first ID and some will only get the second ID. I'm guessing about 2000 lines will have all three items in them.

So, lay it on me, gurus! This has me beat!

[Eddie O]

The first thing you need to do is create a complete list of distinct names by
copying the names from both sheets into ONE COLUMN of a third sheet (one set
of data right below the other), and then using Excel's advanced filter to
return distinct records.

Having gotten your list of distinct names (and put them in let's say column
A), you can now do VLOOKUPs against the 2 sheets. A VLOOKUP in column B
could fetch the ID from one sheet, and another in column C could fetch from
the other sheet. Because some names won't have both IDs, you may want to
hide the inevitable "#N/A" errors by using a nested IF, like
=IF(ISNA(VLOOKUP(A1,'Sheet1'!A:B,2,false)),"",VLOOKUP(A1,'Sheet1'!A:B,2,false))

Eddie O

"JFALK" wrote:

>
> I have two spreadsheets. One has a list of names in one column and an ID
> number in another column. The other sheet has a list of names in one
> column and a second ID number in the other.
>
> I need to get the names in one column, the first ID number in the
> second, and the other ID in the third column. Here comes the problem.
> There are more than 2000 rows in the first sheet and more than 6500
> rows in the second, so there are going to be names that won't get both
> numbers. Some will get only the first ID and some will only get the
> second ID. I'm guessing about 2000 lines will have all three items in
> them.
>
> So, lay it on me, gurus! This has me beat!
>
>
> --
> JFALK
> ------------------------------------------------------------------------
> JFALK's Profile: http://www.excelforum.com/member.php...o&userid=24728
> View this thread: http://www.excelforum.com/showthread...hreadid=382937
>
>

[JFALK]

This is perfect! Thank you SO much!

The first thing you need to do is create a complete list of distinct names by
copying the names from both sheets into ONE COLUMN of a third sheet (one set
of data right below the other), and then using Excel's advanced filter to
return distinct records.

Having gotten your list of distinct names (and put them in let's say column
A), you can now do VLOOKUPs against the 2 sheets. A VLOOKUP in column B
could fetch the ID from one sheet, and another in column C could fetch from
the other sheet. Because some names won't have both IDs, you may want to
hide the inevitable "#N/A" errors by using a nested IF, like
=IF(ISNA(VLOOKUP(A1,'Sheet1'!A:B,2,false)),"",VLOOKUP(A1,'Sheet1'!A:B,2,false))

Eddie O

"JFALK" wrote:

>
> I have two spreadsheets. One has a list of names in one column and an ID
> number in another column. The other sheet has a list of names in one
> column and a second ID number in the other.
>
> I need to get the names in one column, the first ID number in the
> second, and the other ID in the third column. Here comes the problem.
> There are more than 2000 rows in the first sheet and more than 6500
> rows in the second, so there are going to be names that won't get both
> numbers. Some will get only the first ID and some will only get the
> second ID. I'm guessing about 2000 lines will have all three items in
> them.
>
> So, lay it on me, gurus! This has me beat!
>
>
> --
> JFALK
> ------------------------------------------------------------------------
> JFALK's Profile: http://www.excelforum.com/member.php...o&userid=24728
> View this thread: http://www.excelforum.com/showthread...hreadid=382937
>
>