What is the easiest way to remove duplicate entries from a list

S

shadestreet

I have a list of product ID's, problem is, the list has duplicates i
it. I would like to have the list reduced so that each product numbe
is listed only once. Any tricks to do this quickly?

Thank
 
R

Raghu Prakash

Hi,

Delete duplicate rows from a list in Excel
duplicate row (also called a record) in a list is one where all values in
the row are an exact match of all the values in another row. To delete
duplicate rows, you filter a list for unique rows, delete the original
list, and then replace it with the filtered list. The original list must
have column headers.

Caution Because you are permanently deleting data, it's a good idea to
copy the original list to another worksheet or workbook before using the
following procedure.

Select all the rows, including the column headers, in the list you want to
filter.
Tip

Click the top left cell of the range, and then drag to the bottom right
cell.
On the Data menu, point to Filter, and then click Advanced Filter.
In the Advanced Filter dialog box, click Filter the list, in place.
Select the Unique records only check box, and then click OK.
The filtered list is displayed and the duplicate rows are hidden.

On the Edit menu, click Office Clipboard.
The Clipboard task pane is displayed.

Make sure the filtered list is still selected, and then click Copy .
The filtered list is highlighted with bounding outlines and the selection
appears as an item at the top of the Clipboard.

On the Data menu, point to Filter, and then click Show All.
The original list is re-displayed.

Press the DELETE key.
The original list is deleted.

In the Clipboard, click on the filtered list item.
The filtered list appears in the same location as the original list.

http://office.microsoft.com/assistance/preview.aspx?AssetID=HA010346261033&C
TT=1&Origin=EC010229861033&QueryID=fwp5bIlo0

Please let me know has this helped You...
Thank you...
Raghu...
This posting is provided "AS IS" with no warranties, and confers no rights.
 
A

ADArnold

I use this Macro that someone was kind enough to give me.

Copy & Paste this macro:

Public Sub DeleteDuplicateRows()
'
' This macro deletes duplicate rows in the selection. Duplicates are
' counted in the COLUMN of the active cell.

Dim Col As Integer
Dim r As Long
Dim C As Range
Dim N As Long
Dim V As Variant
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Col = ActiveCell.Column

If Selection.Rows.Count > 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If

N = 0
For r = Rng.Rows.Count To 1 Step -1
V = Rng.Cells(r, 1).Value
If Application.WorksheetFunction.CountIf(Rng.Columns(1), V) >
Then
Rng.Rows(r).EntireRow.Delete
N = N + 1
End If
Next r

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub


- - - - - - - - - - - - - - -

Then highlight the column values (eg: A1-A1000) that you are checkin
for duplicates. Run the macro. It will remove all COMPLETE ROWS tha
have exact duplicates in the highlighted cells. Be sure to save th
sheet before you run the macro as there is no undo after running it
 
Top