List help

[countryatheart]

I have two lists. One is about 70 names and the other is about 7000. I need to compare the lists to see if any of the 70 names appear on the list of 7000. Both lists are in Tab-Delimited format, however the names are in different fields (meaning that on one list it's listed as lastname,first name in one field and on the other list last name is a field and then first name is a separate field.)

Question - what's the easiest way to do this. I tried importing as two separate tables into Access and then running a query, but it's not working.

Any ideas? I'm sure it's an easy answer, but I'm drawing a blank right now.

Thanks --

[smurfy]

Hi there. Welocome to CTH.
Excel can do this.

e.g.
List 1 = 70 names, "Lastname, Firstname" = Col A
List 2 = 7000 names, "Lastname" = Col A, "Firstname" = Col B

Open the Tab Delimeted file and go through the wizard until you get list 1 on a worksheet.
Repeat steps for list 2.

Move list 2 sheet to the same workbook as list 1.
So you now have one workbook with 2 sheets, one sheet per list of names. Save the file as an xls.

On List 2, insert column to right of "Firstname" column.
Select first data row in this new column (should be Cell C2)
type:
=A2 & ", " & B2
Drag this formula down entire column C.
Hint: Double click the bottom right corner of cell C2 (when cursor appears as black cross) and it will auto-fill down entire column
This should give you a "Lastname, Firstname" field.

Go to list 1 and insert a column next to Col A.
This will be new Column B
In B2 type
=Vlookup(A2,List2sheetnamehereC1:C7000,1,FALSE)
Drag that down all 70 rows or do the double-click trick.

You will now get a column that lists all those names in List 1 that are also in list 2 with a "N/A" entry for those who are not.

Need more help? Post back.

[countryatheart]

Thank you, thank you!!! It works...the only thing I had to change was to add $ in front of the cell references that shouldn't change, but otherwise it worked like a dream.

Thanks for your help. This site is awesome! :cheesy: