Check one list of names against another list of names

n2t0410

New Member
Joined
Oct 24, 2002
Messages
1
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.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

yippie_ky_yay

Board Regular
Joined
Jun 25, 2002
Messages
63
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
 

Forum statistics

Threads
1,143,750
Messages
5,720,633
Members
422,294
Latest member
Gemini774

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top