How to convert break point time series to 1 second resolution timeseries in Excel-CodePudding

I have a breakpoint time series (when monitored value changes a new timestamp is created), which is created using accuracy of 1 second (time stamps in form of yyyy:mm:dd:ss) and I'd like to change that time series to 1 second resolution time series.

Basically this means adding additional rows and timestamps in between breakpoint timestamps, and these new timestamps need to hold the last value of the monitored entity.

Example Breakpoint time series Breakpoint

Wanted 1 second res time series 1 second res

etc. I hope you get the idea.

Problem is that I don't know how to get started with this issue, I haven't been able to find that many similar cases from the archives.

What could be a good way of approaching this type of dilemma, and how to implement the described solution as a code for VBA?

Any help is appreciated.

Thanks BR Muje

EDIT: I have created a short script that can add missing rows and duplicate the value above to this new row. However I am still struggling with cases where there is more than 1 second missing from the break point time series. Code below:

Option Explicit
Sub BP_to_sec()

Dim wb As Workbook
Set wb = ThisWorkbook
Dim ws As Worksheet
Set ws = wb.Sheets("Test")

Dim LastRow As Long
Dim RowStep As Long

Dim S As Double
S = (1 / 86400)

LastRow = Range("A" & Rows.Count).End(xlUp).Row
 For RowStep = LastRow To 3 Step -1
   If Not IsEmpty(Range("A" & RowStep)) Then
     If (CDate(Range("A" & RowStep)) > (CDate(Range("A" & RowStep - 1))   S)) Then
       Range("A" & RowStep).EntireRow.Insert
        Cells(RowStep, 1).Value = Cells(RowStep - 1, 1).Value   S
        Cells(RowStep, 2).Value = Cells(RowStep - 1, 2).Value
     End If
   End If
 Next

MsgBox ("Sorted")

End Sub

CodePudding user response：

try this

Option Explicit

Sub BP_to_sec()

Dim wb As Workbook
Set wb = ThisWorkbook
Dim ws As Worksheet
Set ws = wb.Sheets("Test")

Dim LastRow As Long
Dim RowStep As Long

Dim S As Double
S = (1 / 86400)

LastRow = Range("A" & Rows.Count).End(xlUp).Row
RowStep = LastRow
Do While RowStep > 3
    If Not IsEmpty(Range("A" & RowStep)) Then
        If (CDate(Range("A" & RowStep)) - (CDate(Range("A" & RowStep - 1))   S)) > S Then
            Range("A" & RowStep).EntireRow.Insert
            Cells(RowStep, 1).Value = Cells(RowStep   1, 1).Value - S
            Cells(RowStep, 2).Value = Cells(RowStep - 1, 2).Value
            RowStep = RowStep   1
        End If
    End If
    RowStep = RowStep - 1
Loop

MsgBox ("Sorted")

End Sub

CodePudding user response：

I managed to come up with a solution by my self as well! I'll post it here if someone finds this exact same problem.

Option Explicit
Sub BP_to_sec1()

Dim wb As Workbook
Set wb = ThisWorkbook
Dim ws As Worksheet
Set ws = wb.Sheets("Test")

Dim LastRow As Integer
Dim RowStep As Integer
Dim FillRow As Integer

Dim S As Double
Dim SS As Double
S = (1 / 86400)     ' 1 sec
SS = (1.2 / 86400)  ' 1.2 sec dif

Dim X As Integer

LastRow = Range("A" & Rows.Count).End(xlUp).Row
 For RowStep = LastRow To 3 Step -1
   If Not IsEmpty(Range("A" & RowStep)) Then
     If (CDate(Range("A" & RowStep)) > (CDate(Range("A" & RowStep - 1))   SS)) Then
        X = ((Second((Range("A" & RowStep))) - (Second(Range("A" & RowStep - 1)))))
        Range("A" & RowStep).EntireRow.Offset(0).Resize(X - 1).Insert Shift:=xlDown
         If X > 0 Then
            For FillRow = RowStep To (RowStep   (X - 2)) Step 1
                Range("A" & FillRow).Value = Range("A" & (FillRow - 1)).Value   S
                Range("B" & FillRow, "AA" & FillRow).Value = Range("B" & (FillRow - 1), "AA" & (FillRow - 1)).Value
            Next
         End If
     End If
   End If
 Next

MsgBox ("Sorted")

End Sub

As a comment, on this solution I have wider range of data in use, reaching all the way to column "AA", hence I made the edit to copy all data below, except of course the timestamp, which is added 1s.

Big thanks also to @h2so4 for your answer, I havent tested but it seems that we are using same elements. My version is also a bit more messy...

Any comments are more than welcome!

Thanks for the help! BR Muje