Daminc,

The macro simply inserts a new column, adds COUNTIF formulas to flag duplicates to delete, and then
sorts and deletes the duplicates, then deletes the inserted column.

If you want to see what is happening, use 2 macros: one that puts in the formula first, and a second
that does the deletion. That way, you can see the flagging and check why numbers you think are
duplicates aren't actually duplicates.

See the two macros below.

HTH,
Bernie
MS Excel MVP

'First Macro
Sub ShowDuplicates()
Dim myRow As Long
With Application
.ScreenUpdating = False
.EnableEvents = False
End With

myRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("A1").EntireColumn.Insert
Range("A1").Value = "Flag"
Range("A2").Formula = _
"=IF(COUNTIF(D:D,B2)0,""Duplicate"","""")"
Range("A2").AutoFill Destination:=Range("A2:A" & myRow)

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub

'Second macro
Sub DeleteDuplicates()

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Range("A:B").Sort key1:=Range("A2"), order1:=xlAscending, Header:=xlYes
With Range("A:A")
.AutoFilter Field:=1, Criteria1:="Duplicate"
.SpecialCells(xlCellTypeVisible).Areas(2).Select
Selection.Offset(0, 1).Select
Selection.Delete Shift:=xlUp
.EntireColumn.Delete
End With

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub




"Daminc" wrote in message
...

Cheers Bernie, it seems like it works but at the moment the math doesn't
work out.

Original: 4434
Dup: 389

Anticipated result: 4045
Actual result: 4082

which looks like 37 of the duplicates weren't duplicates.

I'll have to do a manual check to validate this.

It would help if I understood your macro.
98% of it I haven't come across before


--
Daminc
------------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=493823