Compare to find duplicates within worksheet
Posted by Tracy Janecek on May 16, 2001 4:33 AM
I hope one of you gurus can help me with this one. I have a contact list that stretches across 12 or so columns listing contact first name, last name, title, company, address, mail stop, city, state,....ect
Is there an easy way to do a comparison of the entries within each column searching for same text? I would like to search on the company column first and if no matches are found to search address then phone number and so on and so on.
Not knowing enough to do this myself(still learning), what would be the best way to show the duplicates from within the search? Do you have them filled with color or copied to a second sheet?
I appreciate all of your assistance!
Tracy Janecek
Posted by David Hawley on May 16, 2001 4:46 AM
Hi Tracy
I have some examples of how to higlight Duplicates on my WebSite under "Handy Hints" that I think you may find useful.
You could also use the "Advanced Filter" (Found under Data>Filter) to filter out unique entries in each Column.
If none of the above suit your needs here is a macro that will copy all Duplicates to a new sheet. If you want I could modify it to go through each Column. Let me know. This particular code works on Column A.
Sub CopyUniquesToNewSheet()
'Written by OzGrid Business Applications
'www.ozgrid.com
''''''''''''''''''''''''''''''''''''''''''
'Create a Worksheet
'Extract unique entries only
'Then copy the entire rows to
'the new sheet
''''''''''''''''''''''''''''''''''''''''''
Dim RUniqueCells As Range
'Add a new sheet and name it
'If already exists then rename it
On Error Resume Next
Sheets.Add().Name = "Unique Copies"
If ActiveSheet.Name <> "Unique Copies" Then
ActiveSheet.Name = "Unique Copies" & Sheets.Count
End If
On Error GoTo 0
With Sheet1
'Set Range variable to all entries
Set RUniqueCells = Range(.Range("A1"), .Range("A65536").End(xlUp))
'Advance filter to remove duplicates
RUniqueCells.AdvancedFilter _
Action:=xlFilterInPlace, unique:=True
.UsedRange.SpecialCells(xlCellTypeVisible).Copy _
Destination:=ActiveSheet.Range("A1")
.ShowAllData
End With
Application.CutCopyMode = False
'Release memory
Set RUniqueCells = Nothing
End Sub
Dave
OzGrid Business Applications
Posted by Tracy Janecek on May 16, 2001 7:11 AM
Dave,
As always you have incredible knowledge of these issues and I appreciate your assistance once again!
I am still working out what my employer is going to do regarding training. Not sure of internet/email based yet, but don't give up.
Thanks again,
Tracy
