Finding partial duplicates

I have an Excel sheet of 200,000+ names from my work's client list. I am trying to find duplicate accounts. I have used conditional formatting to narrow the list down to people with identical birthdays. In some cases a client gets married and then creates a new account with their new last name. We only need to have one account for each person.

Is there a formula or way to have Excel show me clients that have a partial match in their name (eg: Johnson, Kate and Loucks, Kate) and share an identical birthday?

Assuming A is names, B is birthdays

Then in C:

=if(countifs($a$2:$a$200000,"*"&mid(a2,find(",",A2)+2,99)&"*",$b$2:$b$200000,b2)>1,"Possible Duplicate","") and copy downwards

Originally Posted by daffodil11

Assuming A is names, B is birthdays

Then in C:

=if(countifs($a$2:$a$200000,"*"&mid(a2,find(",",A2)+2,99)&"*",$b$2:$b$200000,b2)>1,"Possible Duplicate","") and copy downwards

Thank you for the response. I copied the formula down and it puts in the column with the formula numbers like 1,0,68. What do those mean? Am I doing this right? Thanks again.