How to remove repeating time intervals?

[Waimea]

17:58:28	2020-08-02	Location	Dim Level	CHANGE
17:57:09	2020-08-02	x	Off	On to Off
17:06:45	2020-08-02	x	On	On
16:45:36	2020-08-02	x	Off	On to Off
08:15:03	2020-08-02	x	On	On
02:43:52	2020-08-02	x	Off	Off
02:33:50	2020-08-02	x	Off	On to Off
02:05:03	2020-08-02	x	On	On
01:05:02	2020-08-02	x	Off	Off
00:56:45	2020-08-02	x	Off	Off
00:28:45	2020-08-02	x	Off	Off
00:05:02	2020-08-02	x	Off	Off
23:05:03	2020-08-01	x	Off	Off
18:09:30	2020-08-01	x	Off	Off
16:43:46	2020-08-01	x	Off	On to Off
08:15:03	2020-08-01	x	On	On
03:49:46	2020-08-01	x	Off	Off
03:46:09	2020-08-01	x	Off	On to Off
02:05:02	2020-08-01	x	On	On
01:05:02	2020-08-01	x	Off	Off
00:05:03	2020-08-01	x	Off	Off
23:05:02	2020-07-31	x	Off	Off
23:00:03	2020-07-31	x	Off	Off
17:16:50	2020-07-31	x	Off	Off
17:16:50	2020-07-31	x	Off	Off

Hi, I have time data and I want to remove reapeating time intervals, at 02:05:03, at 01:05:02 etc. It could also be 23:05:13 et.

The minute is always 05 but the hour and the seconds varies but I still want to remove the corresponding rows.

I also want to delete multiple Off to Off in the change column. If the change column has Off, Off, Off I want to remove the corresponding rows.

If you have any questions, I'll try to answer as best as I can.

Grateful for your assistance!

I am looking for a VBA solution!

02:05:03​

 

[iggydarsa]

Not sure what you meant by the "Off" criteria but the code below removes the duplicate minutes and keeps only one (Assuming your times are on column A)

Sub RemoveMin()
    lr = Range("A" & Rows.Count).End(xlUp).Row
    
    For i = lr To 2 Step -1
        For j = i - 1 To 1 Step -1
            If Minute(Range("A" & j)) = Minute(Range("A" & i)) Then
                Range("A" & j).EntireRow.Delete
                Exit For
            End If
        Next j
    Next i
End Sub

 

[Waimea]

Hi, thank you for your reply!

I have times in column A but I get a type mismatch when I run your code.

I want to delete all the minutes. So 23:05:02, 00:05:03, 01:05:04 all get deleted.

Is this possible?

 

[iggydarsa]

Maybe there is a better way but I can do it by using a helper column.
I'm assuming column F is blank (if not you can change it to another column)

give this a try

Sub RemoveMin()
    On Error Resume Next
    lr = Range("A" & Rows.Count).End(xlUp).Row

'Identify duplicates
    For i = 1 To lr - 1
        For j = i + 1 To lr
            If Range("F" & i).Value <> "x" Then
                If Minute(Range("A" & j)) = Minute(Range("A" & i)) Then
                    Range("F" & i).Value = "x"
                    Range("F" & j).Value = "x"
                End If
            End If
        Next j
    Next i
   
'Remove duplicates
    For i = lr To 1 Step -1
        If Range("F" & i).Value = "x" Then
            Range("F" & i).EntireRow.Delete
        End If
    Next i
End Sub

 

[Waimea]

Hi again,

thank you for your quick reply and your code.

I did a quick test and of 3000 rows only 60 are left after running your macro.

Is there something I am doing wrong? I want to keep all times that are not xx:05:xx.

 

[iggydarsa]

Oh my bad, I thought you wanted to remove all the duplicate times.
This make the job even easier.

If you only wanna remove minute "05"s then try this:

Sub Remove05()
    On Error Resume Next
    lr = Range("A" & Rows.Count).End(xlUp).Row
    For i = lr To 1 Step -1
        If Minute(Range("A" & i)) = 5 Then
            Range("A" & i).EntireRow.Delete
        End If
    Next i
End Sub

 

[Waimea]

Hi, thank you again.

Now your code works great!

Maybe you can take a swing on deleting the unwanted off's?

 

[iggydarsa]

I'm not clear what you need for the Off situation. You will need to elaborate more

 

[Waimea]

Hi,

I'll try to elaborate as good as I can.

