MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Duplicate entries

Posted by matthew schmitt on May 04, 2001 12:13 PM

Can a query be run to eliminate duplicate entries from a sheet?
I have a sheet that lists all account numbers for a certain company. One of the fields is the group number which is used for statement purposes. Group 18 contains 3 accounts. I need the address info for group 18, but I dont need it 3 times. Any way to have the extra 2 eliminated by way of a query?

Posted by Aladin Akyurek on May 04, 2001 1:22 PM

Hi Matthew

If the address is associated with account numbers and you're pulling off the address data from a sheet to a diffrent sheet or to a different location within the same sheet, a VLOOKUP formula would do the job.

You can of course post a snippet of your data in the follow-up.


Posted by Dave Hawley on May 04, 2001 1:48 PM

Hi Mathew

Here is a macro I have which will list all unique entries on a newly created Worksheet. This one acts on Column A of the Active sheet, but that can be changed to any Column.

Sub CopyUniquesToNewSheet()
'Written by OzGrid Business Applications

'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 _
End With
Application.CutCopyMode = False
'Release memory
Set RUniqueCells = Nothing
End Sub


OzGrid Business Applications