Turning semiperiodic column values into continuously increasing column values

[meriadoc]

First post here.

Let me describe the problem:

A measurement device sends periodic timestamps (integer) values between 0-59 in column A, starting at A2.
The time series can start at any value between 0-59.
The number of transmitted time stamps per second is variable.

I would like to renumber the column values starting in B2, so that

a) the renumbered time series starts with 0
b) the time series will add 60 to whenever its going through its 1st, 2nd.. intervall.

An example might look like this, starting in column A2, going downward:

A B
5 0
5 0
5 0
6 1
6 1
6 1
6 1
7 2
7 2
...
59 54
59 54 <- How to detect and set the proper number?
0 55
0 55
...

I have come up with a partial solution for a) that goes like this

Put a -1 into B1

Use in B2

=IF(COUNTIF(A$1:A2;A2)=1;B1+1;B1)

Use in C2

=IF(COUNTIF(A$2:A2;A2)=1;MAX(B$1:B1)+1;VLOOKUP(A2;A$1:B1;2;0))

I was wondering whether there is a better way to solve part a) of the problem by starting with a general increment series in B that detects whenever the value in column a changes. For b) I havent really found a solution. Any help will be much appreciated.

Thank you kindly.

[shukla.ankur281190]

Welcome to forum,

Please provide the expected result

[meriadoc]

The incoming time series is shown in column A and the expected result is shown in column B in the text above. In the example.xls file the original time series is in Column A and the partial solution is in Column C.

The computed time series must be renumbered such that the first value starts with 0, as shown in B, and it must be continuous such that the timer doesnt switch back from second 59 to 0, but instead continues with 60 onward.

I believe the most effective way is to detect a number change in column A and then perform an increment of B starting with 0. However, I haven't found a solution to this.

[shukla.ankur281190]

May be try

=IF(A2<>A1,B1+1,B1)

[meriadoc]

Thanks for the hint.

I used column B to compute the expression =IF(ISBLANK(A3),"",IF(A2=A3,B1,B1+1)) so that I get the incremental series ignoring blank cells.
I followed up in column C with =IF(ISBLANK(B1),"",B1)

Solution is attached. If anyone has a way to directly shift the data in column B one line down. let me know.

I consider this as solved - thanks again.