Hye Excel experts...

jacksonpt · 06-10-2005, 08:43 AM

Anyone know how to compare 2 lists in excel? I've got a list of names and social security numbers in one list, and another list with social security numbers and e-mail addresses. The first list is a subset of the second (i.e. the second list is everyone we have on file, the first list is only those people who meet certain criteria). I need to bump the first list against the second list so I can get the e-mail addresses for the people on the first list.

Make sense? Anyone know how to do this?

This ad is not displayed to registered members.
Register your free account today and become a member on Yotatech!

Bob_98SR5 · 06-10-2005, 12:58 PM

jackson,

how many records are we talking about here? less than 100? more than 1000? 5000?

If its not a huge list, the easiest thing to do is to do a "sort" by the ssi # since that is the only common set of data. export the data into a new spreadsheet and place the data in columns like so:

Col1 Col2 Col3 Col4
names SSI SSI Email

Then sort the data. Surround both Cols 1 and 2 and then:
Data > Sort > and then select Col 2. Ascending is a good choice

Do the same for Cols 3 and 4. Then visually compare the list

If its a list of a couple thousand, I'll think a little to see what is the best way. If your looking for soemthing completely automated that will match records and then assemple the emails, names and SSI, that will be more difficult

bob

tulsa_97SR5 · 06-10-2005, 03:26 PM

the vlookup function will do it. highlight the second set of data & name that range something, like 'data'.
in the first empty column on the first sheet use vlookup, formula should be something like
=vlookup(a2, data, 2, false)
where a2 is the cell with the ssn you want to find a match for, data is the name of the second block of data, 2 is the column in data that has email addresses, and false tells it to only return exact matches.

edit: the named range needs to have the ssn in the leftmost column of the named range. PM me if this doesn't make sense.

It's my favorite excel function :-)

jacksonpt · 06-13-2005, 06:21 AM

Thanks for the tips guys... I ended up doing it in access using a query. There were about 1500 records - no way was I going to do that manually.

06-10-2005, 12:58 PM	#2 (permalink)
Bob_98SR5 Contributing Member Join Date: May 2002 Location: Los Angeles Posts: 10,092	jackson, how many records are we talking about here? less than 100? more than 1000? 5000? If its not a huge list, the easiest thing to do is to do a "sort" by the ssi # since that is the only common set of data. export the data into a new spreadsheet and place the data in columns like so: Col1 Col2 Col3 Col4 names SSI SSI Email Then sort the data. Surround both Cols 1 and 2 and then: Data > Sort > and then select Col 2. Ascending is a good choice Do the same for Cols 3 and 4. Then visually compare the list If its a list of a couple thousand, I'll think a little to see what is the best way. If your looking for soemthing completely automated that will match records and then assemple the emails, names and SSI, that will be more difficult bob Last edited by Bob_98SR5; 06-10-2005 at 12:59 PM.

06-10-2005, 03:26 PM	#3 (permalink)
tulsa_97SR5 Contributing Member Join Date: Feb 2004 Location: tulsa, OK Posts: 1,329	the vlookup function will do it. highlight the second set of data & name that range something, like 'data'. in the first empty column on the first sheet use vlookup, formula should be something like =vlookup(a2, data, 2, false) where a2 is the cell with the ssn you want to find a match for, data is the name of the second block of data, 2 is the column in data that has email addresses, and false tells it to only return exact matches. edit: the named range needs to have the ssn in the leftmost column of the named range. PM me if this doesn't make sense. It's my favorite excel function :-) __________________ Brian 85 sr5, 5spd - back on the road, doing DD duty for now 86 2wd reg cab - future project 90 sr5, 97 - gone Last edited by tulsa_97SR5; 06-10-2005 at 03:27 PM.

06-13-2005, 06:21 AM	#4 (permalink)
jacksonpt Senior Member Join Date: May 2002 Location: Binghamton, NY Posts: 2,753	Thanks for the tips guys... I ended up doing it in access using a query. There were about 1500 records - no way was I going to do that manually. __________________ Jackson - My Site 96 SR5 4x4 - 33" MTs, open diffs, and 3.90s - LET'S ROCK! My runner has bee traded in sniff on an 06 SR5 long bed d-cab Tacoma. The tacoma is nice, but... sniff ... I'll update my site when I'm done grieving.

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Need help with Word/Excel Plzz	Stive2699	Off Topic Talk	2	01-13-2007 05:14 PM
Excel help! How to create legend title?	DH6twinotter	Computer Talk	7	03-23-2006 10:24 PM
need help with simple excel workings.	bflooks	Computer Talk	3	11-15-2005 04:15 AM
Fina excel???	smiljko	Maintenance & Repair Archives	0	11-03-2004 04:55 AM
Excel Macro and VBA Gurus.....need some help!	MTL_4runner	Off Topic Talk	6	05-15-2004 07:14 AM