Mar 01 2023 10:45 AM
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
Mar 01 2023 12:36 PM
What do you want the result to be? Should matching names be highlighted? Or names that have no match? Or something else?
Mar 01 2023 01:37 PM
@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.
Mar 01 2023 01:52 PM
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.