Script to Compare Names

[DylanTarget]

I'm trying to create a script that will compare names from an employee list at the company I work at.

I want to be able to compare the names of people against our employee directory.

Attached below: The names under 'Full Name (last,first)' is the full list of employees & that's how the report is generated. The names in the C/D columns is the list of employees I want to compare against the employee directory. That's also how the report is generated.

The furthest I got was to create a helper column that wrote the names as "last, first" but with the middle initial listed in the directory - it was acting like the names were different.

EX: 'Adame, Aubrey A.' was not the same as 'Adame, Aubrey' even though they are the same person.

Sorry if it's confusing on how I explained everything - basically I want a fast way of knowing if any names on the right do not match with any names on the left.

I appreciate any help I can get!

Thanks, 

Dylan

[#2]

@DylanTarget 

What do you want the result to be? Should matching names be highlighted? Or names that have no match? Or something else?

[#3]

@Hans Vogelaar If there's a way to highlight the names that don't match - that would be perfect!

That would let me know that any highlighted users will need to be removed from our system.

[#4]

@DylanTarget 

Let's say the full names are in A2:A100, and the first/last names in C2:D95

First, select A2:A100.

A2 should be the active cell in the selection.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula

=ISERROR(MATCH(1,SEARCH($C$2:$C$95&", "&$D$2:$D$95,A2),0))

Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK, then click OK again.

This will highlight the full names that don't have a match in B2:C95.

Next, select B2:C95. B2 or C2 should be the active cell in the selection.

Perform the same steps as above, but with the formula

=ISERROR(MATCH($C2&", "&$D2&"*",$A$2:$A$100, 0))

This will highlight the first and last names that don't have a match in A2:A100.