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.

 

[Eric Kelcher]

yes that did it. Took me a while to figure out what all was being checked and how. I think I learned something, but of course I ran into my next road block. The IF statement works different than when in the worksheet but seems way more powerful (can used for assigning functions not just numers or letters)

How would I do the If statement but compare to a known alpha value ie

IF Cells(x, 1) = "duplicate" Then
Range(Cells(x, 1), Cells(x, LC)).Interior.ColorIndex = 3


This is for my next step when the names are looked up to retrieve address info.

 

[Tom Urtis]

If you mean that you want to shade the rows red where "Duplicate" exists in column C, this would do that, without a loop. Put it in a fresh module and test.



Option Explicit
Option Compare Text


Sub Test2()
Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False
Dim LR&, LC%, myFilter$

With Range("A1").CurrentRegion
LR = .Rows.Count
LC = .Columns.Count
.Interior.ColorIndex = 0
myFilter = "Duplicate"

Range("C1:C" & LR).AutoFilter Field:=1, Criteria1:=myFilter

On Error Resume Next
.Offset(1).Resize(.Rows.Count - 1, LC).SpecialCells(12).Interior.ColorIndex = 3
Err.Clear
End With

ActiveSheet.AutoFilterMode = False
Application.ScreenUpdating = True
End Sub

 

[Eric Kelcher]

Hmm not working. But it got me going in the right direction. I this is what i ended up with and it worked.

Sub HighlightDuplicates()
'
' highlightDuplicates Macro
' Macro recorded 11/5/2006 by EEK the Cat
'

'Sheets("CCS Report").Select
Range("G10:G1000").Select
ActiveWindow.LargeScroll Down:=-16
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Duplicate"
Rows("11:47").Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
Selection.AutoFilter Field:=1
End Sub


Now I know there are two things that can come back to bite me further down the road which is the range and the rows selected. How do I get it to take a range that is Column G from row 11 down and then select the rows to highlight so it is all of the rows that have "duplicate" in the sort column?

 

[Tom Urtis]

Hmm not working.

Yes it does work, as I said, the premise of the code was based on your screen shot of a header row being row 1 and the first column being column A. Obviously if you are going to change the range on your sheet for data to start in cell A10 instead of A1, and for the "Duplicate" containing column to now be G instead of C as you first said, then the code you received based on your initial representation would not be effective on whatever you have changed the data ranges to.

Now I know there are two things that can come back to bite me further down the road which is the range and the rows selected. How do I get it to take a range that is Column G from row 11 down and then select the rows to highlight so it is all of the rows that have "duplicate" in the sort column?

The answer is in the macro I posted. Modify my macro which was composed based on your first description of the arranged data, to fit your new actual data arrangement. If you look at the code it should be apparent which cell addresses to change, example, C1 to G10, and A1 to A10 etc.

 

[Eric Kelcher]

Sorry should have not said it is not working it is working just with an error and yes the 2nd file I wanted to filter with identified Duplicates being highlighted is G and the report starts at row 10. Fairly simple change and I am sorry I did not mention the difference in the 2nd file.

The error I am getting is that the last six rows are also highlighted. I am not savy enough to figure out how to make it stop highlighting and I took what I could see was working and tried to come up with something that would also work. My version ends up with some of the "Duplicates" not being highlighted if there are several or a bunch of highlighted rows if there are not many entries.

I am going to keep playing with it to see if I can get there.

 

[Eric Kelcher]

This is what the last few lines looks like.

Where the first line for Jeff Chen is done right and Jeff Wood is also right but Kevin Elliott and the second entry for Jeff Chen show the errors.

template ver 5-9.xls
	A	B	C	D	E	F	G	H	I	J	K	L
66	Vortex	LSSEx	JeffChen	50	1		Duplicate	GLENDALE	IL	12345	(555)123-0987	123456789
67			RiderTotal	50
68	Vortex	LSSEx	JeffWood	15	4		123MAINST	HOMETOWN	MA	54321	(555)321-5678	098765452
69			RiderTotal	15
70	Vortex	LSSEx	KevinElliott	10	5		POBOX123	YOURTOWN	TX	65432	(817)543-7898	908854312
71			RiderTotal	10
72			Total	75
73	VPFuels	LSSEx	JeffChen	25	1		Duplicate	GLENDALE	IL	12345	(555)123-0987	123456789
74			RiderTotal	25
75			Total	25
CCS Report

Here is what I have modified the macro to.

Sub HighlightDuplicates()
'
' highlightDuplicates Macro
' Macro recorded 11/5/2006 by EEK the Cat
'

Sheets("CCS Report").Select
Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False
Dim LR&, LC%, myFilter$

With Range("A10").CurrentRegion
LR = .Rows.Count
LC = .Columns.Count
.Interior.ColorIndex = 0
myFilter = "Duplicate"

Range("G1:G" & LR).AutoFilter Field:=1, Criteria1:=myFilter

On Error Resume Next
.Offset(1).Resize(.Rows.Count - 1, LC).SpecialCells(12).Interior.ColorIndex = 3
Err.Clear
End With

ActiveSheet.AutoFilterMode = False
Application.ScreenUpdating = True

End Sub