Sorting Duplicates

[pezzer]

I need an easy way to sort and remove duplicate entries (ie: names) from a list.

So far I am sorting, then manually scrolling and looking for the dupes, but as my list is 10,000 names, it is getting too time consuming....

Can anyone help?

[lenze]

This macro will do what you want.

I don't remember who I got this from, so if it's yours, please take credit.

This macro will delete duplicate rows in a range. To use, select a single-column range of cells, comprising the range of rows from which duplicates are to be deleted, e.g., C2:C99. To determine whether a row has duplicates, the values in the selected column are compared. Entire rows are not compared against one another. Only the selected column is used for comparison. When duplicate values are found in the active column, the first row remains, and all subsequent rows are deleted.

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) > 1 Then
Rng.Rows(r).EntireRow.Delete
N = N + 1
End If
Next r

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub


[ This Message was edited by: lenze on 2002-05-03 13:19 ]

[Brian from Maui]

Read this, very interesting

http://www.mrexcel.com/board/viewtop...096&forum=2&32

[sen_edp]

hello there
take a look at this url

http://www.cpearson.com/excel/deleting.htm

I think Chip Pearson's macro is the best choice for you

Regards

Andreas

[lenze]

Actually, I think the one I gave came from either Chip or Dave Hawley

[Mark W.]

If A1:A7 contains...

{"Names"
;"John Lennon"
;"Paul McCartney"
;"Ringo Starr"
;"John Lennon"
;"Ringo Starr"
;"George Harrison"}

...and was named, 'List'. Using the Get External Data menu command, the Excel ODBC driver and the following SQL....

SELECT DISTINCT List.Names
FROM List List
ORDER BY List.Names

...you'd get...

{"Names"
;"George Harrison"
;"John Lennon"
;"Paul McCartney"
;"Ringo Starr"}



[ This Message was edited by: Mark W. on 2002-05-03 14:51 ]