Check one list of names against another list of names

[n2t0410]

I am checking a user list (1300 records) against a master personnel roster list (2500 records). I am now identifying source and merging the two lists then sorting by Last Name - First Name - Source. Then comparing for exact matches - Last & First. Segregating the exact matches and comparing remaining list by LAST name to Last name along with a trim of the First names to 1st letters. After that the process is visually inspecting for matches.

I am looking for any simplification of the above.

 

[yippie_ky_yay]

This may not be a popular solution in this forum - but if you were to create 2 tables in Access from the two spreadsheets you could finish quickly!

Create two tables in Access - tblUser and tblMaster. Have two columns in each and call them PERSON_ID (set to auto-number, primary key) and PERSON_NAME (Text) respectively.

Go to your spreadsheet and copy all the user names from your User name spreadsheet. Open tblUser in Access, click on the column name PERSON_NAME and Ctrl-v (to paste). All of the records should be created automatically. Do the same for the master one.

Now, go to Queries. Select "New" - click "ok" to go to design view and then click "Close" for the next option. Click the "SQL button in the top left corner.

Type in the following sentence to retrieve all the people in the Users table who are not in the master:
Select * FROM tblUsers WHERE PERSON_NAME NOT IN (SELECT PERSON_NAME FROM tblMaster);
OR
If you want all the people in both lists, drop the word "NOT"
ie Select * FROM tblUsers WHERE PERSON_NAME IN (SELECT PERSON_NAME FROM tblMaster);

Switch the table names around in your SQL statement if you wanted the reverse.

Hope this helps and I assure everyone that I mean no offence by providing an Access solution here in an Excel forum - I know there are people out there who could solve this in Excel, I just provided this solution in case it was too complex for an average user of Excel (like me)

Cheers and good luck,

-Sean