Yep, I assumed each series of CX was preceded by a single AY for the pattern
posted. If that's not true it'll show series of As.


"Bernd P" wrote:

Hello Sam,

Your formula is not working. Copy down the AG value into A9 and A10,
for example.

My suggestion consists of two helper columns and one resulting column:
I assume data starts from row 2 and row 1 contains titles in all
columns (text). [If row 1 contains empty cells output in column D
starts from row 3]
Enter into B2:
=IF(LEFT(A2,1)="C",(B10)*(B1+1),--(SUMPRODUCT(--(LEFT(A3:A6,1)="C"))
=4))
Enter into C2:
=(B20)*(MAX(C$1:C1)+1+(C1=0))
Enter into D2:
=IF(ISERROR(MATCH(ROW()-1,C:C,0)),"",INDEX(A:A,MATCH(ROW()-1,C:C,0)))
and now copy C2:D2 down as far as necessary.

Regards,
Bernd