Increment numbers when value changes in another column-CodePudding

I currently have a column of letters in B2:B11 alongside numbers that increment by 1 when letters appear consecutively (C2:C11). When a new letter appears, the sequence resets and starts from 1 again.

This is the formula I'm using:

=SCAN(0,B2:B11,
LAMBDA(a,b,
IF(OFFSET(b,-1,0)=b,
a 1,1)
)
)

It works fine when the letters are together in blocks, but when they are separated, any previous instances of a letter are forgotten about.

I want to find a solution that uses a single formula. I believe I'm on the right path using the new SCAN() function. Please don't suggest methods involving classic formulas or tables (I've already seen these).

The values returned should match those in D2:D11.

CodePudding user response：

Here the array version in E2:

=COUNTIF(OFFSET(A2,0,0,SEQUENCE(ROWS(A2:A11)),1),A2:A11)

or using LET for easier maintenance:

=LET(start, A2, range, A2:A11, 
  COUNTIF(OFFSET(start,0,0,SEQUENCE(ROWS(range)),1),range))

This is the output:

The idea was taken from here: