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...
Let's say the names are in A2 and down, and the birth dates in B2 and down.
If necessary, insert an empty column in column C.
Enter the following formula in C2:
=LEFT(A2)&MID(A2,FIND(" ",A2)+1,1)&B2
You'll see something like JB28141. 28141 is the numeric representation of 16/01/1977.
Fill down.
Select column C.
On the Home tab of the ribbon, select Conditional Formatting > Highlight Cells Rules > Duplicate Values... and click OK.