Deleting Duplicates

[herter24]

Ok once again I'd like to thank everyone for their much appreciated help on the last question.

Now I have a list of numbers many which are duplicates what formula would I use to delete the duplicates?

[TABLE="width: 276"]
<colgroup><col></colgroup><tbody>[TR]
[TD]32245392975[/TD]
[/TR]
[TR]
[TD]32245392975[/TD]
[/TR]
[TR]
[TD]32367684391[/TD]
[/TR]
[TR]
[TD][TABLE="width: 276"]
<colgroup><col></colgroup><tbody>[TR]
[TD]2026012556[/TD]
[/TR]
[TR]
[TD]2026012556[/TD]
[/TR]
[TR]
[TD]2026012556[/TD]
[/TR]
[TR]
[TD]32366479240[/TD]
[/TR]
[TR]
[TD]32366479240[/TD]
[/TR]
[TR]
[TD]32367147322[/TD]
[/TR]
[TR]
[TD]32367147322[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[RedBeard]

Google gives me: https://support.office.com/en-us/ar...e-values-ccf664b0-81d6-449b-bbe1-8daaec1e83c2

 

[JoeMo]

You can't delete duplicates using native Excel functions. Try Data>Data Tools>Remove Duplicates.

 

[alansidman]

If you are using an earlier version of Excel 2003 or earlier, you will need to employ a macro to do the deletion. Here is one I use:

Option Explicit


Sub DeleteDuplicateRows()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' DeleteDuplicateRows
' This will delete duplicate records, based on the Active Column. That is,
' if the same value is found more than once in the Active Column, all but
' the first (lowest row number) will be deleted.
'
' To run the macro, select the entire column you wish to scan for
' duplicates, and run this procedure.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


Dim r As Long
Dim n As Long
Dim V As Variant
Dim rng As Range


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




Set rng = Application.Intersect(ActiveSheet.UsedRange, _
                    ActiveSheet.Columns(ActiveCell.Column))


Application.StatusBar = "Processing Row: " & Format(rng.Row, "#,##0")


n = 0
For r = rng.Rows.Count To 2 Step -1
If r Mod 500 = 0 Then
    Application.StatusBar = "Processing Row: " & Format(r, "#,##0")
End If


V = rng.Cells(r, 1).Value
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Note that COUNTIF works oddly with a Variant that is equal to vbNullString.
' Rather than pass in the variant, you need to pass in vbNullString explicitly.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If V = vbNullString Then
    If Application.WorksheetFunction.CountIf(rng.Columns(1), vbNullString) > 1 Then
        rng.Rows(r).EntireRow.Delete
        n = n + 1
    End If
Else
    If Application.WorksheetFunction.CountIf(rng.Columns(1), V) > 1 Then
        rng.Rows(r).EntireRow.Delete
        n = n + 1
    End If
End If
Next r


EndMacro:


Application.StatusBar = False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox "Duplicate Rows Deleted: " & CStr(n)




End Sub