Using "Contains" or Wildcard to search for duplicates

KaraG

I am currently using COUNTIFS to find and flag duplicate persons by a match of both last and first name with this formula:

=IF([Last Name]@row <> "", IF(COUNTIFS([Last Name]:[Last Name], =[Last Name]@row, [First Name]:[First Name], =[First Name]@row) > 1, 1), false)

I want to change it so that it flags fields containing the same first and last name. So for example, Jane Smith and Jane Smithson would be flagged as duplicates. I'm doing this because we are accepting records from users that sometimes have errors in them (an extra space after the name for example, or a hyphenated name where our other record only includes one part of it.) I want to catch those. What is the best way to do this?

Leibel S

@KaraG

=IF([Last Name]@row <> "", IF(COUNTIFS([Last Name]:[Last Name], CONTAINS([Last Name]@row, @cell), [First Name]:[First Name], CONTAINS([First Name]@row, @cell)) > 1, 1), false)

Leibel S

@KaraG

=IF([Last Name]@row <> "", IF(COUNTIFS([Last Name]:[Last Name], CONTAINS([Last Name]@row, @cell), [First Name]:[First Name], CONTAINS([First Name]@row, @cell)) > 1, 1), false)

KaraG

Thank you! That works great! I don't have a good handle on how to use @cell, I think that was tripping me up.