# find and list duplicate entries

#### Eric Kelcher

##### Board Regular
I need to look at a list of names and see if there are any duplicate names.

List is A=F. Name B=L. Name

I would like to know what names are duplicates and create a seperate worksheet if there are any duplicate names.

I am still learning and I would rather have the direction I need to go to create this as I have a bunch of other info I need to use this with. The code would be nice the insight into how to create would be better in my learning curve. FYI the lines are not complete duplicates as addresses SSN etc are going to be different, just need to check to see if names are the same. The second step to this is checking to see if any of the duplicate names are in another worksheet that F. Name and L. Name are in single colum.

I tried doing a search, but with my limited knowledge, not finding what I need exactly but if I knew what I was looking at for code I could make some of it work.

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Close but not exactly I need both to look at A and B and find any other rows that have the same names in both locations ie first and last name match. Not Col A = Col B

That is type equations I had found when I had done my search prior to asking question.

Any other ideas?

Sorry Eric, I tried a lot but since I am a beginner with vba, I am not able to assist you with this.

I can give you an idea.

use the worksheet function =CONCATENATE(A1," ",B1) to join both Fname and Lname on a temporary column. Use the login given in the link to find duplicates on the full names (instead of fname and lname separately).

After this is done, use =vLOOKUP to lookup addresses SSN etc on these unique records.

Probably somebody else might help

post a few samples of your data?

Eric

Is this any help for identifying the duplicates?
Formula in C2 (copied down) is:
Code:
``=IF(SUMPRODUCT(--(A\$2:A2=A2),--(B\$2:B2=B2))>1,"Dup","")``
Mr Excel.xls
ABCD
1F NameL name
2FredSmith
3JohnJones
4SueSmith
5JohnJonesDup
6FredSmyth
7FredSmithDup
8JohnJonesDup
9
Duplicates

Peter that is something I can see working. How would I put that in VBA? as I have a dynamic alpha list (~3000 people)

FYI what I have is a a program that looks up person's personal data(from cells that have F. Name=cell A L. Name=cell B) from master worksheet when given F/L name(all in cell A) in event worksheet. We discovered about a dozen people that have same first and last names by a visual scan of the alpha list (might have missed a couple) What I am trying to do is generate a list of duplicate names. Then compare the list of people at event with the duplicate list; then it spits out the people that need to have addresses checked manually.

Well regular work got real busy and I never did get this finished.

As I have been rethinking this I have wanted to make some changes on how this works.

Rahter than having it do mutiple look-ups I have an alphabetical list so I only need to search the entry beneath for duplicate name.

IE here is sample of the list
CCSLIST.xls
ABCDEFGH
2GEORGEBUSH123MainWashingtonDC12345(555)321-4356123456789
3GEORGEBUSH124MainWashingtonDC12346(555)321-4357123456790
4GEORGEWBUSH125MainWashingtonDC12347(555)321-4358123456791
5GREGBUSH126MainWashingtonDC12348(555)321-4359123456792
6GEORGEHAMILTON127MainWashingtonDC12349(555)321-4360123456793
7TIMHAMILTON128MainWashingtonDC12350(555)321-4361123456794
8TIMSMITH129MainWashingtonDC12351(555)321-4362123456795
Contingency

I would like to have the check for duplicates give the following ouput.
CCSLIST.xls
ABCDEFGH
2GEORGEBUSHDuplicateWashingtonDC12345(555)321-4356123456789
3GEORGEBUSH124MainWashingtonDC12346(555)321-4357123456790
4GEORGEWBUSH125MainWashingtonDC12347(555)321-4358123456791
5GREGBUSH126MainWashingtonDC12348(555)321-4359123456792
6GEORGEHAMILTON127MainWashingtonDC12349(555)321-4360123456793
7TIMHAMILTON128MainWashingtonDC12350(555)321-4361123456794
8TIMSMITH129MainWashingtonDC12351(555)321-4362123456795
Contingency

I still need this in a macro as the info on this page was genererated from a macro that I just need to tack this onto to the end of the code.

From my basic understanding I need to do something like this.

X=x+1
Check a"X" b"X" --- a"x+1" b"X+1" then c"X" = Duplicate
if a"X" ="" then end
repeat

but I don't know how to make that into code

Based on your screen shots, this would do what you seem to be asking for:

Sub Test1()
Application.ScreenUpdating = False

Dim LR&, LC%, x&
With Range("A1").CurrentRegion
LR = .Rows.Count
LC = .Columns.Count
.Interior.ColorIndex = 0
End With

For x = LR - 1 To 2 Step -1
If Cells(x, 1).Value & Cells(x, 2).Value = Cells(x + 1, 1).Value & Cells(x + 1, 2).Value Then
If Cells(x, 1).Value & Cells(x, 2).Value <> Cells(x - 1, 1).Value & Cells(x - 1, 2).Value Then
Range(Cells(x, 1), Cells(x, LC)).Interior.ColorIndex = 3
Cells(x, 3).Value = "Duplicate"
End If
End If
Next x

Application.ScreenUpdating = True
End Sub

Replies
6
Views
142
Replies
3
Views
208
Replies
9
Views
196
Replies
2
Views
404
Replies
3
Views
128

1,218,909
Messages
6,145,165
Members
450,591
Latest member
mba3170

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

### Which adblocker are you using?

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

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