Forum Discussion

Copper Contributor

Mar 02, 2022

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...

Formulas and Functions

Silver Contributor

Mar 02, 2022

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

Bob_lponge
Copper Contributor
Mar 02, 2022
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
- mtarler
  Silver Contributor
  Mar 02, 2022
  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
  - Bob_lponge
    Copper Contributor
    Mar 04, 2022
    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

Resources