Time + 1h	Time	Date	Time2	Location	Dim Level	Start	End	Length	Status
21:02:44	20:02:44	25-08-2019	20:02:44 25/08/2019	x	Off			0:00	Off to Off
18:39:07	17:39:07	25-08-2019	17:39:07 25/08/2019	x	Off	18:39	21:02	2:23	On to Off
17:51:59	16:51:59	25-08-2019	16:51:59 25/08/2019	x	On	17:51	18:39	0:47	On
13:02:06	12:02:06	25-08-2019	12:02:06 25/08/2019	x	Off	13:02	17:51	4:49	Off to Off
09:23:10	8:23:10	25-08-2019	08:23:10 25/08/2019	x	Off	9:23	13:02	3:38	On to Off
07:20:30	6:20:30	25-08-2019	06:20:30 25/08/2019	x	On	7:20	9:23	2:02	On
03:05:03	2:05:03	25-08-2019	02:05:03 25/08/2019	x	Off	3:05	7:20	4:15	Off to Off
02:05:02	1:05:02	25-08-2019	01:05:02 25/08/2019	x	Off	2:05	3:05	1:00	Off to Off
01:05:03	0:05:03	25-08-2019	00:05:03 25/08/2019	x	Off	1:05	2:05	0:59	Off to Off
00:59:16	23:59:16	24-08-2019	23:59:16 24/08/2019	x	Off	0:59	1:05	0:05	On to Off
00:47:43	23:47:43	24-08-2019	23:47:43 24/08/2019	x	On	0:47	0:59	0:11	On
00:05:03	23:05:03	24-08-2019	23:05:03 24/08/2019	x	Off	0:05	0:47	0:42	Off to Off
21:00:47	20:00:47	24-08-2019	20:00:47 24/08/2019	x	Off	21:00	0:05	3:04	Off to Off
18:30:34	17:30:34	24-08-2019	17:30:34 24/08/2019	x	Off	18:30	21:00	2:30	On to Off
17:48:02	16:48:02	24-08-2019	16:48:02 24/08/2019	x	On	17:48	18:30	0:42	On
05:02:26	4:02:26	24-08-2019	04:02:26 24/08/2019	x	Off	5:02	17:48	12:45	Off to Off
03:05:03	2:05:03	24-08-2019	02:05:03 24/08/2019	x	Off	3:05	5:02	1:57	Off to Off
02:05:03	1:05:03	24-08-2019	01:05:03 24/08/2019	x	Off	2:05	3:05	1:00	Off to Off
01:05:03	0:05:03	24-08-2019	00:05:03 24/08/2019	x	Off	1:05	2:05	1:00	Off to Off
00:05:03	23:05:03	23-08-2019	23:05:03 23/08/2019	x	Off	0:05	1:05	1:00	Off to Off
21:05:51	20:05:51	23-08-2019	20:05:51 23/08/2019	x	Off	21:05	0:05	2:59	Off to Off
20:15:25	19:15:25	23-08-2019	19:15:25 23/08/2019	x	Off	20:15	21:05	0:50	Off to Off
13:04:59	12:04:59	23-08-2019	12:04:59 23/08/2019	x	Off	13:04	20:15	7:10	Off to Off
09:20:26	8:20:26	23-08-2019	08:20:26 23/08/2019	x	Off	9:20	13:04	3:44	On to Off
07:25:26	6:25:26	23-08-2019	06:25:26 23/08/2019	x	On	7:25	9:20	1:55	On
03:05:03	2:05:03	23-08-2019	02:05:03 23/08/2019	x	Off	3:05	7:25	4:20	Off to Off
02:05:03	1:05:03	23-08-2019	01:05:03 23/08/2019	x	Off	2:05	3:05	1:00	Off to Off
01:05:03	0:05:03	23-08-2019	00:05:03 23/08/2019	x	Off	1:05	2:05	1:00	Off to Off

I want to delete Off to Off, but only if there are more then 1 "Off to Off". Se last column and marked with red color.

Does this make sense?

EDIT: In this table, the xx:05:xx minutes are not removed yet.

 

[iggydarsa]

You tell me if I got it; change the column "E" in the code to your "Status" column and give it a try and lets see...

Sub RemoveOff()
    lr = Range("E" & Rows.Count).End(xlUp).Row
    cnt = WorksheetFunction.CountIf(Range("E:E"), "Off to Off")
    If cnt > 1 Then
        For i = lr To 1 Step -1
            If Range("E" & i).Value = "Off to Off" Then
                Range("E" & i).EntireRow.Delete
            End If
        Next i
    End If
End Sub