Remove Duplicate Timestamps Based on Multiple Criteria

[Tracy2573]

Hello, I am looking for a formula that will help find duplicate timestamps from column A within four hours if column E and F have the exact same information. You can see below the time stamp is only a little more htan two minutes apart, which is considered a duplicate because of column E and F. Any help would be greatly appreciated.

 

[Joe4]

Could you have multiple records for the same instance to delete (i.e. 3 records all within 4 hours, so only leave the first)?
Can you sort your data by columns E, F, and then A last?

 

[Joe4]

OK, this MIGHT work for you (make sure you make a backup before running it!).

Sub MyDeleteDups()

    Dim lr As Long
    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   Find last row with data in column A
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Sort results by columns E, F then A
    Range("A1:F" & lr).Sort _
        key1:=Range("E1"), order1:=xlAscending, _
        key2:=Range("F1"), order2:=xlAscending, _
        key3:=Range("A1"), order3:=xlAscending, Header:=xlYes
        
'   Loop through rows backwards, comparing each row to the row above it
    For r = lr To 3 Step -1
'       Check to see if columns E and F match and the difference in A is less than 4 hours
        If (Cells(r, "E") = Cells(r - 1, "E")) And (Cells(r, "F") = Cells(r - 1, "F")) And _
            (Cells(r, "A") - Cells(r - 1, "A") < (4 / 24)) Then
                Rows(r).Delete
        End If
    Next r

    Application.ScreenUpdating = True
    
    MsgBox "Macro complete!"
        
End Sub

It could have problems with some records, depending on the nature of your data, and how you want it to work.
Here is an example of one that could be problematic.

Let's say that you have three records in a row that all have the same values for columns E and F.
Now let's say that the second record is 3 hours after the first, and the third record is 3 hours after the second (so the third record is 6 hours after the first).
Since my code is sorting all the data by columns E, F, and then A, and then working backwards (from last to first), comparing each record to the record just above it, it would delete BOTH the second and third records in this instance.

I am not sure if data situation is possible with your data, and how you want it to behave in that scenario.

 

[Tracy2573]

this is pretty confusing to me but I will always have the same data in column E & F, sometimes with the same description and some times not. I also wouldn't want it to necessarily delete the duplicate lines but highlight them so I could verify if they were true duplicates.

I am not able to attach my file as an example because I can't download the suggested app, but ultimately in my screen shot I would need the second row to be highlighted as it is on 2 minutes after the first time stamp.

In this example the same unit number will be showing several times with the same location, and sometimes two on the same day but more than 4 hours apart, which is okay. But when it is minutes apart we know it is wrong. The two in red, the second one is wrong.

Timestamp	Unit Number	Location/Service Code
3/1/2022 11:45:37​	2508​	920/120 Interior Clean
3/4/2022 13:05:30​	2508​	920/120 Interior Clean
3/6/2022 13:30:28​	2508​	920/120 Interior Clean
3/4/2022 11:42:34​	2508​	920/120 Sanitization
3/6/2022 12:14:09​	2508​	920/120 Sanitization
3/6/2022 12:16:10​	2508​	920/120 Sanitization
3/6/2022 2:05:26​	2508​	920/120 Sanitization
3/6/2022 21:45:56​	2508​	920/120 Sanitization
3/1/2022 11:01:05​	2508​	920/120 Sanitization
3/1/2022 0:50:22​	2508​	920/120 Sanitization
3/1/2022 21:00:13​	2508​	920/120 Sanitization
3/2/2022 20:57:09​	2508​	920/120 Sanitization
3/3/2022 21:17:49​	2508​	920/120 Sanitization
3/4/2022 22:41:59​	2508​	920/120 Sanitization

​

​

​

​

 

[Joe4]

I also wouldn't want it to necessarily delete the duplicate lines but highlight them so I could verify if they were true duplicates.

OK, that is not what you initial post seemed to imply when it was entitled "Remove Duplicate Timestamps Based on Multiple Criteria" (the word "remove" implies "delete").

However, if you want Conditional Formatting, then select the values in column A, starting in cell A3 (your first possible duplicate) down to the bottom of the data in column A, and enter this Conditional Formatting Formula:

=AND($E3=$E2,$F3=$F2,($A3-$A2)<(4/24))

and choose your desired highlighting color.