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?

 

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

 

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 :-)

 

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.