Forum Discussion
Finding duplicates
Is there a way to find duplicates based on initials and date of birth? For example: Joe Bloggs 16/01/1977 Susan Smith 14/4/1987 Brian Red 07/08/1957 Jake Boss 16/01/1977 So a formula that...
=SUMPRODUCT(N(LEFT($A$1:$A$4,1)=LEFT(A1,1))*(MID($A$1:$A$4,FIND(" ",$A$1:$A$4)+1,1)=MID(A1,FIND(" ",A1)+1,1))*(RIGHT($A$1:$A$4,LEN($A$1:$A$4)-FIND(" ",$A$1:$A$4,FIND(" ",$A$1:$A$4)+1))=RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(" ",A1)+1))))>1An alternative could be this rule for conditional formatting if name and birthday are in one cell.
OliverScheurich you should be able to simplify that based on the date format being exactly 10 characters (and combining the terms into a single comparison string):
=SUMPRODUCT((LEFT($A:$A,1)&MID($A:$A,FIND(" ",$A:$A)+1,1)&RIGHT($A:$A,10))= (LEFT(A1,1)&MID(A1,FIND(" ",A1)+1,1)&RIGHT(A1,10)))>1
