Increment a value based upon adjacent cell, but count restarts when number occurs again

Copper Contributor

I'm going to keep this simple, but basically, I have a list of numbers that differ (0,0,1,2,0,0) that need to have a number incremented based on how many identical codes are in the list and restart when the number is counted again, the trouble is that this list won't be ordered numerically, below is an example of what I am looking to do:

 

A1   B1
0      1
0      2
0      3
1      1
0      1
0      2
2      1
3      2
2      1
2      2
0      1

Essentially I need a function that can the count and restart when the same number is counted again, in the adjacent column.

My apologies if the question is relatively easy, but excel is not my strong suit. Thank you.

3 Replies
I may be slow, but I'm not following your example. Could you annotate it for us, thus explaining in just a bit more detail, where the count is incrementing (and why) and where it's restarting (and why). I'm sure it's entirely clear to you, but having the algorithm totally clear is the first step in seeing how Excel can do it.... Thanks

@mathetes , on this sample - we have 3 sequential zeroes and count them one by one till another number appears. When we start numbering this number. If sequence of previous number appears (let say zeroes again) we start numbering of this sequence from scratch.

@lsvoutour 

For such sample

image.png

formula in B2 is

=IF(A2=A1,N(B1)+1,1)

and drag it down.