Avatar of 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.

Microsoft OfficeVBAMicrosoft Excel

Avatar of undefined
Last Comment
Subodh Tiwari (Neeraj)
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

I assume you also want to retain all the ID's where there's just one entry like rows 8 to 12. True?
Avatar of Ejgil Hedegaard
Ejgil Hedegaard
Flag of Denmark image

Blurred text
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
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
    End With
    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.
Avatar of 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.

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

Anyways glad your issue has been resolved.
Microsoft Excel
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo