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...
Hi, sorry yes the characters are in 3 separate cells with headings. Are you inputting the formula into highlight cell rules and then more rules? It doesn’t seem to do anything when I input the formula
I'm sorry, I just noticed I left the SUMPRODUCT() >1 off (thx Quadruple). try it now:
=SUMPRODUCT(--((LEFT($A1,1)&LEFT($B1,1)&$C1) = (LEFT($A:$A,1)&LEFT($B:$B,1)&$C:$C)))>1
but next question is if those dates are hand entered as a date or just text. If they are a date is there any chance they have a time also in which case you may need to replace each reference to C with INT($C1) to strip off the fractional time portion
=SUMPRODUCT(
(LEFT($A1,1)&LEFT($B1,1)&INT($C1)) = (LEFT($A:$A,1)&LEFT($B:$B,1)&INT($C:$C))
)>1
- Hi, no the dates have no time. The first name is in column A2, surname in column B2 and the d.o.b in column C. The above is not highlighting anything even when I change the cells to A2 etc
- 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.- 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.