Removing duplicate time cells/rows from large data tables

[simonbevan]

Hello,

I wonder if anyone can help. I need to remove multiple cell/row data based on UTC time excluding seconds (ss). By example, I would like to auto remove all rows except A-M, T, V, X-Z in the below table. ID N-O-P-S are all minute 20 data but I only need to keep the first entry of any minute data for further analysis. So the cells/rows that need auto deleting are: O,P,S & U. Some of my data files are 3000 rows in length and I am trying to find a quick means of filtering out data I do not need for further analysis.

ID	Name	TimestampUTC	Report	Ownership	Speed	Course	Latitude	Longitude
A	NPAC	31/12/2017 23:59	Auto	0xA0	13.5	78	26.9397733	166.601905
B	NPAC	31/12/2017 23:29	Auto	0xA0	13	80	26.9178233	166.4819367
C	NPAC	31/12/2017 23:21	Auto	0xA0	13	79	26.9127133	166.4528817
D	NPAC	31/12/2017 22:52	Auto	0xA0	13	76	26.8884783	166.33775
E	NPAC	31/12/2017 22:16	Auto	0xA0	13	74	26.8554767	166.199985
F	NPAC	31/12/2017 22:15	Auto	0xA0	13	72	26.8545967	166.196685
G	NPAC	31/12/2017 21:41	Auto	0xA0	12.4	73	26.8203483	166.0682167
H	NPAC	31/12/2017 21:38	Auto	0xA0	13	77	26.817525	166.0569183
M	NPAC	31/12/2017 21:21	Auto	0xA0	13	72	26.7993217	165.989415
N	NPAC	31/12/2017 21:20	Auto	0xA0	12.4	71	26.7991417	165.9886467
O	NPAC	31/12/2017 21:20	Auto	0xA0	12.4	71	26.7991417	165.9886467
P	NPAC	31/12/2017 21:20	Auto	0xA0	12.4	75	26.79898	165.988075
S	NPAC	31/12/2017 21:20	Auto	0xA0	13	72	26.79834	165.9856367
T	NPAC	31/12/2017 20:19	Auto	0xA0	12.4	75	26.798125	165.9848717
U	NPAC	31/12/2017 20:19	Auto	0xA0	12.4	75	26.798125	165.9848717
V	NPAC	31/12/2017 19:19	Auto	0xA0	13	70	26.7978467	165.9837333
X	NPAC	31/12/2017 18:19	Auto	0xA0	13	70	26.7978467	165.9837333
Y	NPAC	31/12/2017 17:18	Auto	0xA0	13	72	26.7962217	165.9779233
Z	NPAC	31/12/2017 16:17	Auto	0xA0	13	75	26.796005	165.9772283

<colgroup><col span="2"><col><col><col><col span="2"><col><col></colgroup><tbody>
</tbody>

I trust this makes sense!
Simon

 

[cerfani]

If you can use VBA, i would maybe just loop through each cell and keep track of each time in a vba collection. So Each time you add to the collection, you check to see if the time exists in the collection, if it exists you delete the row and repeat that iteration of the loop so it doesnt skip the next row.

This page explains how to use collections...

http://codevba.com/help/collection.htm#.WmtmKa6nFhE

 

[cerfani]

This is the basic idea (untested, i wrote in comment box, may have errors)

Sub CleanupTable()
    Dim row As Long
    Dim times As New Collection
    Dim currentDate As Date, d As Date

    row = 2

    Do While Not IsEmpty(Cells(row, 3))
        currentDate = Cells(row, 3)

        For Each d In times
            If d = currentDate Then
                Rows(row).Delete
                GoTo NextLoop
            End If
        Next

        times.Add(currentDate)
        row = row + 1
NextLoop:
    Loop
End Sub

 

[simonbevan]

Thanks for this fast response but it does not work. I think the issue remains with the way that excel handles numbers - even as dates with times - as I got multiple erros, e.g. Compile error - for each control variable must be Variant or object or Can't execute code. Any ideas as I have no knowledge on this stuff?

Simon

This is the basic idea (untested, i wrote in comment box, may have errors)

