Forum Discussion
Bob_lponge
Mar 02, 2022Copper Contributor
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...
mtarler
Mar 04, 2022Silver Contributor
hmm I missed adding --() around the conditional to force it as a value, maybe that was the problem. It is always easier when we have a sample file to test on. Try this:
=SUMPRODUCT(--((LEFT($A1,1)&LEFT($B1,1)&$C1) = (LEFT($A:$A,1)&LEFT($B:$B,1)&$C:$C)))>1
and it doesn't matter where the first name is, it matters the range that the conditional formatting is applied.
=SUMPRODUCT(--((LEFT($A1,1)&LEFT($B1,1)&$C1) = (LEFT($A:$A,1)&LEFT($B:$B,1)&$C:$C)))>1
and it doesn't matter where the first name is, it matters the range that the conditional formatting is applied.
Bob_lponge
Mar 08, 2022Copper Contributor
Hi, I don’t have a current sample file, I’m just looking for potential methods for an upcoming project. The above still does not seem to highlight any values in the example I initially gave.