asked on 3/11/2019

Removing duplicate work orders based on latest date and time stamp in Excel report

I get a daily report generated in Excel from our workflow system. The report lists all the work orders in Column A, and the date/time various tasks were completed for that work order in Column D. Column A can include duplicate work order entries for those work orders that required a variety of tasks to be completed.

I need a macro that removes all duplicate work orders as follows:

Determines the latest date/time stamp in Column D for any duplicate work orders

Removes all entries for that work order that have older date/time stamps

Retains a single entry only for that work order that has the latest date/time stamp

I have attached a sample of the report. I have applied conditional formatting in Column A to highlight the duplicate work orders. In Column D, where there are duplicates in Column A, I have highlighted in yellow the rows that should be retained (as they have the latest date/time stamp) after the duplicate work orders are removed to illustrate the results I am hoping for.

Thanks!
Andrea
EE_Duplicate_WO_Removal.xlsx

Microsoft Office VBA Microsoft Excel

Last Comment

Subodh Tiwari (Neeraj)

3/12/2019

Martin Liss

3/11/2019

I assume you also want to retain all the ID's where there's just one entry like rows 8 to 12. True?

ASKER CERTIFIED SOLUTION

Ejgil Hedegaard

3/11/2019

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Subodh Tiwari (Neeraj)

3/12/2019

You may try something like this...

Sub RemoveDuplicateWorkOrders()
    Dim wsData As Worksheet
    Dim lr As Long
    Application.ScreenUpdating = False
    
    Set wsData = Worksheets("PA08")
    wsData.AutoFilterMode = False
    lr = wsData.Cells(Rows.Count, 1).End(xlUp).Row
    
    wsData.Range("Z2:Z" & lr).Formula = "=D2<>MAXIFS($D$2:$D$" & lr & ",$A$2:$A$" & lr & ",A2)"
    With wsData.Range("Z1:Z" & lr)
        .AutoFilter field:=1, Criteria1:=True
        If .SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
            wsData.Range("Z2:Z" & lr).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        End If
        .AutoFilter
        .ClearContents
    End With
    wsData.Range("A1").AutoFilter
    Application.ScreenUpdating = True
End Sub

Open in new window

Please click the button called "Remove Duplicate Work Orders" on PA08 Sheet to run the macro.
EE_Duplicate_WO_Removal.xlsm

Andreamary

3/12/2019

ASKER

Thanks to everyone for your quick responses!

Martin - yes, that's correct, I did want to retain all the IDs where there was just one entry.

Ejgil - your solution did the trick.

Subodh - I tried your solution a few times but the macro didn't appear to run. I tried both clicking on the button, as well as through the Developer > Macro icon.

Best,
Andrea

Subodh Tiwari (Neeraj)

3/12/2019

That's very strange?
I just tested it and it worked without an issue.

Anyways glad your issue has been resolved.