#1   Report Post  
JFALK
 
Posts: n/a
Default Combine to Sheets...


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

  #2   Report Post  
Eddie O
 
Posts: n/a
Default

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)),"",VLOO KUP(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


  #3   Report Post  
JFALK
 
Posts: n/a
Default


This is perfect! Thank you SO much!

Eddie O Wrote:
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)),"",VLOO KUP(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
------------------------------------------------------------------------
JFALK's Profile: http://www.excelforum.com/member.php...o&userid=24728
View this thread: http://www.excelforum.com/showthread...hreadid=382937

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
HELP!!! combine 4 different sheets ??? TonyKA Excel Discussion (Misc queries) 0 May 13th 05 03:05 PM
Can I combine 100+ sheets from different workbooks into one worksh bootstrapgroup Excel Discussion (Misc queries) 0 April 8th 05 09:17 PM
Copying multiple sheets from one book 2 another and undertake spec Pank Mehta Excel Discussion (Misc queries) 14 March 16th 05 05:41 PM
calculating excel spreadsheet files for pensions and life insurance (including age calculation sheets) RICHARD Excel Worksheet Functions 1 March 15th 05 06:49 PM
Multiple sheets selected twa14 Excel Discussion (Misc queries) 2 December 21st 04 12:15 PM


All times are GMT +1. The time now is 09:41 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"