find and list duplicate entries

Eric Kelcher

Board Regular
Joined
May 11, 2006
Messages
129
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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Eric Kelcher

Board Regular
Joined
May 11, 2006
Messages
129
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.
 

shekhar_pc

Board Regular
Joined
Jan 29, 2006
Messages
185

ADVERTISEMENT

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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,601
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

Eric Kelcher

Board Regular
Joined
May 11, 2006
Messages
129
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.
 

Eric Kelcher

Board Regular
Joined
May 11, 2006
Messages
129
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
1F.NameL.NameAddressCityStZipPhoneSSN
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
1F.NameL.NameAddressCityStZipPhoneSSN
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
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,257
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
 

Forum statistics

Threads
1,141,282
Messages
5,705,483
Members
421,398
Latest member
Rahat Anwar

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