Comparing time stamp values to remove 'duplicate' time entries (rows).

[Scroll_Slowly]

Hello. Firstly, I would like to express many thanks to those forum members which posts have already helped me achieve many improvements to data processing.
While I am aware there may be different approach, let me explain my procedure here.
Data is recorded with a timestamp value, which is supposed to be every minute. However, some of the files have additional data points, i.e. more than 1 per minute or there is a 1 min value missing (data is recorded to the next minute interval).
Currently, I have some VBA code to compare the minute values and then the seconds with a bunch of IF statements - but I believe this approach is not ideal.

Example in the data set below is row 1 and row 2 data arrive in the same minute interval but row 3 has value for 2 minutes after the first entry, so there is a data point 'missing'.
With my limited understanding of VBA, I have reached a large brick wall.
I would like to correctly identify whether the minute interval exists or if it should be created (by revising an existing time stamp).
I am having trouble conceptualizing the steps to properly discern when the timestamp needs to be changed or if value is in fact a duplicate.
I would appreciate if anyone can provide even the slightest push in the right direction.
Thank you in advance.

Example data set:

Book1
	A	B	C	D
1	07/31/2024 19:48:00 EDT	Zone 2 - AST 1 FLOW RATE	142.897	alarm
2	07/31/2024 19:48:58 EDT	Zone 2 - AST 1 FLOW RATE	143.022	alarm
3	07/31/2024 19:50:00 EDT	Zone 2 - AST 1 FLOW RATE	143.084	alarm
4	07/31/2024 19:50:05 EDT	Zone 2 - AST 1 FLOW RATE	143.084	alarm
5	07/31/2024 19:51:00 EDT	Zone 2 - AST 1 FLOW RATE	144.084	alarm
6	07/31/2024 19:52:00 EDT	Zone 2 - AST 1 FLOW RATE	145.084	alarm
7	07/31/2024 19:53:00 EDT	Zone 2 - AST 1 FLOW RATE	146.084	alarm
Sheet1

Example result to produce:

Book1
	A	B	C	D
1	07/31/2024 19:48:00 EDT	Zone 2 - AST 1 FLOW RATE	142.897	alarm
2	07/31/2024 19:49:00 EDT	Zone 2 - AST 1 FLOW RATE	143.022	alarm
3	07/31/2024 19:50:00 EDT	Zone 2 - AST 1 FLOW RATE	143.084	alarm
4	07/31/2024 19:51:00 EDT	Zone 2 - AST 1 FLOW RATE	144.084	alarm
5	07/31/2024 19:52:00 EDT	Zone 2 - AST 1 FLOW RATE	145.084	alarm
6	07/31/2024 19:53:00 EDT	Zone 2 - AST 1 FLOW RATE	146.084	alarm
Sheet2

 

[myall_blues]

What about this? It sets a tolerance value on the time (in seconds) and if the data timestamp doesn't end in zero seconds it applies the tolerance to see whether the minute should be rounded up or down. Then at output time it ignores duplicates. Produces output as shown with your data:

scroll_slowly.xlsm
	A	B	C	D	E	F	G	H	I
1	07/31/2024 19:48:00 EDT	Zone 2 - AST 1 FLOW RATE	142.897	alarm		07/31/2024 19:48:00 EDT	Zone 2 - AST 1 FLOW RATE	142.897	alarm
2	07/31/2024 19:48:58 EDT	Zone 2 - AST 1 FLOW RATE	143.022	alarm		07/31/2024 19:49:00 EDT	Zone 2 - AST 1 FLOW RATE	143.022	alarm
3	07/31/2024 19:50:00 EDT	Zone 2 - AST 1 FLOW RATE	143.084	alarm		07/31/2024 19:50:00 EDT	Zone 2 - AST 1 FLOW RATE	143.084	alarm
4	07/31/2024 19:50:05 EDT	Zone 2 - AST 1 FLOW RATE	143.084	alarm		07/31/2024 19:51:00 EDT	Zone 2 - AST 1 FLOW RATE	144.084	alarm
5	07/31/2024 19:51:00 EDT	Zone 2 - AST 1 FLOW RATE	144.084	alarm		07/31/2024 19:52:00 EDT	Zone 2 - AST 1 FLOW RATE	145.084	alarm
6	07/31/2024 19:52:00 EDT	Zone 2 - AST 1 FLOW RATE	145.084	alarm		07/31/2024 19:53:00 EDT	Zone 2 - AST 1 FLOW RATE	146.084	alarm
7	07/31/2024 19:53:00 EDT	Zone 2 - AST 1 FLOW RATE	146.084	alarm
Sheet1

