How do I automate a cross check between two Employee Name columns that are formatted differently?

kebern

I've inherited a large set of sheets that were set up to pull in employee info based on an employee ID number input via form. There is an INDEX:MATCH to display the employee's name, supervisor, position, etc. from an agency roster based on the ID number.

Because some employees mistype their ID number, the wrong info (or none at all) may be displayed. Someone before me added a text column for the form user to enter the employee's name in the hopes of setting up a cross check between this field and the name brought in by the ID number.

The agency roster has names formatted as "LAST, FIRST"; of course we are mostly getting people submitting "FIRST LAST" via the form field (as well as other variables such as nicknames and misspellings) eg:

(I've added helper text asking the form user to use "LAST, FIRST"—though this just means some will follow this and some won't...) I realize we could move to one field for first and one field for last name, but we would still get some discrepancies, e.g. Josh v.s. Joshua.

What is the simplest way to build an actual cross check between two Employee Name columns, perhaps looking for a partial text match, so that we can set up a workflow to notify someone when there is a mismatch? Do I need helper columns + formulas to separate first and last name and then something like nested COUNTIF/CONTAINS?

Thank you!

Gia Thinh

Hi @kebern,

The key point is that you want to prevent the form users from mistyping their ID, you can set the Employee ID column in the sheet with a dropdown list property and enable "Restrict to list values only" setting.

This way will help form users to select their right ID from a dropdown list without a typing.

Hope this works for you.

kebern

Because we have something like 500+ employees with regular turnover and a set of several dozen rubrics, unfortunately it wouldn't be manageable to keep a dropdown list of values up to date. I can see how this would be the solution in a much smaller static scenario, though, thank you!