We help IT Professionals succeed at work.

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

56 Views
Last Modified: 2019-03-11
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
Comment
Watch Question

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I assume you also want to retain all the ID's where there's just one entry like rows 8 to 12. True?
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Subodh Tiwari (Neeraj)Excel & VBA Expert
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015

Commented:
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

Author

Commented:
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)Excel & VBA Expert
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015

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

Anyways glad your issue has been resolved.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.