Sub scroll_slowly()
    Const tolerance As Double = 29 / 60 ' tolerance in seconds
    Dim lastRow As Long, currDate As Integer, currTime As Double, arrRowdim As Long
    Dim i As Long, outrow As Long
    Dim inData As Variant, arrSplitString() As String
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    inData = Range("A1:D" & lastRow)
    ReDim Preserve inData(1 To UBound(inData, 1), 1 To UBound(inData, 2) + 3)
    For i = 1 To UBound(inData, 1)
        arrSplitString = Split(inData(i, 1), " ")
        inData(i, UBound(inData, 2) - 2) = arrSplitString(0)
        If Second(TimeValue(arrSplitString(1))) = 0 Then
            inData(i, UBound(inData, 2) - 1) = arrSplitString(1)
        Else
            currTime = Minute(TimeValue(arrSplitString(1))) + _
                Second(TimeValue(arrSplitString(1))) / 60
            If currTime - tolerance > Minute(TimeValue(arrSplitString(1))) Then
                currTime = Int(currTime) + 1
            ElseIf currTime - tolerance < Minute(TimeValue(arrSplitString(1))) Then
                currTime = Int(currTime)
            'Else
                'currTime = Int(currTime)
            End If
            inData(i, UBound(inData, 2) - 1) = Hour(TimeValue(arrSplitString(1))) & ":" & _
                currTime & ":00"
        End If
        inData(i, UBound(inData, 2)) = arrSplitString(2)
    Next
    outrow = 1
    For i = 1 To UBound(inData, 1)
        If i = 1 Then
            Cells(outrow, 6).Value = inData(i, 5) & " " & inData(i, 6) & " " & inData(i, 7)
            Cells(outrow, 7).Value = inData(i, 2)
            Cells(outrow, 8).Value = inData(i, 3)
            Cells(outrow, 9).Value = inData(i, 4)
            outrow = outrow + 1
        ElseIf inData(i, 6) <> inData(i - 1, 6) Then
                Cells(outrow, 6).Value = inData(i, 5) & " " & inData(i, 6) & " " & inData(i, 7)
                Cells(outrow, 7).Value = inData(i, 2)
                Cells(outrow, 8).Value = inData(i, 3)
                Cells(outrow, 9).Value = inData(i, 4)
                outrow = outrow + 1
        End If
    Next i
End Sub

 

[Scroll_Slowly]

myall_blues, thank you very much for the response.
Your code gets me much closer to what I needed as opposed to what I was working with in my own code.
I ran a limited test with a larger data set, but the results look very good.
General file size is around 10k lines.
There are a few additional steps I will attempt to implement to achieve the desired final result.
Your code gives me confidence that it will be much easier given the output.
I really appreciate the help and accept the answer as a solution.

 

[myall_blues]

You’re welcome. Thanks for the response.

 

[felixstraube]

What would you like to do if you have two rows with the same timestamp (or the resulting timestamp after calculation) but the values in column C are different? Would you like to take the minimum value, the maximum, average, the first value and discard the second?

 

[felixstraube]

If you would like the average for example, here is a formula alternative:

Book9.xlsx
	A	B	C	D	E	F	G
1	07/31/2024 19:48:00 EDT	Zone 2 - AST 1 FLOW RATE	142.897	alarm		2024-07-31 19:48:00	142.897
2	07/31/2024 19:48:58 EDT	Zone 2 - AST 1 FLOW RATE	143.022	alarm		2024-07-31 19:49:00	143.022
3	07/31/2024 19:50:00 EDT	Zone 2 - AST 1 FLOW RATE	143.084	alarm		2024-07-31 19:50:00	143.084
4	07/31/2024 19:50:05 EDT	Zone 2 - AST 1 FLOW RATE	143.084	alarm		2024-07-31 19:51:00	144.084
5	07/31/2024 19:51:00 EDT	Zone 2 - AST 1 FLOW RATE	144.084	alarm		2024-07-31 19:52:00	145.084
6	07/31/2024 19:52:00 EDT	Zone 2 - AST 1 FLOW RATE	145.084	alarm		2024-07-31 19:53:00	146.084
7	07/31/2024 19:53:00 EDT	Zone 2 - AST 1 FLOW RATE	146.084	alarm
8
Sheet2

Cell Formulas
Range	Formula
F1:G6	F1	=LET(timestamp, ROUND(VALUE(TEXTBEFORE($A$1:$A$7, " EDT"))*24*60, 0)/24/60, values, $C$1:$C$7, uTimestamp, UNIQUE(timestamp), uValues, BYROW(uTimestamp, LAMBDA(x, AVERAGE(FILTER(values, timestamp=x)))), HSTACK(uTimestamp, uValues) )
Dynamic array formulas.

 

[Scroll_Slowly]

felixstraube, thank you for the response.
Ideally, an average of multiple readings would be preferred but as long as there aren't multiple readings it would be sufficient.
I have a few data sets that come in this form, which are eventually combined into another file for further tracking and analysis.
For some reason, the data is reported inconsistently for each zone, resulting in some files differing by the 1,000's of lines.
The formula appears to work very well and returns the expected number of data points for time interval selected.
I would like to test more later, appears very promising.

 

[felixstraube]

Just let us know if we could help you further.