Removing duplicate time cells/rows from large data tables

simonbevan

New Member
Joined
Jan 26, 2018
Messages
5
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
 
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.
 
Upvote 0

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Just as a silly follow up, what results do you get with...

Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,240
Messages
6,170,951
Members
452,368
Latest member
jayp2104

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top