Timestamp data with missing time

Jennings1

New member
Joined
Dec 13, 2019
Messages
3
Reaction score
0
Points
0
Excel Version(s)
16
Hi everyone,

I am having trouble with some timestamped data. This is match event data from a sports game with both a timestamp and the accumulated time in seconds at which each event occurs. In order for me to work with it and time match it to some other data, I really need the data to show every second of match time (even if there was no event. I have posted data below. As you can see, a row only exists if an event occurred. I would like a row for each second that has elapsed even if no statistic_code occured.

PERIODPERIOD_SECSMATCH_TRX_TIME_UTCMATCH_TRX_IDSTATISTIC_CODE
107:49:50100CETU
187:49:58200GELON
187:49:58210CBFP
197:49:59310HBEF
197:49:59320CBCL

This is a very large dataset so I am trying to find a quick method to perform this in excel or I may have the capacity to do it in Python.
Any help would be very much appreciated.
Cheers
Jake
 
Here is a larger portion

PERIODPERIOD_SECSMATCH_TRX_TIME_UTCMATCH_TRX_IDSTATISTIC_CODE
107:49:50100CETU
187:49:58200GELON
187:49:58210CBFP
197:49:59310HBEF
197:49:59320CBCL
1127:50:02400HBRE
1127:50:02500KKLO
1127:50:02510KIN50
1137:50:03600IN50
1157:50:05700OOBO
1277:50:17800THIN
1327:50:22900GEHAN
1327:50:22910TIFP
1327:50:221000KKIN
1327:50:221100TACKL
1367:50:261200GELOO
1367:50:261310HBEF
1367:50:261320TICL
1367:50:261400HBRE
1387:50:281510HBEF
1417:50:311600HBRE
 
Try the button in the attached which runs this code:
Code:
Sub blah()
lr = Cells(Rows.Count, "A").End(xlUp).Row
For rw = lr To 3 Step -1
  x = Cells(rw, 2).Value - Cells(rw - 1, 2).Value
  If x > 1 Then
    Rows(rw).Resize(x - 1).Insert
    Range(Cells(rw - 1, 2), Cells(rw + x - 1, 3)).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, Trend:=True
  End If
Next rw
End Sub

You say: "In order for me to work with it and time match it to some other data, I really need the data to show every second of match time (even if there was no event." but you may still not need to have a value for every second - what formula are you using to match (or what are you doing to match)?
 

Attachments

  • excelGuru10359.xlsm
    18 KB · Views: 8
Last edited:
Wow! Thank you that works perfectly. This is probably a dumb question but is that just assigning a macro?
I want to time match this data with some GPS data which is sampled at 10hz so if I could split this into .10 of a second that would be even more amazing.
Thank you
 
The attached workbook's macro currently gives 1 second intervals as before, but if you change one line:
Code:
myinterval = 1 'seconds
to:
Code:
myinterval = 0.1 'seconds
you'll get tenth of a second intervals. If you change it to 0.5 you'll get half second intervals.
If you change it to
Code:
myinterval = 1 / 3 'seconds
you'll get third of a second intervals.

But I strongly suspect you may still not need to have a value for every second/tenth of a second - what formula are you using to match (or what are you doing to match)? I want to know what formula you're using.

The whole adjusted code is:
Code:
Sub blah()
myinterval = 1 'seconds
lr = Cells(Rows.Count, "A").End(xlUp).Row
For rw = lr To 3 Step -1
  x = Cells(rw, 2).Value - Cells(rw - 1, 2).Value
  If x > myinterval Then
    RowsToInsert = Int(x / myinterval) - 1
    If RowsToInsert > 0 Then
      Rows(rw).Resize(RowsToInsert).Insert
      Range(Cells(rw - 1, 2), Cells(rw + x / myinterval - 1, 3)).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, Trend:=True
    End If
  End If
Next rw
End Sub
 

Attachments

  • excelGuru10359.xlsm
    19.6 KB · Views: 7
Last edited:
Back
Top