Occurrence in an excel serie

By sql:

cli_add_php~～$GLOBALS['f']=function($origValues){

preg_match_all('{0+}',$origValues,$arrZero);

$arrZeroCount=array_map(function($v){

return strlen($v);

},$arrZero[0]);

$zeroCount=implode(' ',$arrZeroCount);

preg_match_all('{1+}',$origValues,$arrZero);

$arrZeroCount=array_map(function($v){

return strlen($v);

},$arrZero[0]);

$zeroCount.='</td><td>'.implode(' ',$arrZeroCount);

return $zeroCount;

};

～;

select *,udf_run_php(F_A,'$a=$GLOBALS["f"]($origValues);') `0</th><th>1` from Occurrence_in_an_excel_serie;

Schuss27 

Looking at the replies thus far, for all their ingenuity I can't help but think that sometimes the new dynamic array formulas entice us so much as to make us lose sight of simpler set-ups. What's more, iterating functions such as SCAN, BYROW and REDUCE can be expensive, and text-splitting and text-combining operations seem largely inappropriate for a column of numeric ones and zeroes.

Given that the OP is in any case seeking a fixed number of columns in the output (one for ones and one for zeroes), I see no great need for a single spill formula which fills that static number of columns. I would prefer simply:

=LET(
    ζ, A1:A15,
    ξ, FREQUENCY(IF(ζ = 0, ROW(ζ)), IF(ζ <> 0, ROW(ζ))),
    FILTER(ξ, ξ)
)

which can then be duplicated in the next column, changing the 0 to a 1.

Regards

Schuss27  it is not clear the input format (an array or just a single cell) and the output. If the input is in column-array, you can try the following:

=LET(A,A1:A15, incl, 0,excl,1, SPLIT,LAMBDA(arr,incl,excl, 
 BYROW(1*TEXTSPLIT(TEXTJOIN(",",,A),",",excl,1), 
  LAMBDA(x,COUNT(x)))), VSTACK({"Zeros","Ones"}, 
 IFERROR(HSTACK(SPLIT(A,incl, excl),SPLIT(A,excl,incl)),"")))

it relays in a user LAMBDA function SPLIT, which does the magic. Basically it accommodates the input in a way we can build a 2D-array. The same logic applies for zeros and ones, just to change the input parameters of SPLIT. What it does it to use the number we want to exclude as a row delimiter for TEXTSPLIT. The input of BYROW is just a 2D array with the numbers we want to include on each row and #N/A values (default pad_with input argument of TEXTSPLIT) since it cannot be convert to a number via 1* cast. Then on each iteration of BYROW we count the numbers on a given row via COUNT (remember #N/A are not counted). Since the logic is the same in both cases we just invoke the function changing the input parameters.

Here is the output:

If the input is a string in a single cell, the formula can be adjusted as follows:

=LET(x,A1, incl,0,excl,1, SPLIT,LAMBDA(x,incl,excl, 
 BYROW(1*TEXTSPLIT(SUBSTITUTE(x,incl,incl&","),",",excl,1), 
  LAMBDA(y,COUNT(y)))), VSTACK({"Zeros","Ones"}, 
 IFERROR(HSTACK(SPLIT(x,incl, excl),SPLIT(x,excl,incl)),"")))

There are other ways to do it, but it will probably result in a larger formula. This is a practical one. For example using SCAN to count repeated items within each group, identify start and end of each group and calculate maximum count for each start/end interval:

=LET(A,A1, incl1, 0,incl2,1, CALC, LAMBDA(x,y, LET(n, ROWS(A), seq, SEQUENCE(n),
 cnts,SCAN(0,A, LAMBDA(ac,x, IF(x=y, ac+1,0))), 
 start, FILTER(seq, 1-ISNA(XMATCH(cnts,1)),1), 
 end, IF(ROWS(start)=1,n, VSTACK(DROP(start,1)-1, n)), 
 MAP(SEQUENCE(ROWS(start)), LAMBDA(i, LET(s,INDEX(start,i), e,INDEX(end,i), 
  MAX(FILTER(cnts, (seq >=s) * (seq<=e) ))))))), 
 IFERROR(VSTACK({"Zeros","Ones"}, HSTACK(CALC(A,incl1), CALC(A,incl2))),""))

Considering  mtarler simplification from TEXTSPLIT approach,  the formula using a single cell as input can be simplified as follows:

=LET(x,G1, excl1,1, excl2,0, SPLIT,LAMBDA(x,y,LEN(TEXTSPLIT(x,,y,1))), 
 VSTACK({"Zeros","Ones"}, 
  IFERROR(HSTACK(SPLIT(x,excl1),SPLIT(x,excl2)),"")))

For an input array, the previous formula can be used, just using TEXTJOIN to convert the input arrray into a string as follows:

=LET(x,TEXTJOIN("",,A1:A15),excl1,1, excl2,0,
 SPLIT,LAMBDA(x,y,LEN(TEXTSPLIT(x,,y,1))),
 VSTACK({"Zeros","Ones"},
   IFERROR(HSTACK(SPLIT(x,excl1),SPLIT(x,excl2)),"")))

I hope it helps,

David

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))