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?
Example:
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.
Aladin
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
'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
