Forum Discussion
Script to Compare Names
What do you want the result to be? Should matching names be highlighted? Or names that have no match? Or something else?
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.