Remove Duplicate Rows within Specified Time Range

[adamnc]

Hi guys,

After you massive help last time I thought I'd run this one by you!

I'm trying to use VBA to remove duplicate rows from a table. say there were two rows with a duplicate value in column C, I would like to keep one of the two. I would also like to be able to set a time range for when to remove the duplicates. For instance, I want to remove the duplicate row containing value 101 in column C from the attached spreadsheet, I want the first one of the two values to remain, but I would like the two entries of 101 20 minutes later to also show only one of these values.

Sorry if that makes no sense or if it can't be done! It's the higlighted yellow rows I'm trying to keep, so only to remove duplicate rows based on values in column C, and only duplicates found within 20 minutes

[Marc L]

According to your attachment as a VBA beginner starter :

Sub Demo1() 
            Const C = 4 
              Dim D%(), V, R&, T#, N& 
    With [A1].CurrentRegion.Rows 
            ReDim D(1 To .Count, 0) 
            V = .Value2 
        For R = 1 To .Count - 1 
            If D(R, 0) = 0 Then 
                    T = V(R, 1) + #12:20:00 AM# 
                For N = R + 1 To .Count 
                    If V(N, 3) <> V(R, 3) Then Exit For 
                    If V(N, 1) > T Then Exit For Else D(N, 0) = 1 
                Next 
            End If 
        Next 
           R = Application.Sum(D) - 1 
        If R > -1 Then 
            Application.ScreenUpdating = False 
           .Columns(C) = D 
           .Resize(, C).Sort .Columns(C), xlAscending, Header:=xlNo 
            Union(.Item(.Count - R & ":" & .Count), .Columns(C)).Clear 
            Application.ScreenUpdating = True 
        End If 
    End With 
End Sub  

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[adamnc]

Amazing, thank you!

Would I be right in assuming if I wanted to edit the time range from 20 minutes to 10 minutes I would edit this line to show 12:10:00 rather than 12:20:00?
T = V(R, 1) + #12:20:00 AM#

[Marc L]

Exactly …


If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[adamnc]

Apologies for the delay!

And thank you for pointing that out, I have done as you mentioned!

Would it be terrible if you could give me a breakdown of the code you created? VBA is very new to me so I'm trying to learn it, but now I have a workbook that I use I will hopefully be able to understand line by line this code!