Sub CleanupTable()
    Dim row As Long
    Dim times As New Collection
    Dim currentDate As Date, d As Date

    row = 2

    Do While Not IsEmpty(Cells(row, 3))
        currentDate = Cells(row, 3)

        For Each d In times
            If d = currentDate Then
                Rows(row).Delete
                GoTo NextLoop
            End If
        Next

        times.Add(currentDate)
        row = row + 1
NextLoop:
    Loop
End Sub

 

[simonbevan]

Hi Cerfani

I am a complete beginner with VBA and have not been able to run this code after spending many hours going through various tutorials. I would be grateful if you could test the routine and let me know what I need to change? Many thnaks

This is the basic idea (untested, i wrote in comment box, may have errors)

Sub CleanupTable()
    Dim row As Long
    Dim times As New Collection
    Dim currentDate As Date, d As Date

    row = 2

    Do While Not IsEmpty(Cells(row, 3))
        currentDate = Cells(row, 3)

        For Each d In times
            If d = currentDate Then
                Rows(row).Delete
                GoTo NextLoop
            End If
        Next

        times.Add(currentDate)
        row = row + 1
NextLoop:
    Loop
End Sub

 

[MARK858]

If all your date/times are 00 seconds as your data posted pastes in Excel then all you need to change is

Dim currentDate As Date, d As Date

to

Dim currentDate As Date, d

 

[MARK858]

If your actual data does contain different seconds (not like your data pastes) then try...

Sub CleanupTable()
    Dim row As Long
    Dim times As New Collection
    Dim currentDate As Date, d

    row = 2

    Do While Not IsEmpty(Cells(row, 3))
        currentDate = Cells(row, 3)

        For Each d In times
            If DateAdd("s", -Second(d), d) = DateAdd("s", -Second(currentDate), currentDate) Then
                Rows(row).Delete
                GoTo NextLoop
            End If
        Next

        times.Add (currentDate)
        row = row + 1
NextLoop:
    Loop
End Sub

 

[simonbevan]

Many Thanks - this worked - brilliant! Best regards, Simon

If your actual data does contain different seconds (not like your data pastes) then try...

Sub CleanupTable()
    Dim row As Long
    Dim times As New Collection
    Dim currentDate As Date, d

    row = 2

    Do While Not IsEmpty(Cells(row, 3))
        currentDate = Cells(row, 3)

        For Each d In times
            If DateAdd("s", -Second(d), d) = DateAdd("s", -Second(currentDate), currentDate) Then
                Rows(row).Delete
                GoTo NextLoop
            End If
        Next

        times.Add (currentDate)
        row = row + 1
NextLoop:
    Loop
End Sub

 

[cerfani]

I got here late but it seems you got it sorted. Thanks Mark for the tip about the time. I overlooked that part.

 

[simonbevan]

Hello Cefani and MARKS858

I have taken further look at the data and see that it has not done the changes for all cells. The top table is before and the bottom table is after - you can see that 18:08 and 18:07 still have more than one row? I cant work out why this is happening so I would be grateful for further help?

TimestampUTC

02/07/2017 22:26:08

02/07/2017 21:26:37

02/07/2017 21:17:58

02/07/2017 20:54:00

02/07/2017 20:11:18

02/07/2017 19:35:48

02/07/2017 19:02:28

02/07/2017 18:56:46

02/07/2017 18:55:07

02/07/2017 18:55:06

02/07/2017 18:09:17

02/07/2017 18:08:56

02/07/2017 18:08:26

02/07/2017 18:08:17

02/07/2017 18:08:07

02/07/2017 18:07:56

02/07/2017 18:07:47

02/07/2017 18:07:38

02/07/2017 18:07:26

02/07/2017 18:07:07

<colgroup><col></colgroup><tbody>
</tbody>

TimestampUTC

02/07/2017 22:26:08

02/07/2017 21:26:37

02/07/2017 21:17:58

02/07/2017 20:54:00

02/07/2017 20:11:18

02/07/2017 19:35:48

02/07/2017 19:02:28

02/07/2017 18:56:46

02/07/2017 18:55:07

02/07/2017 18:09:17

02/07/2017 18:08:56

02/07/2017 18:08:17

02/07/2017 18:07:56

02/07/2017 18:07:47

<colgroup><col></colgroup><tbody>
</tbody>

Many thanks

SimonBevan

I got here late but it seems you got it sorted. Thanks Mark for the tip about the time. I overlooked that part.