Find Duplicates

[kbnetguy]

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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]FIRST NAME[/TD]
[TD]LAST NAME[/TD]
[/TR]
[TR]
[TD]Arpil & Pat[/TD]
[TD]Abernathy[/TD]
[/TR]
[TR]
[TD]Pat[/TD]
[TD]Abernathy[/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]Ackary[/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]Ackary[/TD]
[/TR]
[TR]
[TD]Karen[/TD]
[TD]Smith[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]Barker[/TD]
[/TR]
[TR]
[TD]Ellen[/TD]
[TD]Barker[/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD]Smith[/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]Brady[/TD]
[/TR]
</tbody>[/TABLE]

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?


[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]FIRST NAME[/TD]
[TD]LAST NAME[/TD]
[/TR]
[TR]
[TD]Arpil & Pat[/TD]
[TD]Abernathy[/TD]
[/TR]
[TR]
[TD]Pat,John[/TD]
[TD]Abernathy[/TD]
[/TR]
[TR]
[TD]David,Will[/TD]
[TD]Ackary,John[/TD]
[/TR]
[TR]
[TD]David,Jake[/TD]
[TD]Ackary,Jake[/TD]
[/TR]
</tbody>[/TABLE]