Forum Discussion
Finding duplicates
so are those all in 1 cell, 2 cells or 3 separate cells? Lets assume 3 cells (and if not you could make them 3 cells using Text to Columns tool under the Data tab). Then under conditional formatting -> user your own formula:
=SUMPRODUCT((LEFT($A1,1)&LEFT($B1,1)&$C1) = (LEFT($A:$A,1)&LEFT($B:$B,1)&$C:$C))>1
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.