Forum Discussion
Occurrence in an excel serie
Hello, I have an excel column filled with 0 and 1. I would like to count the number of successive 1 and 0 Ex: 011001011100000 The result could be something like 2 columns, I. E the first for 0 an...
Schuss27 I hope to see other improvements but here is a formula that works. I put the lambda declarations inside the LET but could easily be pulled out into its own function(s). Depends if you just need this in 1 spot or all over the worksheet.
=LET(in,A1,keys, C1:D1, Num, LEN(in), SEQ, SEQUENCE(Num), arr, MID(in,SEQ,1),
ArrayCounts, LAMBDA(array,key, REDUCE(0,array, LAMBDA(p,q, IF(CODE(q)=CODE(key), IFERROR(VSTACK(DROP(p,-1),TAKE(p,-1)+1),p+1),IF(INDEX(TAKE(p,-1),1),VSTACK(p,0),p))))),
out,DROP(REDUCE("",keys,LAMBDA(p,q, HSTACK(p,ArrayCounts(arr,q)))),,1),
IF(ISERROR(1/out),"",out))