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.

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

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