Find Duplicates

kbnetguy

New Member
Joined
Nov 18, 2016
Messages
9
I have Column A "Fist Name" and Column B "Last Name". For example

FIRST NAMELAST NAME
Arpil & PatAbernathy
PatAbernathy
DavidAckary
DavidAckary
KarenSmith
BobBarker
EllenBarker
JackSmith
DavidBrady

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

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

znoxide

New Member
Joined
Oct 9, 2016
Messages
3
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

Well-known Member
Joined
Jun 24, 2015
Messages
1,707
Office Version
  1. 2010
Platform
  1. Windows
kbnetguy,

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

Code:
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

New Member
Joined
Oct 14, 2009
Messages
6
Hi tonyyy

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


FIRST NAMELAST NAME
Arpil & PatAbernathy
Pat,JohnAbernathy
David,WillAckary,John
David,JakeAckary,Jake

<tbody>
</tbody>
 

Forum statistics

Threads
1,147,732
Messages
5,742,854
Members
423,759
Latest member
meb229

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top