Finding duplicate name and date of birth

jcasorla1

Hi, I have a sheet with demographics date. I created a helper column to find if the combined name and date of birth are duplicates, it works when the name is exactly the same, but when there's a slight difference in the name, say one has a middle initial and the other doesn't have one, it won't be flagged as duplicate. This is my formula: =IF(COUNTIFS([Member Name]:[Member Name], [Member Name]@row, DOB:DOB, DOB@row) > 1, 1, 0)

How do I make it so it will still flag as duplicate? Thank you.

John C Murray

Essentially you can't. It's not a problem with Smartsheet, there's simply no way to know if "John Murray 1920" is the same person as "John C Murray 1920" or a different person. Human intervention will always be needed to resolve this type of issue.

A third criterion "might" help, but it would have to be a strong one that has only one permutation, like licence number. It cannot be weak, like town of birth, because there's no guarantee that two different John Murray's couldn't be born in the same town on the same day.

jcasorla1

Thank you John for your help. One thing that comes to mind is they have health insurance policy numbers that can be a third criterion. But I think the downside is if someone tries to create a new record and that person has a different insurance, it won't find that duplicate. Anyway, let me try to find unique ones that I can use and can be future proof.