Forum Discussion
Script to Compare Names
HansVogelaar 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.
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.