Avatar of Andreamary
Andreamary asked on

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 OfficeVBAMicrosoft Excel

Avatar of undefined
Last Comment
Subodh Tiwari (Neeraj)

8/22/2022 - Mon
Martin Liss

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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Subodh Tiwari (Neeraj)

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
ASKER
Andreamary

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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Subodh Tiwari (Neeraj)

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

Anyways glad your issue has been resolved.