Delete duplicate row but keep the one with highest version number in the cell

  • Hello

    As I tried to explain very short in the title. I would like to delete rows that has duplicates "column Material", but still keep the entire row with higest number from a cell in the "column Version"
    I have made an excell document (check the atachement i made) with two sheets, for better visualization. I would like that table looks like in the green sheet "desired_table". The data is in first sheet "Original Table"

    In the original file i have apx. a few thousand rows

    Macro would be great

    Many thanks

  • Here is one approach. It deletes the data on the same sheet so take a copy first if it needs to be kept
    [vba]Sub x()

    Dim rData As Range, r As Range

    Application.ScreenUpdating = False

    With Sheets("Original Table")
    Set r = .Range("A1").CurrentRegion
    .Range("H2").FormulaArray = "=G2=MAX(IF($A$2:$A$" & r.Rows.Count & "=A2,$G$2:$G$" & r.Rows.Count & "))"
    .Range("H2:H" & r.Rows.Count).FillDown
    .AutoFilterMode = False
    .Range("A1").AutoFilter Field:=8, Criteria1:="FALSE"
    With .AutoFilter.Range
    On Error Resume Next
    Set rData = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    If Not rData Is Nothing Then
    rData.Delete shift:=xlUp
    End If
    End With
    .AutoFilterMode = False
    End With

    Application.ScreenUpdating = True

    End Sub[/vba]

  • Hi
    try this

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!