duplicate checking
Posted by John Villegas on April 26, 2001 1:33 PM
I'm consolidatig 4 e-mail lists and I want to ensure that any duplicates are extracted.
How do I do this in Excel??
Thanks for your great service!!
John Vilegas
Posted by Mark W. on April 26, 2001 1:53 PM
Use an Advanced AutoFilter to copy unique records
to another location (perhaps below your existing list
of email addresses).
Posted by Dave Hawley on April 26, 2001 11:11 PM
Hi John
Here is a macro that will automate the process for you. To use it, push Alt+F11 and go to Insert>Module and paste in the code below:
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
As is it will act on Column A. Push Alt+Q to return to Excel and then push F8, click "CopyUniquesToNewSheet" then click Options and assign a shortcut key.
Dave
OzGrid Business Applications
