Macro to delete Duplicate Records

[howard]

I have a spreadsheet containing numerous duplicate references. These aere contained in Col H. I would like a macro that will delere only one of the duplicate records. For EG if H25, contains Ref 145609 & H26 contains 145609, then the first row containing the duplicate record must be deleted

Your assistance will be most appreciated

 

[Dannyh1]

Will the duplications always be in order?

e.g. could 145609 be in H25 & in H30

 

[RSXchin]

you could use the "remove duplicates" function

but you need to REVERSE sort your data first. Because "remove duplicates" always deletes the SECOND record.

 

[howard]

Hi Guys

Thanks for the help

The duplicate records will allways be in order

 

[Dannyh1]

Without code, you could put in formula

J2 =If(H2=H1,"delete","")

Copy down & filter on column J for delete, then delete rows.

 

[Walky]

Hi,

I "manual" workaround, using a helper column would be to add a formula which will indicate which is the 1st and the 2nd occurrence of your data, then via the data filter, you simply choose occurrences 2/2 (the 2nd ones) and delete the lines.

So in an empty column next to your data, presuming the your data starts on row 2, enter:

=COUNTIF(H$2:H2;H2)&"/"&COUNTIF(H:H;H3)

I am using an european version of Excel, so you might have to replace the ";" with a comma ",".

Once you have copied this formula down, do not forget to use the auto-filter to select "2/2". Of course, if there are more than 2 occurences, the numbers will show it and you can throw in some conditional formatting to differentiate lines with 2, 3 or more occurences.

Btw, your duplicates do not need to be one above the other in order to use this option to find and delete them... no re-ordering, so very practical.

I hope this helps.

W.

 

[howard]

Hi Walky

Thanks for the help. The formula is great and works well

 

[Walky]

You are welcome.

I love this option, since I can't seem to understand the logic of the Advanced Filter when it comes to finding duplicates and getting rid of them.

W.

 

[howard]

Hi Walky

I tried to amend you formula that if a duplicate reference appears in Col H starting with an "F" then the result must display 1/1 otherwise the formula =COUNTIF(H$3:H3,H3)&"/"&COUNTIF(H:H,H3) must apply

See my formula below which I tried to amend, but it does not give me the correct result


=IF(H93="F?",1/1,COUNTIF(H$3:H93,H93)&"/"&COUNTIF(H:H,H93))

Your assistance will be most appreciated

 

[Dazzawm]

Try this on a copy of your workbook

Public 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
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