find and list duplicate entries

[Eric Kelcher]

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.

 

[shekhar_pc]

http://www.cpearson.com/excel/duplicat.htm

HTH

 

[Eric Kelcher]

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.

 

[Eric Kelcher]

Any other ideas?

 

[shekhar_pc]

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

 

[agihcam]

post a few samples of your data?

 

[Peter_SSs]

Eric

Is this any help for identifying the duplicates?
Formula in C2 (copied down) is:

=IF(SUMPRODUCT(--(A$2:A2=A2),--(B$2:B2=B2))>1,"Dup","")

Mr Excel.xls
	A	B	C	D
1	F Name	L name
2	Fred	Smith
3	John	Jones
4	Sue	Smith
5	John	Jones	Dup
6	Fred	Smyth
7	Fred	Smith	Dup
8	John	Jones	Dup
9
Duplicates

 

[Eric Kelcher]

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]

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
	A	B	C	D	E	F	G	H
1	F.Name	L.Name	Address	City	St	Zip	Phone	SSN
2	GEORGE	BUSH	123Main	Washington	DC	12345	(555)321-4356	123456789
3	GEORGE	BUSH	124Main	Washington	DC	12346	(555)321-4357	123456790
4	GEORGEW	BUSH	125Main	Washington	DC	12347	(555)321-4358	123456791
5	GREG	BUSH	126Main	Washington	DC	12348	(555)321-4359	123456792
6	GEORGE	HAMILTON	127Main	Washington	DC	12349	(555)321-4360	123456793
7	TIM	HAMILTON	128Main	Washington	DC	12350	(555)321-4361	123456794
8	TIM	SMITH	129Main	Washington	DC	12351	(555)321-4362	123456795
Contingency

I would like to have the check for duplicates give the following ouput.

CCSLIST.xls
	A	B	C	D	E	F	G	H
1	F.Name	L.Name	Address	City	St	Zip	Phone	SSN
2	GEORGE	BUSH	Duplicate	Washington	DC	12345	(555)321-4356	123456789
3	GEORGE	BUSH	124Main	Washington	DC	12346	(555)321-4357	123456790
4	GEORGEW	BUSH	125Main	Washington	DC	12347	(555)321-4358	123456791
5	GREG	BUSH	126Main	Washington	DC	12348	(555)321-4359	123456792
6	GEORGE	HAMILTON	127Main	Washington	DC	12349	(555)321-4360	123456793
7	TIM	HAMILTON	128Main	Washington	DC	12350	(555)321-4361	123456794
8	TIM	SMITH	129Main	Washington	DC	12351	(555)321-4362	123456795
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]

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