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

 

[MARK858]

1st step put =ISNUMBER(C2) in an empty column and drag the formula down to your last row to make sure the result is TRUE on all the cells.

 

[MARK858]

Just as a silly follow up, what results do you get with...

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 Round(DateAdd("s", -Second(d), d), 4) = Round(DateAdd("s", -Second(currentDate), currentDate), 4) Then
                Rows(row).Delete
                GoTo NextLoop
            End If
        Next

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