Removing duplicate time cells/rows from large data tables

S

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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
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
 
Upvote 0
This is the basic idea (untested, i wrote in comment box, may have errors)

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





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

Code: 
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
Click to expand...
 
Upvote 0
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



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

Code: 
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
Click to expand...
 
Upvote 0
If all your date/times are 00 seconds as your data posted pastes in Excel then all you need to change is
Code: 
Dim currentDate As Date, d As Date
to
Code: 
Dim currentDate As Date, d
 
Upvote 0
If your actual data does contain different seconds (not like your data pastes) then try...

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 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
 
Upvote 0
Many Thanks - this worked - brilliant! Best regards, Simon




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

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 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
Click to expand...
 
Upvote 0
I got here late but it seems you got it sorted. Thanks Mark for the tip about the time. I overlooked that part.
 
Last edited:
Upvote 0
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?

[TABLE="width: 170"]
<colgroup><col></colgroup><tbody>[TR]
[TD]TimestampUTC[/TD]
[/TR]
[TR]
[TD="align: right"]02/07/2017 22:26:08[/TD]
[/TR]
[TR]
[TD="align: right"]02/07/2017 21:26:37[/TD]
[/TR]
[TR]
[TD="align: right"]02/07/2017 21:17:58[/TD]
[/TR]
[TR]
[TD="align: right"]02/07/2017 20:54:00[/TD]
[/TR]
[TR]
[TD="align: right"]02/07/2017 20:11:18[/TD]
[/TR]
[TR]
[TD="align: right"]02/07/2017 19:35:48[/TD]
[/TR]
[TR]
[TD="align: right"]02/07/2017 19:02:28[/TD]
[/TR]
[TR]
[TD="align: right"]02/07/2017 18:56:46[/TD]
[/TR]
[TR]
[TD="align: right"]02/07/2017 18:55:07[/TD]
[/TR]
[TR]
[TD="align: right"]02/07/2017 18:55:06[/TD]
[/TR]
[TR]
[TD="align: right"]02/07/2017 18:09:17[/TD]
[/TR]
[TR]
[TD="align: right"]02/07/2017 18:08:56[/TD]
[/TR]
[TR]
[TD="align: right"]02/07/2017 18:08:26[/TD]
[/TR]
[TR]
[TD="align: right"]02/07/2017 18:08:17[/TD]
[/TR]
[TR]
[TD="align: right"]02/07/2017 18:08:07[/TD]
[/TR]
[TR]
[TD="align: right"]02/07/2017 18:07:56[/TD]
[/TR]
[TR]
[TD="align: right"]02/07/2017 18:07:47[/TD]
[/TR]
[TR]
[TD="align: right"]02/07/2017 18:07:38[/TD]
[/TR]
[TR]
[TD="align: right"]02/07/2017 18:07:26[/TD]
[/TR]
[TR]
[TD="align: right"]02/07/2017 18:07:07[/TD]
[/TR]
</tbody>[/TABLE]




[TABLE="width: 155"]
<colgroup><col></colgroup><tbody>[TR]
[TD]TimestampUTC[/TD]
[/TR]
[TR]
[TD="align: right"]02/07/2017 22:26:08[/TD]
[/TR]
[TR]
[TD="align: right"]02/07/2017 21:26:37[/TD]
[/TR]
[TR]
[TD="align: right"]02/07/2017 21:17:58[/TD]
[/TR]
[TR]
[TD="align: right"]02/07/2017 20:54:00[/TD]
[/TR]
[TR]
[TD="align: right"]02/07/2017 20:11:18[/TD]
[/TR]
[TR]
[TD="align: right"]02/07/2017 19:35:48[/TD]
[/TR]
[TR]
[TD="align: right"]02/07/2017 19:02:28[/TD]
[/TR]
[TR]
[TD="align: right"]02/07/2017 18:56:46[/TD]
[/TR]
[TR]
[TD="align: right"]02/07/2017 18:55:07[/TD]
[/TR]
[TR]
[TD="align: right"]02/07/2017 18:09:17[/TD]
[/TR]
[TR]
[TD="align: right"]02/07/2017 18:08:56[/TD]
[/TR]
[TR]
[TD="align: right"]02/07/2017 18:08:17[/TD]
[/TR]
[TR]
[TD="align: right"]02/07/2017 18:07:56[/TD]
[/TR]
[TR]
[TD="align: right"]02/07/2017 18:07:47[/TD]
[/TR]
</tbody>[/TABLE]

Many thanks

SimonBevan




cerfani said:
I got here late but it seems you got it sorted. Thanks Mark for the tip about the time. I overlooked that part.
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
User to select data
Replies
2
Views
383
pastorkc
P
J
  • Question Question
Removing blank rows based on cell value
Replies
2
Views
256
Peter_SSs
Peter_SSs
H
  • Solved
Delete all rows including cells in column "A" that are identical to the values in cells "D1", "E1","F1" and "A6".
2
Replies
10
Views
575
Peter_SSs
Peter_SSs
A
  • Question Question
VBA help: Removing Patient from list that has Discharged, and for non-discharged getting the time period
Replies
1
Views
212
eiloken
E
B
  • Question Question
countifs and remove data completed
2
Replies
10
Views
902
MuchToLearn
M

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,636
Latest member
laura12345

Share this page

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