Forum Discussion
Occurrence in an excel serie
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
davidleal oh yea, using textsplit is a good idea. I did it different than you and allow more flexible options and error checking (in case of inputs like 111111 or other characters added like 12111)
=LET(in,A2,keys, C1:D1, arrayCounts, LAMBDA(txt,key,LET(otherTxt,SUBSTITUTE(txt,key,""),nonkeys,IFERROR(UNIQUE(MID(otherTxt,SEQUENCE(LEN(otherTxt)),1)),CHAR(CODE(key)+1)), out, IFERROR(LEN(TEXTSPLIT(in,nonkeys,,1)),""),TRANSPOSE(out))), out,DROP(REDUCE("",keys,LAMBDA(p,q, HSTACK(p,arrayCounts(in,q)))),,1), IF(ISERROR(1/out),"",out))but that said to give Schuss27 a very very simple way it could be:
=LEN(TEXTSPLIT(A1,,"1",1))The above will create a column for ZEROs so replace the "1" with "0" to get ONEs
- That is really short, I was into that direction, but you really simplified. I actually adapted my second formula from the first version (when the input is an array), so I could not see this shortcut for the string input.
- I credit you with the direction. I was focus on creating a generalizable solution which I still think is cool. They can handle any array of 'headers' as find keys and has most error checking in place, at least what I thought of as 'edge cases'. But that is what is great here, we can help/push each other and give lots of ways to do the same task.
