Forum Discussion

DylanTarget's avatar
DylanTarget
Copper Contributor
Mar 01, 2023

Script to Compare Names

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

 

 

excel
Formulas and Functions
Macros and VBA

3 Replies

Sort By
    • DylanTarget's avatar
      DylanTarget
      Copper Contributor
      Mar 01, 2023

      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.

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Mar 01, 2023

        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.

Resources