Find Duplicates

[kbnetguy]

I have Column A "Fist Name" and Column B "Last Name". For example

FIRST NAME	LAST NAME
Arpil & Pat	Abernathy
Pat	Abernathy
David	Ackary
David	Ackary
Karen	Smith
Bob	Barker
Ellen	Barker
Jack	Smith
David	Brady

<tbody>
</tbody>

I want to highlight or indicate in a 3rd column the records that Are Either an exact match like in rows 3 "David Ackary" and row 4 "David Ackary"

AND I would also like to identify those records that COULD be a match like row 1 "April & Pat Abernathy" and row 2 "Pat Abernathy"

 

[znoxide]

If you have the option to add a column, I would suggest concatenate and find duplicates. With regard to your second question, an individual can be assumed to only have one FIRST NAME without any spaces. With that said, I would recommend cleaning the data with Convert to Text using the delimiter "space". Then update the table to appropriately separate April Abernathy and Pat Abernathy.

=CONCATENATE(A2," ",B2)

 

[tonyyy]

kbnetguy,

If you're comfortable with a vba approach, you might consider the following...

Sub FindDuplicates_1062243()
Dim arr() As Variant
Dim LastRow As Long, r As Long, r1 As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
ReDim arr(1 To LastRow, 1 To 3)
arr = Range("A1:C" & LastRow)

For r = 2 To UBound(arr)
    For r1 = r + 1 To UBound(arr)
        If arr(r, 1) & arr(r, 2) = arr(r1, 1) & arr(r1, 2) Then
            arr(r, 3) = "rows " & r & ", " & r1
            arr(r1, 3) = "rows " & r1 & ", " & r
        ElseIf arr(r, 2) = arr(r1, 2) And (InStr(arr(r, 1), arr(r1, 1)) > 0 Or InStr(arr(r1, 1), arr(r, 1)) > 0) Then
            arr(r, 3) = "rows " & r & ", " & r1
            arr(r1, 3) = "rows " & r1 & ", " & r
        End If
    Next r1
Next r

arr(1, 3) = "Duplicate Names"
Range("A1:C" & LastRow).Value = arr
Columns(3).AutoFit
End Sub

Cheers,

tonyyy

 

[Christopher12]

Hi tonyyy

How about the above example and we have words in same cell separated with comma but no space?

FIRST NAME	LAST NAME
Arpil & Pat	Abernathy
Pat,John	Abernathy
David,Will	Ackary,John
David,Jake	Ackary,Jake

<tbody>
</tbody>