# ASCAN  	Array SCAN, 3 in 1 function, SCAN by row, by column, by array



## Xlambda (Sep 30, 2021)

*ASCAN**, * *A*rray *SCAN*, *3 in 1 function*, *SCAN* *by row*, *by column*, *by array*. This is my take of tboulden's SCANBYROW/BYCOL.
Uses *only* *new!! SCAN* lambda helper function, no need of byrow,bycol,makearray, or lambda as arguments.

```
=LAMBDA(a,[d],
    LET(y,IF(d=1,TRANSPOSE(a),a),s,SCAN(0,y,LAMBDA(v,a,v+a)),r,ROWS(s),c,COLUMNS(s),sr,SEQUENCE(r)-1,
       x,s-IF(d,IFERROR(INDEX(INDEX(s,,c),sr)*sr^0,0),0),
       IF(d=1,TRANSPOSE(x),x)
   )
)
```
LAMBDA 1.1.3.xlsxABCDEFGHIJKLMNOPQRST1d,omitted (by array)d,-1 (by rows)d,1 (by clms)2sample 1a=ASCAN(A3:D6)=ASCAN(A3:D6,-1)=ASCAN(A3:D6,1)312341361013610123445678152128365111826681012591011124555667891930421518212461314151691105120136132742582832364078d,omitted (by array)d,-1 (by rows)d,1 (by clms)9sample 2a=ASCAN(A10#)=ASCAN(A10#,-1)=ASCAN(A10#,1)1012341361013610123411567815212836511182668101212910111245556678919304215182124131314151691105120136132742582832364014171819201531711902101735547445505560152122232423125327630021436690667278841617Note: Did not set an extra argument for any kind of "initial value", since the only thing it does is too simple,18adds a constant value to the final outcome , like in 10+ASCAN(a)19ASCAN postCell FormulasRangeFormulaF2,P9,K9,F9,P2,K2F2=FORMULATEXT(F3)F3:I6F3=ASCAN(A3:D6)K3:N6K3=ASCAN(A3:D6,-1)P3:S6P3=ASCAN(A3:D6,1)A10:D15A10=SEQUENCE(6,4)F10:I15F10=ASCAN(A10#)K10:N15K10=ASCAN(A10#,-1)P10:S15P10=ASCAN(A10#,1)Dynamic array formulas.


----------



## Xlambda (Sep 30, 2021)

*Second draft, shorter*. *T**he concept of SCAN by row or by col is extremely simple. We SCAN the whole array with whatever operation and then we amend the result with the values that keep first row or first column intact. *

```
=LAMBDA(a,[d],
    LET(y,IF(d=1,TRANSPOSE(a),a),s,SCAN(0,y,LAMBDA(v,a,v+a)),
       x,s-IF(d,INDEX(s,,1)-INDEX(y,,1)),
       IF(d=1,TRANSPOSE(x),x)
    )
)
```
LAMBDA 1.1.3.xlsxABCDEFGHIJKLMNOPQRST1same outcome as befored,omitted (by array)d,-1 (by rows)d,1 (by clms)2sample 1a=ASCAN(A3:D6)=ASCAN(A3:D6,-1)=ASCAN(A3:D6,1)312341361013610123445678152128365111826681012591011124555667891930421518212461314151691105120136132742582832364078d,omitted (by array)d,-1 (by rows)d,1 (by clms)9sample 2a=ASCAN(A10#)=ASCAN(A10#,-1)=ASCAN(A10#,1)10123413610136101234115678152128365111826681012129101112455566789193042151821241313141516911051201361327425828323640141718192015317119021017355474455055601521222324231253276300214366906672788416ASCAN postCell FormulasRangeFormulaF2,P9,K9,F9,P2,K2F2=FORMULATEXT(F3)F3:I6F3=ASCAN(A3:D6)K3:N6K3=ASCAN(A3:D6,-1)P3:S6P3=ASCAN(A3:D6,1)A10:D15A10=SEQUENCE(6,4)F10:I15F10=ASCAN(A10#)K10:N15K10=ASCAN(A10#,-1)P10:S15P10=ASCAN(A10#,1)Dynamic array formulas.


----------



## Xlambda (Sep 30, 2021)

Third draft. More versatility for *real life scenarios*, can handle blanks, null strings, text, errors, d argument wrong input.

```
=LAMBDA(a,[d],
    LET(n,ISNUMBER(a),r,IF(n,a,0),o,IF(d,d^0*SIGN(d),0),y,IF(o=1,TRANSPOSE(r),r),
      s,SCAN(0,y,LAMBDA(v,a,v+a)),x,s-IF(o,INDEX(s,,1)-INDEX(y,,1)),
      IF(n,IF(o=1,TRANSPOSE(x),x),a)
    )
)
```
LAMBDA 1.1.3.xlsxABCDEFGHIJKLMNOPQRST1d,omitted (by array)d,-1 (by rows)d,1 (by clms)2sample=ASCAN(A3:D7)=ASCAN(A3:D7,-1)=ASCAN(A3:D7,1)31a341a481a481a3445678131926345111826661012591011#N/A435364#N/A91930#N/A151621#N/A613 15167792108132844283628717182012514301631735055453404889Note: Blanks will be returned 0's because a blnk is a 0 and a null string at the same time.10null string B6=""Null strings will remain intact as any other text value.11blankThe functions works only with the numeric layer, everything else returned as in the initial array12text13errorwhen d out of range, d <> {0,-1,1} , if d<0,d=-1, if d>0, d=114d,-3 <=> d=-1d,4 <=> d=115=ASCAN(A3:D7,-3)=ASCAN(A3:D7,4)161a481a341751118266610121891930#N/A151621#N/A19132844283628201735055453404821ASCAN post 3Cell FormulasRangeFormulaF2,K15,F15,P2,K2F2=FORMULATEXT(F3)F3:I7F3=ASCAN(A3:D7)K3:N7K3=ASCAN(A3:D7,-1)P3:S7P3=ASCAN(A3:D7,1)D5D5=NA()B6B6=""F16:I20F16=ASCAN(A3:D7,-3)K16:N20K16=ASCAN(A3:D7,4)Dynamic array formulas.


----------



## Xlambda (Sep 30, 2021)

Following *first concept* (*we use only SCAN for the whole array, and we amend the result to keep the first row or first clm of initial array intact*) , this is how it looks for appending by row.
The trick using this concept in general is finding the proper "amending" formula.
Since I do not see any practicability in real life, did not allocate a dedicated lambda function.

```
=LAMBDA(a,LET(s,SCAN("",A3:D5,LAMBDA(v,a,v&a)),RIGHT(s,LEN(s)-LEN(INDEX(s,,1))+LEN(INDEX(a,,1)))))
```
LAMBDA 1.1.3.xlsxABCDEFGHIJKLMNOPQ12samplea=LAMBDA(a,LET(s,SCAN("",A3:D5,LAMBDA(v,a,v&a)),RIGHT(s,LEN(s)-LEN(INDEX(s,,1))+LEN(INDEX(a,,1)))))(A3:D5)3abcdaababcabcd4x234xx2x23x2345rt678967rtrt67rt6789rt6789676ASCAN post 4Cell FormulasRangeFormulaF2F2=FORMULATEXT(F3)F3:I5F3=LAMBDA(a,LET(s,SCAN("",A3:D5,LAMBDA(v,a,v&a)),RIGHT(s,LEN(s)-LEN(INDEX(s,,1))+LEN(INDEX(a,,1)))))(A3:D5)Dynamic array formulas.


----------



## Xlambda (Sep 30, 2021)

If I said no lambda, then should be a formula, a simple let:
*=LET(a,A3:D5,s,SCAN("",a,LAMBDA(v,a,v&a)),RIGHT(s,LEN(s)-LEN(INDEX(s,,1))+LEN(INDEX(a,,1))))*
LAMBDA 1.1.3.xlsxABCDEFGHIJKLMNO12samplea=LET(a,A3:D5,s,SCAN("",a,LAMBDA(v,a,v&a)),RIGHT(s,LEN(s)-LEN(INDEX(s,,1))+LEN(INDEX(a,,1))))3abcdaababcabcd4x234xx2x23x2345rt678967rtrt67rt6789rt6789676ASCAN post 4Cell FormulasRangeFormulaF2F2=FORMULATEXT(F3)F3:I5F3=LET(a,A3:D5,s,SCAN("",a,LAMBDA(v,a,v&a)),RIGHT(s,LEN(s)-LEN(INDEX(s,,1))+LEN(INDEX(a,,1))))Dynamic array formulas.


----------



## Xlambda (Oct 1, 2021)

*Second concept*. Does not require a lambda, is a simple formula that we can write anywhere according to context.
Uses *SCAN* embedded in *MAKEARRAY* (similar concept in AMORTIZE)
LAMBDA 1.1.3.xlsxABCDEFGHIJKLMN1adding by row2sample array 5x4=MAKEARRAY(5,4,LAMBDA(r,c,LET(x,INDEX(A3#,r,),s,SCAN(0,x,LAMBDA(v,a,v+a)),INDEX(s,1,c))))3123413610456785111826591011129193042613141516132742587171819201735547489appending by row10=MAKEARRAY(5,4,LAMBDA(r,c,LET(x,INDEX(A3#,r,),s,SCAN("",x,LAMBDA(v,a,v&a)),INDEX(s,1,c))))111121231234125565675678139910910119101112141313141314151314151615171718171819171819201617multiplication by row18=MAKEARRAY(5,4,LAMBDA(r,c,LET(x,INDEX(A3#,r,),s,SCAN(1,x,LAMBDA(v,a,a*v)),INDEX(s,1,c))))191262420530210168021990990118802213182273043680231730658141162802425division by row26=MAKEARRAY(5,4,LAMBDA(r,c,LET(x,INDEX(A3#,r,),s,SCAN(1,x,LAMBDA(v,a,a/v)),INDEX(s,1,c))))27121.52.6666672851.25.8333331.3714292991.1111119.91.21212130131.07692313.928571.14871831171.05882417.944441.11455132ASCAN post 5Cell FormulasRangeFormulaF2,F26,F18,F10F2=FORMULATEXT(F3)A3:D7A3=SEQUENCE(5,4)F3:I7F3=MAKEARRAY(5,4,LAMBDA(r,c,LET(x,INDEX(A3#,r,),s,SCAN(0,x,LAMBDA(v,a,v+a)),INDEX(s,1,c))))F11:I15F11=MAKEARRAY(5,4,LAMBDA(r,c,LET(x,INDEX(A3#,r,),s,SCAN("",x,LAMBDA(v,a,v&a)),INDEX(s,1,c))))F19:I23F19=MAKEARRAY(5,4,LAMBDA(r,c,LET(x,INDEX(A3#,r,),s,SCAN(1,x,LAMBDA(v,a,a*v)),INDEX(s,1,c))))F27:I31F27=MAKEARRAY(5,4,LAMBDA(r,c,LET(x,INDEX(A3#,r,),s,SCAN(1,x,LAMBDA(v,a,a/v)),INDEX(s,1,c))))Dynamic array formulas.


----------



## Xlambda (Oct 1, 2021)

*Humble Conclusion!!*
I think that these new lambda helper functions are so intrinsic versatile and powerful that their main purpose is not to help us in lambda constructions, quite the opposite, *they were created to help us in lambdas deconstruction.*
I know, first instinct is, wow, I can write a lambda doing this. If we'll write a lambda for every construction that these new functions are capable of, soon, our name manager will be oversaturated with a tone of lambdas, variables, fancy arguments, doing nothing, never used in real life, never embraced by other users, doing stuff that a simple short formula can do. It's so unproductive as gluing lego pieces. And these exercises here are a proof of how simple is to omit a lambda. Even if these examples have no use in real life, they help us understand how simple and versatile these constructions can be. We are witnessing a paradigm shift. My 2 cents. Sorry for the long talk. To end on a funny note, ASCAN will survive, running totals are practical. ?✌
LAMBDA 1.1.3.xlsxABCDEFGHIJKLMN1adding by col2sample array 5x4=MAKEARRAY(5,4,LAMBDA(r,c,LET(x,INDEX(A3#,,c),s,SCAN(0,x,LAMBDA(v,a,v+a)),INDEX(s,r,1))))312341234456786810125910111215182124613141516283236407171819204550556089appending by col10=MAKEARRAY(5,4,LAMBDA(r,c,LET(x,INDEX(A3#,,c),s,SCAN("",x,LAMBDA(v,a,v&a)),INDEX(s,r,1))))11123412152637481315926103711481214159132610143711154812161515913172610141837111519481216201617multiplication by col18=MAKEARRAY(5,4,LAMBDA(r,c,LET(x,INDEX(A3#,,c),s,SCAN(1,x,LAMBDA(v,a,v*a)),INDEX(s,r,1))))19123420512213221451202313842258516803465614423994530240658351228802425division by col26=MAKEARRAY(5,4,LAMBDA(r,c,LET(x,INDEX(A3#,,c),s,SCAN(1,x,LAMBDA(v,a,a/v)),INDEX(s,r,1))))27123428532.33333332291.83.33333334.71428576307.22222224.23.18181822.6666667312.35384624.28571435.97142867.532ASCAN post 6Cell FormulasRangeFormulaF2,F26,F18,F10F2=FORMULATEXT(F3)A3:D7A3=SEQUENCE(5,4)F3:I7F3=MAKEARRAY(5,4,LAMBDA(r,c,LET(x,INDEX(A3#,,c),s,SCAN(0,x,LAMBDA(v,a,v+a)),INDEX(s,r,1))))F11:I15F11=MAKEARRAY(5,4,LAMBDA(r,c,LET(x,INDEX(A3#,,c),s,SCAN("",x,LAMBDA(v,a,v&a)),INDEX(s,r,1))))F19:I23F19=MAKEARRAY(5,4,LAMBDA(r,c,LET(x,INDEX(A3#,,c),s,SCAN(1,x,LAMBDA(v,a,v*a)),INDEX(s,r,1))))F27:I31F27=MAKEARRAY(5,4,LAMBDA(r,c,LET(x,INDEX(A3#,,c),s,SCAN(1,x,LAMBDA(v,a,a/v)),INDEX(s,r,1))))Dynamic array formulas.


----------



## Xlambda (Oct 9, 2021)

*What if we want to apply various functions to each row of an array, (UNIQUE or FILTER or SORT), instead of only simple operations? *
(Exactly what AFUSBYROW does without the lambda helper functions)
It's quite easy, using a trick. Since in this case, SCAN cannot return "row" arrays, as we need, we can TEXTJOIN the result of each row, in a single value, and split them afterwards.
*Another argument in favor of how a simple and short lambda "formula" can replace the functionality of custom-made dedicated functions.*
LAMBDA 1.1.4.xlsxABCDEFGHIJKLMNOPQRSTUVW11. Extracting UNIQUE values of each row of an array23sampleadother functions44333114AFUSBYROW52112775ATEXTSPLIT6866558978the result we are looking for:9=AFUSBYROW(ad,,1)=SCAN("",SEQUENCE(3),LAMBDA(v,i,TEXTJOIN(",",,UNIQUE(INDEX(ad,i,),1))))104314,3,11121752,1,7,5next step, splitting1286598,6,5,9=ATEXTSPLIT(H10#)1343114217515865916as we see, we get the same result172. SORTing in ascending order, each row1819the result we are looking for:lambda formula20=AFUSBYROW(ad,,,1)=SCAN("",SEQUENCE(3),LAMBDA(v,i,TEXTJOIN(",",,SORT(INDEX(ad,i,),,,1))))2111333441,1,3,3,3,4,42211225771,1,2,2,5,7,7next step, splitting2355668895,5,6,6,8,8,9=ATEXTSPLIT(I21#)24113334425112257726556688927as we see, we get the same result283. SORTing in descending order, each row2930the result we are looking for:lambda formula31=AFUSBYROW(ad,,,-1)=SCAN("",SEQUENCE(3),LAMBDA(v,i,TEXTJOIN(",",,SORT(INDEX(ad,i,),,-1,1))))3244333114,4,3,3,3,1,13377522117,7,5,2,2,1,1next step, splitting3498866559,8,8,6,6,5,5=ATEXTSPLIT(I32#)35443331136775221137988665538as we see, we get the same result39ASCAN post 7Cell FormulasRangeFormulaA9,K34,I31,A31,K23,I20,A20,K12,H9A9=FORMULATEXT(A10)A10:D12A10=AFUSBYROW(ad,,1)H10:H12H10=SCAN("",SEQUENCE(3),LAMBDA(v,i,TEXTJOIN(",",,UNIQUE(INDEX(ad,i,),1))))K13:N15K13=ATEXTSPLIT(H10#)A21:G23A21=AFUSBYROW(ad,,,1)I21:I23I21=SCAN("",SEQUENCE(3),LAMBDA(v,i,TEXTJOIN(",",,SORT(INDEX(ad,i,),,,1))))K24:Q26,K35:Q37K24=ATEXTSPLIT(I21#)A32:G34A32=AFUSBYROW(ad,,,-1)I32:I34I32=SCAN("",SEQUENCE(3),LAMBDA(v,i,TEXTJOIN(",",,SORT(INDEX(ad,i,),,-1,1))))Dynamic array formulas.Named RangesNameRefers ToCellsad='ASCAN post 7'!$A$4:$G$6I32, I21, H10, A32, A21, A10


----------



## tboulden (Oct 9, 2021)

Xlambda said:


> SCAN cannot return "row" arrays, as we need, we can TEXTJOIN


A little more readable using BYROW and ARRAYTOTEXT:
LAMBDA_SCANBY.xlsxABCDEFGHIJKLMN143331142211277538665589454, 3, 1=BYROW(A1:G3,
    LAMBDA(row_,
        ARRAYTOTEXT(UNIQUE(TRANSPOSE(row_)))
    ))62, 1, 7, 578, 6, 5, 989101, 1, 3, 3, 3, 4, 4=BYROW(A1:G3,
    LAMBDA(row_,
        ARRAYTOTEXT(SORT(TRANSPOSE(row_)))
    ))111, 1, 2, 2, 5, 7, 7125, 5, 6, 6, 8, 8, 91314154, 4, 3, 3, 3, 1, 1=BYROW(A1:G3,
    LAMBDA(row_,
        ARRAYTOTEXT(SORT(TRANSPOSE(row_),,-1))
    ))167, 7, 5, 2, 2, 1, 1179, 8, 8, 6, 6, 5, 518Sheet1Cell FormulasRangeFormulaI5:I7I5=BYROW(A1:G3,
    LAMBDA(row_,
        ARRAYTOTEXT(UNIQUE(TRANSPOSE(row_)))
    ))J5,J15,J10J5=FORMULATEXT(I5)I10:I12I10=BYROW(A1:G3,
    LAMBDA(row_,
        ARRAYTOTEXT(SORT(TRANSPOSE(row_)))
    ))I15:I17I15=BYROW(A1:G3,
    LAMBDA(row_,
        ARRAYTOTEXT(SORT(TRANSPOSE(row_),,-1))
    ))Dynamic array formulas.


----------



## Xlambda (Oct 9, 2021)

Super cool!! ✌ There are so many ways. BYROW still cannot "spill" rows. And I have used  TEXTJOIN because of its "ignore empty argument". It's good for handling blanks/null strings. Examples will follow.


----------



## Xlambda (Sep 30, 2021)

*ASCAN**, * *A*rray *SCAN*, *3 in 1 function*, *SCAN* *by row*, *by column*, *by array*. This is my take of tboulden's SCANBYROW/BYCOL.
Uses *only* *new!! SCAN* lambda helper function, no need of byrow,bycol,makearray, or lambda as arguments.

```
=LAMBDA(a,[d],
    LET(y,IF(d=1,TRANSPOSE(a),a),s,SCAN(0,y,LAMBDA(v,a,v+a)),r,ROWS(s),c,COLUMNS(s),sr,SEQUENCE(r)-1,
       x,s-IF(d,IFERROR(INDEX(INDEX(s,,c),sr)*sr^0,0),0),
       IF(d=1,TRANSPOSE(x),x)
   )
)
```
LAMBDA 1.1.3.xlsxABCDEFGHIJKLMNOPQRST1d,omitted (by array)d,-1 (by rows)d,1 (by clms)2sample 1a=ASCAN(A3:D6)=ASCAN(A3:D6,-1)=ASCAN(A3:D6,1)312341361013610123445678152128365111826681012591011124555667891930421518212461314151691105120136132742582832364078d,omitted (by array)d,-1 (by rows)d,1 (by clms)9sample 2a=ASCAN(A10#)=ASCAN(A10#,-1)=ASCAN(A10#,1)1012341361013610123411567815212836511182668101212910111245556678919304215182124131314151691105120136132742582832364014171819201531711902101735547445505560152122232423125327630021436690667278841617Note: Did not set an extra argument for any kind of "initial value", since the only thing it does is too simple,18adds a constant value to the final outcome , like in 10+ASCAN(a)19ASCAN postCell FormulasRangeFormulaF2,P9,K9,F9,P2,K2F2=FORMULATEXT(F3)F3:I6F3=ASCAN(A3:D6)K3:N6K3=ASCAN(A3:D6,-1)P3:S6P3=ASCAN(A3:D6,1)A10:D15A10=SEQUENCE(6,4)F10:I15F10=ASCAN(A10#)K10:N15K10=ASCAN(A10#,-1)P10:S15P10=ASCAN(A10#,1)Dynamic array formulas.


----------



## tboulden (Oct 9, 2021)

Xlambda said:


> BYROW still cannot "spill" rows.


Correct, but as I mentioned in SCANBYROW, we can thunk the results and get them back out with MAKEARRAY; I think the usefulness of this to have an array-of-arrays will be important for some use-cases.

LAMBDA_SCANBY.xlsxABCDEFGHIJKLMNOPQRSTU14333114221127753866558945431#REF!=LET(
    thunks,
        BYROW(A1:G3,
            LAMBDA(row_,
                LAMBDA(TRANSPOSE(UNIQUE(TRANSPOSE(row_))))
            )),
    rows_,ROWS(thunks),
    cols_,MAX(MAP(thunks,LAMBDA(thunk,COLUMNS(thunk())))),
    MAKEARRAY(rows_,cols_,
        LAMBDA(i,j,
            INDEX(
                INDEX(thunks,i,1)(),
                1,
                j
            )
        )
    )
)62175786598910111213141516171819202122Sheet1Cell FormulasRangeFormulaI5:L7I5=LET(
    thunks,
        BYROW(A1:G3,
            LAMBDA(row_,
                LAMBDA(TRANSPOSE(UNIQUE(TRANSPOSE(row_))))
            )),
    rows_,ROWS(thunks),
    cols_,MAX(MAP(thunks,LAMBDA(thunk,COLUMNS(thunk())))),
    MAKEARRAY(rows_,cols_,
        LAMBDA(i,j,
            INDEX(
                INDEX(thunks,i,1)(),
                1,
                j
            )
        )
    )
)N5N5=FORMULATEXT(I5)Dynamic array formulas.


----------



## Xlambda (Oct 9, 2021)

Of course it can be tricked in even more ways, but still, not for real life. One day excel will make it dynamic.? ✌


----------



## Xlambda (Oct 9, 2021)

SCAN vs BYROW
LAMBDA 1.1.4.xlsxABCDEFGHIJKLMNOPQRSTUVWXY1SCAN vs BYROW versatility2In general SCAN is more versatile because we can control the "iteration argument".3Also we are not limited to one direction, we can use sections of an array, plus initial value could be "silent" or cumulative. 4Examples:51. First time seen on one of MrExcel YT videos , an unusual type of sequencial extraction.6Instead of a regular:↓he needed this :↓For SCAN versatility, very easy7↓↓=SCAN("",SEQUENCE(6),LAMBDA(v,i,LET(j,IF(MOD(i,2),1,-1),TEXTJOIN("|",,SORT(INDEX(A8#,i,),,j,1)))))812345123451|2|3|4|5967891010987610|9|8|7|6=ATEXTSPLIT(M8#,"|")101112131415111213141511|12|13|14|1512345111617181920201918171620|19|18|17|16109876122122232425212223242521|22|23|24|251112131415132627282930302928272630|29|28|27|26201918171614("snake" sequence)212223242515302928272616MrExcel YT link17Better ESPN Fantasy Draft Recap In Excel Free Download - 242618192. dynamic selection2021sort ascending between rows 22262324sample=ATEXTSPLIT(SCAN("",SEQUENCE(G22-G21+1,,G21),LAMBDA(v,i,TEXTJOIN("|",,SORT(INDEX(A25:D31,i,),,,1)))),"|")2565721245264215568927865936782873861459299154257103010572315810732ASCAN post 8Cell FormulasRangeFormulaM7,G24,O9M7=FORMULATEXT(M8)A8:E13A8=SEQUENCE(6,5)M8:M13M8=SCAN("",SEQUENCE(6),LAMBDA(v,i,LET(j,IF(MOD(i,2),1,-1),TEXTJOIN("|",,SORT(INDEX(A8#,i,),,j,1)))))O10:S15O10=ATEXTSPLIT(M8#,"|")G25:J29G25=ATEXTSPLIT(SCAN("",SEQUENCE(G22-G21+1,,G21),LAMBDA(v,i,TEXTJOIN("|",,SORT(INDEX(A25:D31,i,),,,1)))),"|")Dynamic array formulas.


----------



## Xlambda (Oct 9, 2021)

*TEXTJOIN vs ARRAYTOTEXT*
LAMBDA 1.1.4.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD1TEXTJOIN vs ARRAYTOTEXT2TEXTJOIN is more versatile in general because of its "ignore empty" argument, and its flexibility to choose any delimiter, even array delimiter.3ARRAYTOTEXT, only "," delimiter, no "ignore empty".  "," could be a problem with the final splitting, if the cells contain already ","'s. 4With textjoin, no problem, we choose any delim we want.561. Extract only the unique numeric values of each row of the following array (ae)78unique all values9ae=SCAN("",SEQUENCE(3),LAMBDA(v,i,TEXTJOIN("|",1,UNIQUE(INDEX(ae,i,),1))))10b33aa4d14b|3|a|4|d|1117xx5x5y8x7|x|5|y|812m6nn66mo6m|6|n|o1314textthe only thing we have to modify for numeric values15numbersis "ae" with:=IF(ISTEXT(ae),"",ae), textjoin will ignore the gaps=ARRAYTOTEXT(L16:T16) does not16 33414, 3, 3, , , 4, , 1, 4177558=ARRAYTOTEXT(L16:T16,1) still not good186666{"",3,3,"","",4,"",1,4}1920=SCAN("",SEQUENCE(3),LAMBDA(v,i,TEXTJOIN("|",1,UNIQUE(INDEX(IF(ISTEXT(ae),"",ae),i,),1))))21↓=ATEXTSPLIT(K22#,"|")223|4|1341237|5|8758other functions2466ATEXTSPLIT25261. Extract only the unique non numeric values of each row of (ae)2728single cell  ( we use ae=IF(ISNUMBER(ae),"",ae)2930=ATEXTSPLIT(SCAN("",SEQUENCE(3),LAMBDA(v,i,TEXTJOIN("|",1,UNIQUE(INDEX(IF(ISNUMBER(ae),"",ae),i,),1)))),"|")31bad32xyNotes:33mnoNo need to say that whatever crazy conditions will apply to initial array, the formula will comply.34If we need, for example, unique extraction of numeric values >=10 and <100, no problem.35Rhetoric question: What custom-made function can ever cover everything that a 5" long lambda "formula" can do in 2 sec?36Very few lambda functions will survive.37ASCAN post 9Cell FormulasRangeFormulaK9,D30,N21,L15K9=FORMULATEXT(K10)K10:K12K10=SCAN("",SEQUENCE(3),LAMBDA(v,i,TEXTJOIN("|",1,UNIQUE(INDEX(ae,i,),1))))Z15Z15=FORMULATEXT(Z16)&" does not"L16:T18L16=IF(ISTEXT(ae),"",ae)Z16Z16=ARRAYTOTEXT(L16:T16)Z17Z17=FORMULATEXT(Z18)&" still not good"Z18Z18=ARRAYTOTEXT(L16:T16,1)K20K20=FORMULATEXT(K22)K22:K24K22=SCAN("",SEQUENCE(3),LAMBDA(v,i,TEXTJOIN("|",1,UNIQUE(INDEX(IF(ISTEXT(ae),"",ae),i,),1))))N22:P24N22=ATEXTSPLIT(K22#,"|")D31:F33D31=ATEXTSPLIT(SCAN("",SEQUENCE(3),LAMBDA(v,i,TEXTJOIN("|",1,UNIQUE(INDEX(IF(ISNUMBER(ae),"",ae),i,),1)))),"|")Dynamic array formulas.Named RangesNameRefers ToCellsae='ASCAN post 9'!$A$10:$I$12L16, K22, K10, D31


----------



## tboulden (Oct 9, 2021)

Below I perform practically the same snake-sequence calc with BYROW/TEXTJOIN that you're performing with SCAN/TEXTJOIN. The main difference is BYROW construction has SEQUENCE(,5) in the TEXTJOIN, not sure why your version doesn't also work with BYROW, any ideas?

The whole point of the LAMBDA helpers is to add more tools, so there will be multiple ways to do the same task generally speaking; I'm mainly concerned about clarity and best practices. In your SCAN construction, the init_value can be anything at all because it is ignored since the v param in your LAMBDA is never used.

The TEXTJOIN vs ARRAYTOTEXT points are good to note, but I skip needing either one by thunking the arrays and using MAKEARRAY and without needing ATEXTSPLIT.

Personally, I'd rather do snake-sequence calc using MAP/thunks/MAKEARRAY as below than BYROW/BYCOL.

LAMBDA_SCANBY.xlsxABCDEFGHIJKLMNOPQR1123451|2|3|4|5=SCAN(
    "",
    SEQUENCE(6),
    LAMBDA(v,i,
        LET(
            j,IF(MOD(i,2),1,-1),
            TEXTJOIN("|",,SORT(INDEX(A1#,i,),,j,1))
        ))
)267891010|9|8|7|63111213141511|12|13|14|154161718192020|19|18|17|165212223242521|22|23|24|256262728293030|29|28|27|2678910111|2|3|4|5=BYROW(
    SEQUENCE(6),
    LAMBDA(i,
        LET(
            j,IF(MOD(i,2),1,-1),
            TEXTJOIN("|",,SORT(INDEX(A1#,i,SEQUENCE(,5)),,j,1))
        ))
)1210|9|8|7|61311|12|13|14|151420|19|18|17|161521|22|23|24|251630|29|28|27|261718192012345=LET(
    array,TRANSPOSE(A1#),rows_,ROWS(array), cols_,COLUMNS(array),
    seq,SEQUENCE(,cols_),sort_params,IF(ISEVEN(seq),-1,1),
    thunks,
        MAP(seq,sort_params,
            LAMBDA(idx,order,
                LAMBDA(SORT(INDEX(array,,idx),,order))
            )),
    expand,
        MAKEARRAY(rows_,cols_,
            LAMBDA(i,j,
                INDEX(INDEX(thunks,1,j)(),i,1)
            )),
    TRANSPOSE(expand)
)21109876221112131415232019181716242122232425253029282726262728293031323334Sheet2Cell FormulasRangeFormulaA1:E6A1=SEQUENCE(6,5)G1:G6G1=SCAN(
    "",
    SEQUENCE(6),
    LAMBDA(v,i,
        LET(
            j,IF(MOD(i,2),1,-1),
            TEXTJOIN("|",,SORT(INDEX(A1#,i,),,j,1))
        ))
)L1,L20,L11L1=FORMULATEXT(G1)G11:G16G11=BYROW(
    SEQUENCE(6),
    LAMBDA(i,
        LET(
            j,IF(MOD(i,2),1,-1),
            TEXTJOIN("|",,SORT(INDEX(A1#,i,SEQUENCE(,5)),,j,1))
        ))
)G20:K25G20=LET(
    array,TRANSPOSE(A1#),rows_,ROWS(array), cols_,COLUMNS(array),
    seq,SEQUENCE(,cols_),sort_params,IF(ISEVEN(seq),-1,1),
    thunks,
        MAP(seq,sort_params,
            LAMBDA(idx,order,
                LAMBDA(SORT(INDEX(array,,idx),,order))
            )),
    expand,
        MAKEARRAY(rows_,cols_,
            LAMBDA(i,j,
                INDEX(INDEX(thunks,1,j)(),i,1)
            )),
    TRANSPOSE(expand)
)Dynamic array formulas.


----------



## Xlambda (Oct 10, 2021)

?if you noticed, my threads have, in general, more than one post. Sometimes they reveal more ways of solving same problem, sometimes they are based on a concept, they have a foundation and a structure that builds up in a flow of examples, from basic simple ones to more complex ones at the end. So, concept, foundation, flow, all consistent with each other. The concept here is: lambda "formula" (mandatory to be short), foundation is SCAN/REDUCE, flow....
The flow just started, and you came in with alternatives. Very nice! I am open to all alternatives, but here the "foundation" was SCAN as the thread name states (consistency again), pushing it to the limits. I will finish the flow other time, no problem. 
Anyhow, single lambda like SCAN versus 2 other lambdas whatever they are BYROW and MAKEARRAY or MAP and other…come on, never. I did combinatorics with all possible formulas and methods, when we deal with 10M cells a simple comma added makes the difference between 5s and eternity.
Regarding ATEXTSPLIT; Why we use any function? Why we use UNIQUE? We can still do it old school. The answer is quite simple. We use them because we have them, and they do their job. As simple as that. 
To conclude on a funny note. You know, obviously, what cab sharing does, more popular in US. I was in a cab heading to SCAN, you jump in asking me if we can detour to BYROW , normally is no problem, if I am not in a hurry.?✌


----------



## Xlambda (Oct 10, 2021)

Snake sequence RxC . An example of what fits my personal "concept" of a lambda "formula" in general. 
=MAKEARRAY(R,C,LAMBDA(r,c,C*(r-1)+c*ISODD(r)+(C-c+1)*ISEVEN(r)))
LAMBDA 1.1.4.xlsxABCDEFGHIJK1rows72clms834=MAKEARRAY(B1,B2,LAMBDA(r,c,B2*(r-1)+c*ISODD(r)+(B2-c+1)*ISEVEN(r)))512345678616151413121110971718192021222324832313029282726259333435363738394010484746454443424111495051525354555612Sheet11Cell FormulasRangeFormulaA4A4=FORMULATEXT(A5)A5:H11A5=MAKEARRAY(B1,B2,LAMBDA(r,c,B2*(r-1)+c*ISODD(r)+(B2-c+1)*ISEVEN(r)))Dynamic array formulas.


----------



## tboulden (Oct 10, 2021)

Xlambda said:


> To conclude on a funny note. You know, obviously, what cab sharing does, more popular in US. I was in a cab heading to SCAN, you jump in asking me if we can detour to BYROW , normally is no problem, if I am not in a hurry.?✌





Xlambda said:


> This is my take of tboulden's SCANBYROW/BYCOL.


Not sure the car-sharing metaphor holds up since I inspired the thread


----------



## Xlambda (Oct 10, 2021)

?You can own the cab company, still is cab sharing?


----------



## Xlambda (Oct 17, 2021)

Back on track with more examples:
LAMBDA 1.1.4.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAI1Task: "scan" whole array by each product23sample array 6 rows, 3, products unique prod."scan" whole array, not what we want"scan" by row, not what we want4prod.=UNIQUE(A5:A10)=SCAN(0,B5:F10,LAMBDA(v,a,v+a))or=ASCAN(B5:F10)=ASCAN(B5:F10,-1)5A86863A8142228318142228318142228316B79875B384755626738475562677162431367A49923C718089919471808991944132224278C141519599100105106959910010510615611129B632781121151171241321121151171241326911182610C568811371431511591601371431511591605111927281112scan only for prod=Ascan only prod=Bscan only prod=Csum all scans13=J14#+P14#+V14#148142228310000000000814222831150000071624313600000716243136163544535558000000000035445355581700000000001561112156111218000004245475462000004245475462190000000000172331394017233139402021K14:=SCAN(0,(B5:F10)*(A5:A10=INDEX(H5#,1)),LAMBDA(v,a,v+a))*((A5:A10=INDEX(H5#,1)))22Q14:=SCAN(0,(B5:F10)*(A5:A10=INDEX(H5#,2)),LAMBDA(v,a,v+a))*((A5:A10=INDEX(H5#,2)))23W14:=SCAN(0,(B5:F10)*(A5:A10=INDEX(H5#,3)),LAMBDA(v,a,v+a))*((A5:A10=INDEX(H5#,3)))2425single cell26=REDUCE(0,SEQUENCE(3),LAMBDA(v,i,v+SCAN(0,(B5:F10)*(A5:A10=INDEX(H5#,i)),LAMBDA(v,a,v+a))*((A5:A10=INDEX(H5#,i)))))278142228312871624313629354453555830156111231424547546232172331394033ASCAN post 10Cell FormulasRangeFormulaH4,L4,J26,AB13,AA4,U4H4=FORMULATEXT(H5)H5:H7H5=UNIQUE(A5:A10)L5:P10L5=SCAN(0,B5:F10,LAMBDA(v,a,v+a))U5:Y10U5=ASCAN(B5:F10)AA5:AE10AA5=ASCAN(B5:F10,-1)J14:N19J14=SCAN(0,(B5:F10)*(A5:A10=INDEX(H5#,1)),LAMBDA(v,a,v+a))*((A5:A10=INDEX(H5#,1)))P14:T19P14=SCAN(0,(B5:F10)*(A5:A10=INDEX(H5#,2)),LAMBDA(v,a,v+a))*((A5:A10=INDEX(H5#,2)))V14:Z19V14=SCAN(0,(B5:F10)*(A5:A10=INDEX(H5#,3)),LAMBDA(v,a,v+a))*((A5:A10=INDEX(H5#,3)))AB14:AF19AB14=J14#+P14#+V14#J21J21=FORMULATEXT(J14)J22J22=FORMULATEXT(P14)J23J23=FORMULATEXT(V14)J27:N32J27=REDUCE(0,SEQUENCE(3),LAMBDA(v,i,v+SCAN(0,(B5:F10)*(A5:A10=INDEX(H5#,i)),LAMBDA(v,a,v+a))*((A5:A10=INDEX(H5#,i)))))Dynamic array formulas.


----------



## Xlambda (Sep 30, 2021)

*ASCAN**, * *A*rray *SCAN*, *3 in 1 function*, *SCAN* *by row*, *by column*, *by array*. This is my take of tboulden's SCANBYROW/BYCOL.
Uses *only* *new!! SCAN* lambda helper function, no need of byrow,bycol,makearray, or lambda as arguments.

```
=LAMBDA(a,[d],
    LET(y,IF(d=1,TRANSPOSE(a),a),s,SCAN(0,y,LAMBDA(v,a,v+a)),r,ROWS(s),c,COLUMNS(s),sr,SEQUENCE(r)-1,
       x,s-IF(d,IFERROR(INDEX(INDEX(s,,c),sr)*sr^0,0),0),
       IF(d=1,TRANSPOSE(x),x)
   )
)
```
LAMBDA 1.1.3.xlsxABCDEFGHIJKLMNOPQRST1d,omitted (by array)d,-1 (by rows)d,1 (by clms)2sample 1a=ASCAN(A3:D6)=ASCAN(A3:D6,-1)=ASCAN(A3:D6,1)312341361013610123445678152128365111826681012591011124555667891930421518212461314151691105120136132742582832364078d,omitted (by array)d,-1 (by rows)d,1 (by clms)9sample 2a=ASCAN(A10#)=ASCAN(A10#,-1)=ASCAN(A10#,1)1012341361013610123411567815212836511182668101212910111245556678919304215182124131314151691105120136132742582832364014171819201531711902101735547445505560152122232423125327630021436690667278841617Note: Did not set an extra argument for any kind of "initial value", since the only thing it does is too simple,18adds a constant value to the final outcome , like in 10+ASCAN(a)19ASCAN postCell FormulasRangeFormulaF2,P9,K9,F9,P2,K2F2=FORMULATEXT(F3)F3:I6F3=ASCAN(A3:D6)K3:N6K3=ASCAN(A3:D6,-1)P3:S6P3=ASCAN(A3:D6,1)A10:D15A10=SEQUENCE(6,4)F10:I15F10=ASCAN(A10#)K10:N15K10=ASCAN(A10#,-1)P10:S15P10=ASCAN(A10#,1)Dynamic array formulas.


----------



## Xlambda (Oct 17, 2021)

LAMBDA 1.1.4.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC1Task: scan largest n values of each row of the sample array2extract largest 4 values=INDEX(AFUSBYROW(A5:G14,,,-1),SEQUENCE(10),SEQUENCE(,C3))3largest n=4row by row↓alternative functionresult to get:4sample 10 rows=LARGE(A5:G5,SEQUENCE(,$C$3))↓single cell=SCAN(0,I5:L14,LAMBDA(v,a,v+a))5819910689191099191099192938476771618417418171671817167658298105737825101818108718108712313314114881014711152012015141120151411168183197208929112811121212119121211922023224325210521121113202013111120131111272285296307111811416711161411816141183233373483561210910212571210109121010936837838839713619119519141919141119191411416435449460141120111422015202015142020151448050051552915161st way, 2 lambda helper, SCAN(MAKEARRAY)17=SCAN(0,MAKEARRAY(10,C3,LAMBDA(r,c,LET(x,INDEX(A5:G14,r,),LARGE(x,c)))),LAMBDA(v,a,v+a))181929384719other function65829810520AFUSBYROW1231331411482116818319720822220232243252232722852963072432333734835625368378388397264164354494602748050051552928292nd way, 1 lambda helper, SCAN30=SCAN(0,SEQUENCE(10,C3),LAMBDA(v,i,LET(x,INDEX(A5:G14,QUOTIENT(i-1,C3)+1,),v+LARGE(x,MOD(i-1,C3)+1))))311929384732658298105331231331411483416818319720835220232243252362722852963073732333734835638368378388397394164354494604048050051552941ASCAN post 11Cell FormulasRangeFormulaP2P2=FORMULATEXT(P5)I4,I30,I17,V4I4=FORMULATEXT(I5)P5:S14P5=INDEX(AFUSBYROW(A5:G14,,,-1),SEQUENCE(10),SEQUENCE(,C3))V5:Y14V5=SCAN(0,I5:L14,LAMBDA(v,a,v+a))I5:L14I5=LARGE(A5:G5,SEQUENCE(,$C$3))I18:L27I18=SCAN(0,MAKEARRAY(10,C3,LAMBDA(r,c,LET(x,INDEX(A5:G14,r,),LARGE(x,c)))),LAMBDA(v,a,v+a))I31:L40I31=SCAN(0,SEQUENCE(10,C3),LAMBDA(v,i,LET(x,INDEX(A5:G14,QUOTIENT(i-1,C3)+1,),v+LARGE(x,MOD(i-1,C3)+1))))Dynamic array formulas.


----------



## Xlambda (Oct 17, 2021)

LAMBDA 1.1.4.xlsxABCDEFGHIJKLMNOPQRSTUVW1largest n=4Task: scan whole array by each product's largest n values 2sample 10 rows3productsresults to get step by step34unique prod.5=TRANSPOSE(UNIQUE(A8:A17))filter prod A6ABC=--(A8:A17=INDEX(A6#,1))1A7prod.↓=K8*LARGE(B8:H8,SEQUENCE(,$D$1))=K8#*ASCAN(M8:P17)8A8199106891191099192938479B771618417400000000010A37825101811810876575839011C10147111520100000000012C291128111200000000013A5211211132012013111111012313414514B181141671100000000015A10910212571121010915716717718616C6191195191400000000017B11201114220150000000001819filter prod B20result we should obtain=--(A8:A17=INDEX(A6#,2))2B211A+2B+3C↓=K22*LARGE(B8:H8,SEQUENCE(,$D$1))=K22#*ASCAN(M22:P31)22=R8#+R22#+R36#000000000231929384711817167183551582418355158000000000256575839000000000026203549600000000002772849510400000000028110123134145116141187488991072974889910700000000030157167177186000000000311231421561671202015141271471621763212714716217633filter prod C34=--(A8:A17=INDEX(A6#,3))3C35↓=K36*LARGE(B8:H8,SEQUENCE(,$D$1))=K36#*ASCAN(M36:P45)36000000000370000000003800000000039120151411203549604011212119728495104410000000004200000000043000000000441191914111231421561674500000000046ASCAN post 12Cell FormulasRangeFormulaA5,M35,R35,B22,M21,R21,M7,R7A5=FORMULATEXT(A6)A6:C6A6=TRANSPOSE(UNIQUE(A8:A17))K6,K34,K20K6=FORMULATEXT(K8)K8:K17K8=--(A8:A17=INDEX(A6#,1))R8:U17,R36:U45,R22:U31R8=K8#*ASCAN(M8:P17)M8:P17M8=K8*LARGE(B8:H8,SEQUENCE(,$D$1))K22:K31K22=--(A8:A17=INDEX(A6#,2))B23:E32B23=R8#+R22#+R36#M22:P31M22=K22*LARGE(B8:H8,SEQUENCE(,$D$1))K36:K45K36=--(A8:A17=INDEX(A6#,3))M36:P45M36=K36*LARGE(B8:H8,SEQUENCE(,$D$1))Dynamic array formulas.


----------



## Xlambda (Oct 17, 2021)

LAMBDA 1.1.4.xlsxABCDEFGHIJKLMNOPQRSTUV1Task: scan whole array by each product's largest n values 2single cell formulas 2 ways34largest n=45sample 10 rows3productsunique prod.6prod.=UNIQUE(A7:A16)7A819910689A8B7716184174B9A378251018C10C10147111520111C291128111212A5211211132013B181141671114A109102125715C6191195191416B112011142201517181st way, 3 lambda helper, REDUCE(SCAN(MAKEARRAY))19 =REDUCE(0,SEQUENCE(D3),LAMBDA(v,j,v+SCAN(0,
    MAKEARRAY(10,C2,LAMBDA(r,c,LET(x,INDEX(B5:H14,r,),LARGE(x,c))))*(--(A5:A14=INDEX(J5#,j))),
      LAMBDA(v,a,v+a))*(--(A5:A14=INDEX(J5#,j)))))201929384721183551582265758390232035496024728495104251101231341452674889910727157167177186281231421561672912714716217630312nd way, 2 lambda helper, REDUCE(SCAN)32 =REDUCE(0,SEQUENCE(D3),LAMBDA(v,j,v+SCAN(0,SEQUENCE(10,C2),
    LAMBDA(v,i,LET(q,QUOTIENT(i-1,C2)+1,x,INDEX(B5:H14,q,)*INDEX(--(A5:A14=INDEX(J5#,j)),q),
     v+LARGE(x,MOD(i-1,C2)+1))))*(--(A5:A14=INDEX(J5#,j)))))331929384734183551583565758390362035496037728495104381101231341453974889910740157167177186411231421561674212714716217643ASCAN post 13Cell FormulasRangeFormulaJ6J6=FORMULATEXT(J7)J7:J9J7=UNIQUE(A7:A16)B20:E29B20=REDUCE(0,SEQUENCE(D5),LAMBDA(v,j,v+SCAN(0,MAKEARRAY(10,D4,LAMBDA(r,c,LET(x,INDEX(B7:H16,r,),LARGE(x,c))))*(--(A7:A16=INDEX(J7#,j))),LAMBDA(v,a,v+a))*(--(A7:A16=INDEX(J7#,j)))))B33:E42B33=REDUCE(0,SEQUENCE(D5),LAMBDA(v,j,v+SCAN(0,SEQUENCE(10,D4),LAMBDA(v,i,LET(q,QUOTIENT(i-1,D4)+1,x,INDEX(B7:H16,q,)*INDEX(--(A7:A16=INDEX(J7#,j)),q),v+LARGE(x,MOD(i-1,D4)+1))))*(--(A7:A16=INDEX(J7#,j)))))Dynamic array formulas.


----------



## Xlambda (Oct 17, 2021)

This is how a lambda function will look (practice purpose), let's name it LARGESCAN(a,p,l) where a, array, p, product clm, l, l largest values

```
=LAMBDA(a,p,l,LET(u,UNIQUE(p),w,ROWS(u),
   REDUCE(0,SEQUENCE(w),LAMBDA(v,j,LET(x,(--(p=INDEX(u,j))),v+
     SCAN(0,MAKEARRAY(10,l,LAMBDA(r,c,LET(x,INDEX(a,r,),LARGE(x,c))))*x,
      LAMBDA(v,a,v+a))*x)))))
```
LAMBDA 1.1.4.xlsxABCDEFGHIJK1Task: scan whole array by each product's largest n values 2single cell (lambda function)34sample5prod.6A8199106897B77161841748A3782510189C10147111520110C291128111211A5211211132012B181141671113A109102125714C6191195191415B11201114220151617a,B6:H15,p,A6:A15,l,418=LARGESCAN(B6:H15,A6:A15,4)191929384720183551582165758390222035496023728495104241101231341452574889910726157167177186271231421561672812714716217629ASCAN post 13.1Cell FormulasRangeFormulaB18B18=FORMULATEXT(B19)B19:E28B19=LARGESCAN(B6:H15,A6:A15,4)Dynamic array formulas.


----------



## Xlambda (Oct 18, 2021)

Examples with REDUCE as main lambda helper function.
LAMBDA 1.1.4.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAG1Task: Filter only the rows of an array that hold unique values, alternative functions2dups1.1.  AFUSBYROW (f , ignored,u,1,s,omitted)1.2. AHCLEAN(AFUSBYROW)3sample=AFUSBYROW(A4:J23,,1)=AHCLEAN(L4#)4252251920203492825219203492841110328714131620561223132919742986122313291974819114282682912201061827182528262526221827252826221926302782212231271918151925194119231918152541231873825209414268411103287141316204111032871413162091516183211517188121516183211781210232313227171925223132271719252. recursive method seen in ARRANGEMENTS thread1112462771917272641246277191726=T_P(A4:J23)1274178823616282874178236162841110328714131620132121268216311392126821631191911428268291220101455232012381315232012813119263027822122312151911428268291220101911428268291220101873825209414261629201712316121228182920171231628181751429528162724305514292816272430188307253091920195830725919205other functions191926302782212231219263027822122312AFUSBYROW202913539231027921291353923102721AHCLEAN21187382520941426187382520941426T_P222020292743141021222029274314102122231682125211515191715168212515191724ASCAN post 14Cell FormulasRangeFormulaL3,W11,W3L3=FORMULATEXT(L4)L4:U23L4=AFUSBYROW(A4:J23,,1)W4:AF7W4=AHCLEAN(L4#)W12:AF15W12=T_P(A4:J23)Dynamic array formulas.


----------



## Xlambda (Oct 18, 2021)

LAMBDA 1.1.4.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAI1Task: Filter only the rows of an array that holds unique values, REDUCE23step 1   clm1=a + clm2=a +….+ clm10=a4duplicatesrows unique values =REDUCE(0,SEQUENCE(D5),LAMBDA(v,i,v+(INDEX(A8:J27,,i)=A8:J27)))5columns10⬇6⬇step 2 sum BYROW(a')7samplea⬇a'=BYROW(L8#,LAMBDA(a,SUM(a)))82522519202034928212122111114a''96122313291974298111121112112step 3 FILTER(a,a''=clms(a))10182718252826252622212211222116=FILTER(A8:J27,W8#=D5)11191815192519411923411414114122411103287141316201241110328714131620111111111110191142826829122010131516183211517188122121121211141926302782212231214232313227171925222111112121418738252094142615124627719172726412121112121416741788236162828111221112214172121268216311392211112121141855232012381312212112111141919114282682912201011111111111020292017123161212281811131133111621514295281627243053113111113162283072530919201951211212121142319263027822122312111111111110single cell242913539231027921111121112112411103287141316202518738252094142611111111111019114282682912201026202029274314102122221111111112192630278221223122716821252115151917151121233113181873825209414262829Y24:=FILTER(A8:J27,BYROW(REDUCE(0,SEQUENCE(D5),LAMBDA(v,i,v+(INDEX(A8:J27,,i)=A8:J27))),LAMBDA(x,SUM(x)=D5)))30ASCAN post 15Cell FormulasRangeFormulaL4L4=FORMULATEXT(L8)W7,Y10W7=FORMULATEXT(W8)L8:U27L8=REDUCE(0,SEQUENCE(D5),LAMBDA(v,i,v+(INDEX(A8:J27,,i)=A8:J27)))W8:W27W8=BYROW(L8#,LAMBDA(a,SUM(a)))Y11:AH14Y11=FILTER(A8:J27,W8#=D5)Y24:AH27Y24=FILTER(A8:J27,BYROW(REDUCE(0,SEQUENCE(D5),LAMBDA(v,i,v+(INDEX(A8:J27,,i)=A8:J27))),LAMBDA(x,SUM(x)=D5)))I29I29=FORMULATEXT(Y24)Dynamic array formulas.


----------



## Xlambda (Oct 18, 2021)

LAMBDA 1.1.4.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAA1Task: filter only rows of an array that are in ascending order, alternative functions23asc.order1.1. all rows in asc. order1.2. compare a=a'1.3. whole rows comparing4samplea=AFUSBYROW(A5:E12,,,1)=A5:E12=H5#=BYROW(N5#,LAMBDA(a,AND(a)))5abcdea'abcdeTRUETRUETRUETRUETRUETRUE6aebdeabdeeTRUEFALSEFALSEFALSETRUEFALSE1.4. step 4,filtering71234512345TRUETRUETRUETRUETRUETRUE=FILTER(A5:E12,T5#)81304501345FALSEFALSEFALSETRUETRUEFALSEabcde9aabcdaabcdTRUETRUETRUETRUETRUETRUE12345101223412234TRUETRUETRUETRUETRUETRUEaabcd11ab123123abFALSEFALSEFALSEFALSEFALSEFALSE1223412123ab123abTRUETRUETRUETRUETRUETRUE123ab1314single cell15=FILTER(A5:E12,BYROW(A5:E12=AFUSBYROW(A5:E12,,,1),LAMBDA(a,AND(a))))16other functionabcde17AFUSBYROW1234518T_CAaabcd191223420123ab21222. recursive method seen in ARRANGEMENTS thread23=T_CA(A5:E12)24abcde251234526aabcd271223428123ab29ASCAN post 16Cell FormulasRangeFormulaH4,N4,H23,H15,V7,T4H4=FORMULATEXT(H5)H5:L12H5=AFUSBYROW(A5:E12,,,1)N5:R12N5=A5:E12=H5#T5:T12T5=BYROW(N5#,LAMBDA(a,AND(a)))V8:Z12V8=FILTER(A5:E12,T5#)H16:L20H16=FILTER(A5:E12,BYROW(A5:E12=AFUSBYROW(A5:E12,,,1),LAMBDA(a,AND(a))))H24:L28H24=T_CA(A5:E12)Dynamic array formulas.


----------



## Xlambda (Oct 18, 2021)

LAMBDA 1.1.4.xlsxABCDEFGHIJKLMNOPQR1Task: filter only rows of an array that are in ascending order, REDUCE25columnsstep 1 <=> for i=1 to clms(a)-1, 1*(clm i<=clm i+1)3asc.order=REDUCE(1,SEQUENCE(A2-1),LAMBDA(v,i,v*(INDEX(A5:E12,,i)<=INDEX(A5:E12,,i+1))))4sampleaa'5abcde16aebde0step 2, FILTER(a,a')7123451=FILTER(A5:E12,G5#)8130450abcde9aabcd11234510122341aabcd11ab12301223412123ab1123ab131415step 3, single cell16=FILTER(A5:E12,REDUCE(1,SEQUENCE(A2-1),LAMBDA(v,i,v*(INDEX(A5:E12,,i)<=INDEX(A5:E12,,i+1)))))17abcde181234519aabcd201223421123ab22ASCAN post 17Cell FormulasRangeFormulaG3G3=FORMULATEXT(G5)G5:G12G5=REDUCE(1,SEQUENCE(A2-1),LAMBDA(v,i,v*(INDEX(A5:E12,,i)<=INDEX(A5:E12,,i+1))))I7,C16I7=FORMULATEXT(I8)I8:M12I8=FILTER(A5:E12,G5#)C17:G21C17=FILTER(A5:E12,REDUCE(1,SEQUENCE(A2-1),LAMBDA(v,i,v*(INDEX(A5:E12,,i)<=INDEX(A5:E12,,i+1)))))Dynamic array formulas.


----------



## Xlambda (Nov 4, 2021)

Another cool example using SCAN versatility that deserves to be added to our collection, this time, inspired by latest video of Leila Gharani (4-Nov-21):
How to Use Excel's New SCAN Function (and attempting to calculate YTD values with it)
The idea is to SCAN the sales by calendar year (find ways to reset the "acumulator" by a certain condition)
Note: I have changed the sales values to be easier to check
leila SCAN.xlsxABCDEFGHIJKLMNOPQRST1Leila's solution: ( notice the cool trick of 0*a+b, that resets the "acumulator" "a"))2=SCAN(0,Sales[Sales],LAMBDA(a,b,IF(MONTH(OFFSET(b,,-1))<>1,a+b,0*a+b)))3table sample : Sales↓↓↓my solution (longer, unfortunatily, but avoids offset, hope that still can be considered a lambda "formula" ?)4MonthSales↓↓↓=SCAN(0,SEQUENCE(ROWS(Sales)),LAMBDA(v,i,(MONTH(INDEX(Sales[Month],i))<>1)*v+INDEX(Sales[Sales],i)))5Jan-191116Feb-19233to reset the "acumulator" on July , we change only <>1 to <>7 on both formulas7Mar-19366=SCAN(0,Sales[Sales],LAMBDA(a,b,IF(MONTH(OFFSET(b,,-1))<>7,a+b,0*a+b)))8Apr-1941010↓↓↓9May-1951515↓↓↓=SCAN(0,SEQUENCE(ROWS(Sales)),LAMBDA(v,i,(MONTH(INDEX(Sales[Month],i))<>7)*v+INDEX(Sales[Sales],i)))10Jun-19621211111Jul-19728283312Aug-19836366613Sep-1994545101014Oct-19105555151515Nov-19116666212116Dec-191278787717Jan-20111151518Feb-20233242419Mar-20366343420Apr-2041010454521May-2051515575722Jun-2062121585823Jul-2072828606024Aug-2083636636325Sep-2094545676726Oct-20105555727227Nov-20116666787828Dec-201278787729Jan-21111151530Feb-21233242431Mar-21366343432Apr-2141010454533May-2151515575734Jun-2162121585835Jul-2172828606036Aug-2183636636337Sep-2194545676738Oct-21105555727239Nov-21116666787840Dec-211278787741Jan-22111151542Feb-22233242443Mar-22366343444Apr-2241010454545May-2251515575746Jun-2262121585847Jul-2272828606048Aug-2283636636349Sep-2294545676750Oct-22105555727251Nov-22116666787852Dec-221278787753151554242455343456454557575758Sheet2Cell FormulasRangeFormulaD2,H7D2=FORMULATEXT(D5)F4,J9F4=FORMULATEXT(F5)D5:D52D5=SCAN(0,Sales[Sales],LAMBDA(a,b,IF(MONTH(OFFSET(b,,-1))<>1,a+b,0*a+b)))F5:F52F5=SCAN(0,SEQUENCE(ROWS(Sales)),LAMBDA(v,i,(MONTH(INDEX(Sales[Month],i))<>1)*v+INDEX(Sales[Sales],i)))H10:H57H10=SCAN(0,Sales[Sales],LAMBDA(a,b,IF(MONTH(OFFSET(b,,-1))<>7,a+b,0*a+b)))J10:J57J10=SCAN(0,SEQUENCE(ROWS(Sales)),LAMBDA(v,i,(MONTH(INDEX(Sales[Month],i))<>7)*v+INDEX(Sales[Sales],i)))Dynamic array formulas.


----------



## Xlambda (Nov 4, 2021)

leila SCAN.xlsxABCDEFGHIJKLMNOPQR1Bonus trick: If we want to SCAN by months and not years, a REDUCE(SCAN)) lambda "formula" (every month acumulates values of previous records of same month)2There are more approaches, chose this one because is with SCAN embeded in REDUCE3MonthSales=REDUCE(0,SEQUENCE(12),LAMBDA(v,i,LET(x,IF(MONTH(Sales[Month])=i,1),v+IF(x,SCAN(0,IF(x,Sales[Sales]),LAMBDA(a,b,a+b))))))4Jan-19115Feb-19226Mar-19337Apr-19448May-19559Jun-196610Jul-197711Aug-198812Sep-199913Oct-19101014Nov-19111115Dec-19121216Jan-201217Feb-202418Mar-203619Apr-204820May-2051021Jun-2061222Jul-2071423Aug-2081624Sep-2091825Oct-20102026Nov-20112227Dec-20122428Jan-211329Feb-212630Mar-213931Apr-2141232May-2151533Jun-2161834Jul-2172135Aug-2182436Sep-2192737Oct-21103038Nov-21113339Dec-21123640Jan-221441Feb-222842Mar-2231243Apr-2241644May-2252045Jun-2262446Jul-2272847Aug-2283248Sep-2293649Oct-22104050Nov-22114451Dec-22124852Sheet3Cell FormulasRangeFormulaD3D3=FORMULATEXT(D4)D4:D51D4=REDUCE(0,SEQUENCE(12),LAMBDA(v,i,LET(x,IF(MONTH(Sales[Month])=i,1),v+IF(x,SCAN(0,IF(x,Sales[Sales]),LAMBDA(a,b,a+b))))))Dynamic array formulas.


----------



## Xlambda (Sep 30, 2021)

*ASCAN**, * *A*rray *SCAN*, *3 in 1 function*, *SCAN* *by row*, *by column*, *by array*. This is my take of tboulden's SCANBYROW/BYCOL.
Uses *only* *new!! SCAN* lambda helper function, no need of byrow,bycol,makearray, or lambda as arguments.

```
=LAMBDA(a,[d],
    LET(y,IF(d=1,TRANSPOSE(a),a),s,SCAN(0,y,LAMBDA(v,a,v+a)),r,ROWS(s),c,COLUMNS(s),sr,SEQUENCE(r)-1,
       x,s-IF(d,IFERROR(INDEX(INDEX(s,,c),sr)*sr^0,0),0),
       IF(d=1,TRANSPOSE(x),x)
   )
)
```
LAMBDA 1.1.3.xlsxABCDEFGHIJKLMNOPQRST1d,omitted (by array)d,-1 (by rows)d,1 (by clms)2sample 1a=ASCAN(A3:D6)=ASCAN(A3:D6,-1)=ASCAN(A3:D6,1)312341361013610123445678152128365111826681012591011124555667891930421518212461314151691105120136132742582832364078d,omitted (by array)d,-1 (by rows)d,1 (by clms)9sample 2a=ASCAN(A10#)=ASCAN(A10#,-1)=ASCAN(A10#,1)1012341361013610123411567815212836511182668101212910111245556678919304215182124131314151691105120136132742582832364014171819201531711902101735547445505560152122232423125327630021436690667278841617Note: Did not set an extra argument for any kind of "initial value", since the only thing it does is too simple,18adds a constant value to the final outcome , like in 10+ASCAN(a)19ASCAN postCell FormulasRangeFormulaF2,P9,K9,F9,P2,K2F2=FORMULATEXT(F3)F3:I6F3=ASCAN(A3:D6)K3:N6K3=ASCAN(A3:D6,-1)P3:S6P3=ASCAN(A3:D6,1)A10:D15A10=SEQUENCE(6,4)F10:I15F10=ASCAN(A10#)K10:N15K10=ASCAN(A10#,-1)P10:S15P10=ASCAN(A10#,1)Dynamic array formulas.


----------



## Xlambda (Nov 4, 2021)

leila SCAN.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAG1Existing alternative functions : We already have functions shorter than a formula bar row that can handle these tasks.23MonthSalesSCAN by monthsingle cell year SCAN4Jan-191=ARESIZE(Sales[Sales],,12)=ARESIZE(E11#,,1)=ARESIZE(ASCAN(ARESIZE(Sales[Sales],,12),1),,1)5Feb-1921.12345678910111213.16Mar-193123456789101112227Apr-194123456789101112338May-195123456789101112449Jun-1965510Jul-197=ASCAN(E5#,1)6611Aug-1982.1234567891011127712Sep-199246810121416182022248813Oct-19103691215182124273033369914Nov-19114812162024283236404448101015Dec-1912111116Jan-201SCAN by year121217Feb-2021.2.3.22single cell month SCAN18Mar-203=AUNSTACK(Sales[Sales],12)=ASCAN(E19#,1)=ASTACK(J19#,1)44=ASTACK(ASCAN(AUNSTACK(Sales[Sales],12),1),1)19Apr-20411111111166120May-20522223333388321Jun-2063333666661010622Jul-2074444101010101012121023Aug-2085555151515151514141524Sep-2096666212121212116162125Oct-20107777282828282818182826Nov-20118888363636363620203627Dec-20129999454545454522224528Jan-21110101010555555555524245529Feb-212111111116666666666336630Mar-213121212127878787878667831Apr-214199132May-21531212333Jun-216other functions61515634Jul-217ASTACK1018181035Aug-218AUNSTACK1521211536Sep-219ARESIZE2124242137Oct-21102827272838Nov-21113630303639Dec-21124533334540Jan-2215536365541Feb-22266446642Mar-22378887843Apr-22411212144May-22531616345Jun-22662020646Jul-2271024241047Aug-2281528281548Sep-2292132322149Oct-22102836362850Nov-22113640403651Dec-221245444445525548485553666654787855115633576658101059151560212161282862363663454564555565666666787867Sheet3Cell FormulasRangeFormulaE4,W18,J18,E10,U4E4=FORMULATEXT(E5)Q4,D18,N18Q4=FORMULATEXT(R5)E5:P8E5=ARESIZE(Sales[Sales],,12)R5:R52R5=ARESIZE(E11#,,1)U5:U52U5=ARESIZE(ASCAN(ARESIZE(Sales[Sales],,12),1),,1)E11:P14E11=ASCAN(E5#,1)E19:H30E19=AUNSTACK(Sales[Sales],12)J19:M30J19=ASCAN(E19#,1)O19:O66O19=ASTACK(J19#,1)W19:W66W19=ASTACK(ASCAN(AUNSTACK(Sales[Sales],12),1),1)Dynamic array formulas.


----------



## Xlambda (Nov 8, 2021)

Getting closer to the last examples of SCAN/REDUCE, lambda "formulas" that deserve to be shared.
Did this first time ages ago, with recursion. AAGGREGATE and other functions uses same concept.
The concept is quite simple, if we want to apply a function to an array (calculations, sorting, filtering, extractions...etc), row by row, a function that spills (horizontally), we apply it row by row, recursively, and we append the results, row by row, for all the rows of initial array, one by one.
At that time as an appending "accumulator" I have used APPEND2V (also recursive). There is even a newer version  APP2V, non-recursive, but both can deal with 2D arrays and have more features that are not needed in this case.
We need a simple function that appends vertically an array "a" to a single row "b", will call it *A1V(a,b)*. 
Now, having this new "accumulator", let's see if we can replace the recursion with *REDUCE*.
*A1V(a,b)*

```
=LAMBDA(a,b,LET(r,ROWS(a)+1,s,SEQUENCE(r),IF(s=r,b,a)))
```
LAMBDA 1.2.1.xlsxABCDEFGHIJKLMNOPQRSTUV11. A1V(a,b)2rws a+rws b=2+1=33a=IF(SEQUENCE(3)=3,A7:B7,A4:B5)=A1V(A4:B5,A7:B7)4ababab5cdcdcd6befef7ef892. lambda formula to replace recursion with REDUCE10If y(a) is the function to apply to every row11=reduce("",seq(rws(a)),lambda(v,i,A1V(v,y(index(a,i,))))) 12-initial value v="",i: iteration nr., accumulator: A1V(v,y(index(a,i,))1314lamda "formula" to sort in ascending  order each row ( sort(a,,,1) 1 argument for sorting by clm)15a=REDUCE("",SEQUENCE(3),LAMBDA(v,i,A1V(v,SORT(INDEX(A16:C18,i,),,,1))))16acb =A1V("",A7:B7)17123abcthe first row  is blank because initial value is "" and first iteration is A1V=("",sort(first row of a)), like in  18321123ef191232021to amend the formula to "cut" first row22=LET(a,A16:C18,r,ROWS(a),s,SEQUENCE(r),x,REDUCE("",s,LAMBDA(v,i,A1V(v,SORT(INDEX(a,i,),,,1)))),INDEX(x,s+1,SEQUENCE(,COLUMNS(x))))23abc24123251232627sorting in descending order each row28=LET(a,A16:C18,r,ROWS(a),s,SEQUENCE(r),x,REDUCE("",s,LAMBDA(v,i,A1V(v,SORT(INDEX(a,i,),,-1,1)))),INDEX(x,s+1,SEQUENCE(,COLUMNS(x))))29cba3032131321Instead of SORT we can write any function y=y(a)=LAMBDA(a,FUNCTION(a)) ( has to spill horizontally)32ASCAN np1Cell FormulasRangeFormulaD3,J3,E28,E22,S16,E15D3=FORMULATEXT(D4)D4:E6D4=IF(SEQUENCE(3)=3,A7:B7,A4:B5)J4:K6J4=A1V(A4:B5,A7:B7)E16:G19E16=REDUCE("",SEQUENCE(3),LAMBDA(v,i,A1V(v,SORT(INDEX(A16:C18,i,),,,1))))S17:T18S17=A1V("",A7:B7)E23:G25E23=LET(a,A16:C18,r,ROWS(a),s,SEQUENCE(r),x,REDUCE("",s,LAMBDA(v,i,A1V(v,SORT(INDEX(a,i,),,,1)))),INDEX(x,s+1,SEQUENCE(,COLUMNS(x))))E29:G31E29=LET(a,A16:C18,r,ROWS(a),s,SEQUENCE(r),x,REDUCE("",s,LAMBDA(v,i,A1V(v,SORT(INDEX(a,i,),,-1,1)))),INDEX(x,s+1,SEQUENCE(,COLUMNS(x))))Dynamic array formulas.


----------



## Xlambda (Nov 8, 2021)

Following the concept of previous post, here is how a function looks like, Introducing *AROW(a,y)* function.
*a: array
y: lambda "container" of y(a) LAMBDA(a,y(a)) *
Note 1: This is a preliminary design, y(a), only one argument (variable).
We can use functions with more variables y(a,k,...)
Note 2: Not a huge fan of "lambda" arguments. When we use it for data that lives in a range, firsthand calculations, is ok. I like to design functions that can live in other functions, interchanging variables, for intuitive useful repetitive tasks. If we master well these lambda helper functions, for firsthand calculations, a simple lambda "formula" can do the job in most of the cases. If arguments lengths exceed the length of some average custom functions, we will start to write functions to write arguments. ?
*AROW(a,y)*

```
=LAMBDA(a,y,LET(r,ROWS(a),
     x,REDUCE("",SEQUENCE(r),LAMBDA(v,i,A1V(v,y(INDEX(IF(a="","",a),i,))))),
     IFERROR(INDEX(x,SEQUENCE(r,,2),SEQUENCE(,COLUMNS(x))),"")
    )
)
```
LAMBDA 1.2.1.xlsxABCDEFGHIJKLMNOPQRSTU1asorting ascendingsorting descending22113y,LAMBDA(a,SORT(a,,,-1))y,LAMBDA(a,SORT(a,,,-1))33220=AROW(A2:D7,LAMBDA(a,SORT(a,,,-1)))=AROW(A2:D7,LAMBDA(a,SORT(a,,-1,-1)))41232112332115321002233220621341223322172221012332108123443219122222211011unique valuesunique values ascending order12y,LAMBDA(a,UNIQUE(a,1))y,LAMBDA(a,SORT(UNIQUE(a,1),,,-1))13=AROW(A2:D7,LAMBDA(a,UNIQUE(a,1)))=AROW(A2:D7,LAMBDA(a,SORT(UNIQUE(a,1),,,-1)))142131231532002316123123173210012318213412341921122021exclude blanks, align leftfilter text,excluding blanks22y,LAMBDA(a,FILTER(a,a<>""))y,LAMBDA(a,FILTER(a,ISTEXT(a)*(a<>"")))23a=AROW(A24:E26,LAMBDA(a,FILTER(a,a<>"")))=AROW(A24:E26,LAMBDA(a,FILTER(a,ISTEXT(a)*(a<>""))))24ab12ab12ab251e3cd1e3cdecd263cd43cd4cd2728filter numbers29y,LAMBDA(a,FILTER(a,ISNUMBER(a)))30=AROW(A24:E26,LAMBDA(a,FILTER(a,ISNUMBER(a))))31123213333434ASCAN np 2Cell FormulasRangeFormulaF3,F23F3=FORMULATEXT(G4)M3,G30,M23,M13M3=FORMULATEXT(M4)G4:J9G4=AROW(A2:D7,LAMBDA(a,SORT(a,,,-1)))M4:P9M4=AROW(A2:D7,LAMBDA(a,SORT(a,,-1,-1)))E13E13=FORMULATEXT(G14)G14:J19G14=AROW(A2:D7,LAMBDA(a,UNIQUE(a,1)))M14:P19M14=AROW(A2:D7,LAMBDA(a,SORT(UNIQUE(a,1),,,-1)))G24:K26G24=AROW(A24:E26,LAMBDA(a,FILTER(a,a<>"")))M24:O26M24=AROW(A24:E26,LAMBDA(a,FILTER(a,ISTEXT(a)*(a<>""))))G31:H33G31=AROW(A24:E26,LAMBDA(a,FILTER(a,ISNUMBER(a))))Dynamic array formulas.


----------



## Xlambda (Jan 13, 2022)

Another example using *REDUCE*, inspired from Leila's today's YT (13-Jan-22): Easily Combine Text & Numbers into ONE Cell in Excel | NO CONCATENATE.
*Task: Lambda "formula" to sum array formatted with TEXT*
Other functions on minisheet: T_CHARS , AKEEP
Book1ABCDEFGHIJKLMNO1NameAmount BaseBonusTask: Lambda "formula" to sum array formatted with TEXTother functions2Bill100050T_CHARS3Nick2500601st method, when we have a pattern (digits after ":")AKEEP4Sara3600100single cell for whole row(last posts functions)5Jessica10020=SUM(--REPLACE(B6:C6,1,SEARCH(":",B6:C6),""))6Base: 7,200Bonus: 230074307↑↑↑8↑↑=SUM(B6:C6)2nd method, works for any text format (REDUCE method)9↑="Bonus: "&TEXT(SUM(C2:C5),"#,##0")single cell for whole row10="Base: "&TEXT(SUM(B2:B5),"#,##0")=LET(a,B6:C6,REDUCE(0,a,LAMBDA(v,x,v+CONCAT(IFERROR(--MID(x,SEQUENCE(LEN(x)),1),"")))))1174301213=LET(a,B14:D14,REDUCE(0,a,LAMBDA(v,x,v+CONCAT(IFERROR(--MID(x,SEQUENCE(LEN(x)),1),"")))))14Base: $ 7,200Bonus# 1,230$Other/$2,740$/11170151617Base: $ 7,200Other/$2,740$/=LET(a,B17:C18,REDUCE(0,a,LAMBDA(v,x,v+CONCAT(IFERROR(--MID(x,SEQUENCE(LEN(x)),1),"")))))18Bonus# 1,230$$200$HGFgh1137019203rd method, dedicated functions, any text format21=SUM(AKEEP(B6:C6,T_CHARS(1)))22Big advantage of using dedicated function,=SUM(AKEEP(B17:C18,T_CHARS(1)))=SUM(AKEEP(B14:D14,T_CHARS(1)))23can handle decimal points with ease7430113701117024because of T_CHARS versatility25=AKEEP(B26:C27,T_CHARS(1,,"."))=SUM(AKEEP(B26:C27,T_CHARS(1,,".")))26Base: $ 7,200.01Other/$2,740.03$/7200.012740.0311370.127Bonus# 1,230.02$$200.04$HGFgh1230.02200.042829=SUM(F26#)3011370.131Sheet1Cell FormulasRangeFormulaF5,F29,F25,J25,H22,L22,F17,F13,F10F5=FORMULATEXT(F6)B6B6="Base: "&TEXT(SUM(B2:B5),"#,##0")C6C6="Bonus: "&TEXT(SUM(C2:C5),"#,##0")D6D6=SUM(B6:C6)F6F6=SUM(--REPLACE(B6:C6,1,SEARCH(":",B6:C6),""))D8D8=FORMULATEXT(D6)C9C9=FORMULATEXT(C6)B10B10=FORMULATEXT(B6)F11F11=LET(a,B6:C6,REDUCE(0,a,LAMBDA(v,x,v+CONCAT(IFERROR(--MID(x,SEQUENCE(LEN(x)),1),"")))))F14F14=LET(a,B14:D14,REDUCE(0,a,LAMBDA(v,x,v+CONCAT(IFERROR(--MID(x,SEQUENCE(LEN(x)),1),"")))))F18F18=LET(a,B17:C18,REDUCE(0,a,LAMBDA(v,x,v+CONCAT(IFERROR(--MID(x,SEQUENCE(LEN(x)),1),"")))))F21F21=FORMULATEXT(F23)F23F23=SUM(AKEEP(B6:C6,T_CHARS(1)))H23H23=SUM(AKEEP(B17:C18,T_CHARS(1)))L23L23=SUM(AKEEP(B14:D14,T_CHARS(1)))F26:G27F26=AKEEP(B26:C27,T_CHARS(1,,"."))J26J26=SUM(AKEEP(B26:C27,T_CHARS(1,,".")))F30F30=SUM(F26#)Dynamic array formulas.


----------



## Xlambda (Jan 13, 2022)

No time to rest. Another example for *REDUCE*, this time, Mike's ExcelIsFun today's YT (13-jan-22) : FILTER, LET, TRANSPOSE and Other Excel Functions: Mode Calculation for each Quartile. EMT 1771
We managed before to make an array to spill results horizontally, "byrow" style, with the help of another function, called "accumulator". (A1V function)
There is a way to do it without any external "accumulator". An extensive study will be published separately my first custom-made lambda helper function AROW or ABYROW, a lambda helper function that spills, and does not call any other function.
ETM1771-Ch03-ESA.xlsmABCDEFGHIJKLMNOPQRSTU1Formula D11 : =LET(a,B3:B36,q,QUARTILE.INC(a,SEQUENCE(5)-1),x,XMATCH(a,q,-1),
     REDUCE(0,SEQUENCE(4),LAMBDA(v,i,
       LET(x,TRANSPOSE(MODE.MULT(FILTER(a,x=i))),y,IF(COLUMNS(x)=1,IF({1,0},x,""),x),IFNA(IF(SEQUENCE(i)=i,y,v),"")))))2Data=QUARTILE.INC(B3:B36,D4:D8)32QuartValueMode/s440251012012=TRANSPOSE(MODE.MULT(FILTER($B$3:$B$36,($B$3:$B$36>=E4)*($B$3:$B$36<E5))))61224131=TRANSPOSE(MODE.MULT(FILTER($B$3:$B$36,($B$3:$B$36>=E5)*($B$3:$B$36<E6))))712367.544=TRANSPOSE(MODE.MULT(FILTER($B$3:$B$36,($B$3:$B$36>=E6)*($B$3:$B$36<E7))))8134796870=TRANSPOSE(MODE.MULT(FILTER($B$3:$B$36,($B$3:$B$36>=E7)*($B$3:$B$36<E8))))9151018single cell formula, spills horizontally1119121223311324441430687015311631first try was a shorter formula that had this problem:1731=LET(a,B3:B36,q,QUARTILE.INC(a,SEQUENCE(5)-1),x,XMATCH(a,q,-1),REDUCE(0,SEQUENCE(4),LAMBDA(v,i,IF(SEQUENCE(i)=i,TRANSPOSE(MODE.MULT(FILTER(a,x=i))),v))))1838121219393131why is that? REDUCE appends with every iteration another row. 20434444If the final result will "spill" max n clms, if one row spills a single value, will spread this single result to all clms.214468702244Like in this example:to append a single value "7" at the bottom of the array "a"2352a=IF(SEQUENCE(3)=3,7,H24:J25)24571231232561456456266477727662868to overcome this behavior(that happens only for single values) we have to "force" "7" in a  2 elem array ({7,""})29683070=IF(SEQUENCE(3)=3,IF({1,0},7,""),H24:J25)=IFNA(IF(SEQUENCE(3)=3,IF({1,0},7,""),H24:J25),"")3170123123327145645633727#N/A734733577This explain this part of final formula: 3679...y,IF(COLUMNS(x)=1,IF({1,0},x,""),x),IFNA(IF(SEQUENCE(i)=i,y,v),"")3738ModeInQuartile1Cell FormulasRangeFormulaE2E2=FORMULATEXT(E4)E4:E8E4=QUARTILE.INC(B3:B36,D4:D8)F5:F7,F8:G8F5=TRANSPOSE(MODE.MULT(FILTER($B$3:$B$36,($B$3:$B$36>=E4)*($B$3:$B$36<E5))))H5:H8H5=FORMULATEXT(F5)D11:E14D11=LET(a,B3:B36,q,QUARTILE.INC(a,SEQUENCE(5)-1),x,XMATCH(a,q,-1),REDUCE(0,SEQUENCE(4),LAMBDA(v,i,LET(x,TRANSPOSE(MODE.MULT(FILTER(a,x=i))),y,IF(COLUMNS(x)=1,IF({1,0},x,""),x),IFNA(IF(SEQUENCE(i)=i,y,v),"")))))D17,H30,L30,L23D17=FORMULATEXT(D18)D18:E21D18=LET(a,B3:B36,q,QUARTILE.INC(a,SEQUENCE(5)-1),x,XMATCH(a,q,-1),REDUCE(0,SEQUENCE(4),LAMBDA(v,i,IF(SEQUENCE(i)=i,TRANSPOSE(MODE.MULT(FILTER(a,x=i))),v))))L24:N26L24=IF(SEQUENCE(3)=3,7,H24:J25)H31:J33H31=IF(SEQUENCE(3)=3,IF({1,0},7,""),H24:J25)L31:N33L31=IFNA(IF(SEQUENCE(3)=3,IF({1,0},7,""),H24:J25),"")Dynamic array formulas.


----------



## Xlambda (Jan 14, 2022)

AROW example ETM1771-Ch03-ESA.xlsmABCDEFGHIJKLMNOPQRSTU1We had to amend the previous formula, to cover scenarios when 4'th quartile range max value is also one of MODE.MULT values (2 occurences)2(to simulate this scenario we modified last 2 values of "Data" array, 4'th quartile, to be max ones and both=79)34=QUARTILE.INC(A6:A39,SEQUENCE(5)-1)As we see, last xmatch values are 5, and they should "stay" 4 (5 values can have only 4 inner intervals/quartiles)5Data↓=XMATCH(A6:A39,C6#,-1)=IF(E6#=5,4,E6#)62211742011Amended formula:8104111 =LET(a,A6:A39,xm,XMATCH(a,QUARTILE.INC(a,SEQUENCE(5)-1),-1),x,IF(xm=5,4,xm),
      REDUCE(0,SEQUENCE(4),LAMBDA(v,i,
          LET(m,TRANSPOSE(MODE.MULT(FILTER(a,x=i))),r,IF(COLUMNS(m)=1,IF({1,0},m,""),m),IFNA(IF(SEQUENCE(i)=i,r,v),"")))))91267.5111012791111131112121511311318114414191168707915232216242217302218312219312220312221382222392223433324443325443326523327573328613329643330663331684432684433704434704435714436724437734438795439795440Sheet1Cell FormulasRangeFormulaC4C4=FORMULATEXT(C6)E5,H5E5=FORMULATEXT(E6)C6:C10C6=QUARTILE.INC(A6:A39,SEQUENCE(5)-1)E6:E39E6=XMATCH(A6:A39,C6#,-1)H6:H39H6=IF(E6#=5,4,E6#)J11:L14J11=LET(a,A6:A39,xm,XMATCH(a,QUARTILE.INC(a,SEQUENCE(5)-1),-1),x,IF(xm=5,4,xm),REDUCE(0,SEQUENCE(4),LAMBDA(v,i,LET(m,TRANSPOSE(MODE.MULT(FILTER(a,x=i))),r,IF(COLUMNS(m)=1,IF({1,0},m,""),m),IFNA(IF(SEQUENCE(i)=i,r,v),"")))))Dynamic array formulas.


----------



## Xlambda (Jan 14, 2022)

Modified more values through "Data" array to check if formula spills correctly. (1st and 3rd quartile)
C4 formula:

```
=LET(a,A4:A37,xm,XMATCH(a,QUARTILE.INC(a,SEQUENCE(5)-1),-1),x,IF(xm=5,4,xm),
    REDUCE(0,SEQUENCE(4),LAMBDA(v,i,
       LET(m,TRANSPOSE(MODE.MULT(FILTER(a,x=i))),r,IF(COLUMNS(m)=1,IF({1,0},m,""),m),IFNA(IF(SEQUENCE(i)=i,r,v),"")))))
```
AROW example ETM1771-Ch03-ESA.xlsmABCDEFGH1Modified "Data" to check if formula spills correctly.23Data42412195431644466712687079812913101511191219132314241530163117311831193820392143224423442452255726612766286629683068317032703371347235733679377938Sheet2Cell FormulasRangeFormulaC4:E7C4=LET(a,A4:A37,xm,XMATCH(a,QUARTILE.INC(a,SEQUENCE(5)-1),-1),x,IF(xm=5,4,xm),REDUCE(0,SEQUENCE(4),LAMBDA(v,i,LET(m,TRANSPOSE(MODE.MULT(FILTER(a,x=i))),r,IF(COLUMNS(m)=1,IF({1,0},m,""),m),IFNA(IF(SEQUENCE(i)=i,r,v),"")))))Dynamic array formulas.


----------



## Xlambda (Jan 14, 2022)

This thread, which covered so many examples using SCAN; REDUCE, deserves the first peek to (my) first custom made lambda helper function based on REDUCE, ABYROW , before opening a separate thread for it.
ABYROW is a BYROW lambda helper function that can spills results, horizontally, has same syntax:
=ABYROW(array,LAMBDA(row))
*ABYROW(ar,y)* 
*ar: array
y: lambda argument*

```
=LAMBDA(ar,y,
    LET(a,IF(ar="","",ar),r,REDUCE(0,SEQUENCE(ROWS(a)),LAMBDA(v,i,
       LET(w,INDEX(a,i,),f,FILTER(w,w<>""),x,IFERROR(y(f),""),j,COLUMNS(x)-1,z,IF(j,x,IF({1,0},x,"")),IFNA(IF(SEQUENCE(i)=i,z,v),"")))),
         IF(AND(COLUMNS(r)=2,INDEX(r,,2)=""),INDEX(r,,1),r)
    )
)
```
ABYROW.xlsxABCDEFGHIJKLMNOPQR1Using ABYROW to solve previous task. Checking that ABYROW spills horizontally.23Data42=LET(a,A4:A37,m,XMATCH(a,QUARTILE.INC(a,SEQUENCE(5)-1),-1),x,IF(m=5,4,m),ABYROW(SEQUENCE(4),LAMBDA(i,TRANSPOSE(MODE.MULT(FILTER(a,x=i))))))544121964317124466812687079913101511191219132314241530163117311831193820392143224423442452255726612766286629683068317032703371347235733679377938ABYROW peek 1Cell FormulasRangeFormulaC4C4=FORMULATEXT(C5)C5:E8C5=LET(a,A4:A37,m,XMATCH(a,QUARTILE.INC(a,SEQUENCE(5)-1),-1),x,IF(m=5,4,m),ABYROW(SEQUENCE(4),LAMBDA(i,TRANSPOSE(MODE.MULT(FILTER(a,x=i))))))Dynamic array formulas.


----------



## Xlambda (Jan 14, 2022)

ABYROW.xlsxABCDEFGHIJKLMNOPQR1Task: Extract the quartile sections of "Data" as separate rows. Unpivoting a vertical array in uneven rows, based on row pattern only.23=QUARTILE.INC(A5:A38,SEQUENCE(5)-1)4Data↓=LET(x,XMATCH(A5:A38,C5#,-1),IF(x=5,4,x))522164201=ABYROW(SEQUENCE(4),LAMBDA(i,TRANSPOSE(FILTER(A5:A38,E5#=i))))7441124412121315191981267.5123243031313138399127914344445257616666101316868707071727379791115112191How easy is now to get MODE.MULT with "spilling" ABYROW1319114232=ABYROW(G7#,LAMBDA(a,TRANSPOSE(MODE.MULT(a))))1524241219163023117312446618312687079193122038221392224332344324443255232657327613286632966330684316843270433704347143572436734377943879439ABYROW peek 2Cell FormulasRangeFormulaC3C3=FORMULATEXT(C5)E4,G14,G6E4=FORMULATEXT(E5)C5:C9C5=QUARTILE.INC(A5:A38,SEQUENCE(5)-1)E5:E38E5=LET(x,XMATCH(A5:A38,C5#,-1),IF(x=5,4,x))G7:O10G7=ABYROW(SEQUENCE(4),LAMBDA(i,TRANSPOSE(FILTER(A5:A38,E5#=i))))G15:I18G15=ABYROW(G7#,LAMBDA(a,TRANSPOSE(MODE.MULT(a))))Dynamic array formulas.


----------



## Xlambda (Jan 14, 2022)

ABYROW.xlsxABCDEFGHIJKLMNOPQRSTU1Task. Unflatten an array for a certain pattern.2Unflatten a vertical array into 3 rows. 1st row has 6 elements, 2nd 4, and 3rd 5 elements.34step1.step2. row pattern5elements/row=SCAN(0,C6:C8,LAMBDA(v,a,v+a))=XMATCH(SEQUENCE(MAX(E6#)),E6#,1)6a6617b4101step 3. ABYROW8c5151=ABYROW(SEQUENCE(ROWS(C6:C8)),LAMBDA(i,TRANSPOSE(FILTER(A6:A20,I6#=i))))9d1abcdef10eor1ABCD11f11234512A=ASCAN(C6:C8)213B6214C10215D1521613172318331943205321ABYROW peek 3Cell FormulasRangeFormulaE5,I5,E12,K8E5=FORMULATEXT(E6)E6:E8E6=SCAN(0,C6:C8,LAMBDA(v,a,v+a))I6:I20I6=XMATCH(SEQUENCE(MAX(E6#)),E6#,1)K9:P11K9=ABYROW(SEQUENCE(ROWS(C6:C8)),LAMBDA(i,TRANSPOSE(FILTER(A6:A20,I6#=i))))E13:E15E13=ASCAN(C6:C8)Dynamic array formulas.


----------



## Xlambda (Sep 30, 2021)

*ASCAN**, * *A*rray *SCAN*, *3 in 1 function*, *SCAN* *by row*, *by column*, *by array*. This is my take of tboulden's SCANBYROW/BYCOL.
Uses *only* *new!! SCAN* lambda helper function, no need of byrow,bycol,makearray, or lambda as arguments.

```
=LAMBDA(a,[d],
    LET(y,IF(d=1,TRANSPOSE(a),a),s,SCAN(0,y,LAMBDA(v,a,v+a)),r,ROWS(s),c,COLUMNS(s),sr,SEQUENCE(r)-1,
       x,s-IF(d,IFERROR(INDEX(INDEX(s,,c),sr)*sr^0,0),0),
       IF(d=1,TRANSPOSE(x),x)
   )
)
```
LAMBDA 1.1.3.xlsxABCDEFGHIJKLMNOPQRST1d,omitted (by array)d,-1 (by rows)d,1 (by clms)2sample 1a=ASCAN(A3:D6)=ASCAN(A3:D6,-1)=ASCAN(A3:D6,1)312341361013610123445678152128365111826681012591011124555667891930421518212461314151691105120136132742582832364078d,omitted (by array)d,-1 (by rows)d,1 (by clms)9sample 2a=ASCAN(A10#)=ASCAN(A10#,-1)=ASCAN(A10#,1)1012341361013610123411567815212836511182668101212910111245556678919304215182124131314151691105120136132742582832364014171819201531711902101735547445505560152122232423125327630021436690667278841617Note: Did not set an extra argument for any kind of "initial value", since the only thing it does is too simple,18adds a constant value to the final outcome , like in 10+ASCAN(a)19ASCAN postCell FormulasRangeFormulaF2,P9,K9,F9,P2,K2F2=FORMULATEXT(F3)F3:I6F3=ASCAN(A3:D6)K3:N6K3=ASCAN(A3:D6,-1)P3:S6P3=ASCAN(A3:D6,1)A10:D15A10=SEQUENCE(6,4)F10:I15F10=ASCAN(A10#)K10:N15K10=ASCAN(A10#,-1)P10:S15P10=ASCAN(A10#,1)Dynamic array formulas.


----------



## Xlambda (Jan 17, 2022)

Another example with *SCAN* that I forgot to mention, inspired by Mike's ExcelIsFun YT : Excel Statistical Analysis 09: Location, Mean, Median, Mode, Moving Average, Skew & More! 
(*min 33:00*)
It's about calculating *moving average*.
ABYROW.xlsxABCDEFGHIJKLMNOPQR1lambda "formula", Moving Average, using SCAN23What if you need the "moving average", like average of last 7 days or last 12 months?4Moving averages "smooth out" numbers to give you a better over view of the trend.(all numeric data, same format)5single cell (input array, entire column, formula knows where to stop)6Month EndSales6-month Moving Ave.=LET(a,C7:C37,nm,6,s,SCAN(0,SEQUENCE(ROWS(a)),LAMBDA(v,i,AVERAGE(INDEX(a,SEQUENCE(nm,,i))))),FILTER(s,NOT(ISERR(s))))731-07-22614,212494,631=AVERAGE(C7:C12)494,631830-06-22551,250456,528=AVERAGE(C8:C13)456,528931-05-22352,220410,297=AVERAGE(C9:C14)410,2971030-04-22607,028405,090=AVERAGE(C10:C15)405,0901131-03-22494,401342,149=AVERAGE(C11:C16)342,1491228-02-22348,672283,648=AVERAGE(C12:C17)283,6481331-01-22385,596273,681=AVERAGE(C13:C18)273,6811431-12-21273,864279,941=AVERAGE(C14:C19)279,9411530-11-21320,980282,871=AVERAGE(C15:C20)282,8711631-10-21229,383328,734=AVERAGE(C16:C21)328,7341730-09-21143,395337,529=AVERAGE(C17:C22)337,5291831-08-21288,865394,946=AVERAGE(C18:C23)394,9461931-07-21423,159393,964=AVERAGE(C19:C24)393,9642030-06-21291,445385,404=AVERAGE(C20:C25)385,4042131-05-21596,158366,708=AVERAGE(C21:C26)366,7082230-04-21282,149314,443=AVERAGE(C22:C27)314,4432331-03-21487,897358,810=AVERAGE(C23:C28)358,8102428-02-21282,977315,192=AVERAGE(C24:C29)315,1922531-01-21371,800307,968=AVERAGE(C25:C30)307,9682631-12-20179,265289,454=AVERAGE(C26:C31)289,4542730-11-20282,570282,478=AVERAGE(C27:C32)282,4782831-10-20548,348268,041=AVERAGE(C28:C33)268,0412930-09-20226,192219,677=AVERAGE(C29:C34)219,6773031-08-20239,634260,147=AVERAGE(C30:C35)260,1473131-07-20260,717295,960=AVERAGE(C31:C36)295,9603230-06-20137,409278,240=AVERAGE(C32:C37)278,2403331-05-20195,947drag and drop formula knows3430-04-20258,165has to stop herewhere to stop3531-03-20469,0093629-02-20454,5133731-01-20154,39638moving averageCell FormulasRangeFormulaF6F6=FORMULATEXT(H7)D7:D32D7=AVERAGE(C7:C12)E7:E32E7=FORMULATEXT(D7)H7:H32H7=LET(a,C7:C37,nm,6,s,SCAN(0,SEQUENCE(ROWS(a)),LAMBDA(v,i,AVERAGE(INDEX(a,SEQUENCE(nm,,i))))),FILTER(s,NOT(ISERR(s))))Dynamic array formulas.


----------



## Xlambda (Jan 17, 2022)

ABYROW.xlsxABCDEFGHIJKLMNOPQRS1Moving Average , different approach, unpivoting, using BYROW and ABYROW23=LET(a,C5:C35,nm,6,r,ROWS(a)-nm+1,s,SEQUENCE(,nm)+SEQUENCE(r)-1,x,INDEX(a,s),BYROW(x,LAMBDA(a,AVERAGE(a))))4Month EndSales↓=LET(a,C5:C35,nm,6,ABYROW(SEQUENCE(ROWS(a)-nm+1),LAMBDA(i,AVERAGE(INDEX(a,SEQUENCE(,6)+i-1)))))531-07-22614,212494,631494,631630-06-22551,250456,528456,528To prove that ABYROW is "natively" capable (BYROW is not)731-05-22352,220410,297410,297to "spread" (spill) a horizontal pattern from a vertical array (single values/row).830-04-22607,028405,090405,090ABYROW is can embed a pattern created from a column. 931-03-22494,401342,149342,149=ABYROW(SEQUENCE(26),LAMBDA(i,SEQUENCE(,6)+i-1))1028-02-22348,672283,648283,6481234561131-01-22385,596273,681273,6812345671231-12-21273,864279,941279,9413456781330-11-21320,980282,871282,8714567891431-10-21229,383328,734328,73456789101530-09-21143,395337,529337,529678910111631-08-21288,865394,946394,9467891011121731-07-21423,159393,964393,96489101112131830-06-21291,445385,404385,404910111213141931-05-21596,158366,708366,7081011121314152030-04-21282,149314,443314,4431112131415162131-03-21487,897358,810358,8101213141516172228-02-21282,977315,192315,1921314151617182331-01-21371,800307,968307,9681415161718192431-12-20179,265289,454289,4541516171819202530-11-20282,570282,478282,4781617181920212631-10-20548,348268,041268,0411718192021222730-09-20226,192219,677219,6771819202122232831-08-20239,634260,147260,1471920212223242931-07-20260,717295,960295,9602021222324253030-06-20137,409278,240278,2402122232425263131-05-20195,9472223242526273230-04-20258,1652324252627283331-03-20469,0092425262728293429-02-20454,5132526272829303531-01-20154,39626272829303136mov avrg 2Cell FormulasRangeFormulaE3E3=FORMULATEXT(E5)G4,I9G4=FORMULATEXT(G5)E5:E30E5=LET(a,C5:C35,nm,6,r,ROWS(a)-nm+1,s,SEQUENCE(,nm)+SEQUENCE(r)-1,x,INDEX(a,s),BYROW(x,LAMBDA(a,AVERAGE(a))))G5:G30G5=LET(a,C5:C35,nm,6,ABYROW(SEQUENCE(ROWS(a)-nm+1),LAMBDA(i,AVERAGE(INDEX(a,SEQUENCE(,6)+i-1)))))I10:N35I10=ABYROW(SEQUENCE(26),LAMBDA(i,SEQUENCE(,6)+i-1))Dynamic array formulas.


----------



## Xlambda (Jan 18, 2022)

Tweaked *ABYROW* lambda helper function to display a message *if no results are returned*, first variable after LET, "nr", easy to change if we want so.
Chose as default nr=NA(). Considered that is more helpful if ABYROW is called by other function inner calculations and NA() is easier to handle by IFNA(….

```
=LAMBDA(ar,y,
    LET(nr,NA(),a,IF(ar="","",ar),
      r,REDUCE(0,SEQUENCE(ROWS(a)),
         LAMBDA(v,i,LET(w,INDEX(a,i,),f,FILTER(w,w<>""),x,IFERROR(y(f),""),j,COLUMNS(x)-1,z,IF(j,x,IF({1,0},x,"")),IF(SEQUENCE(i)=i,z,v)))),b,IFNA(r,""),
     IFS(AND(b=""),nr,AND(COLUMNS(b)=2,INDEX(b,,2)=""),INDEX(b,,1),TRUE,b)
    )
)
```
ABYROW.xlsxABCDEFGHIJKLMNOP1ABYROW. Handling "no result" byrow scenarios.2"nr" variable=NA()if nr="" function returns "" , so can be confusing,3=ABYROW(C4:D5,LAMBDA(a,FILTER(a,ISNUMBER(a))))better to have something4ab#N/A5cd671.filter only numeric values byrow89manually (drag and drop)10F6:F12 =FILTER(A11:D11,ISNUMBER(A11:D11))=ABYROW(A11:D17,LAMBDA(a,FILTER(a,ISNUMBER(a))))1112ab121212cd3e3313fghi#CALC!14j45645645615789x78978916klmn#CALC!17o10p111011101118192. count the rows that have no numeric values3. remove empty rows2021=SUM(--(J11:J17=""))=FILTER(J11#,J11:J17<>"")22212233244. average of numbers, if empty numeric rows are considered 0's 4562578926=IF(J11:J17="",0,J11#)10112712283290003045631789320003310113435=AVERAGE(F27#)363.88243738compared to 3940=AVERAGE(A11:D17)41642ABYROW peek 4Cell FormulasRangeFormulaF3,F40,F35,F26,J21,F21,J10F3=FORMULATEXT(F4)F4F4=ABYROW(C4:D5,LAMBDA(a,FILTER(a,ISNUMBER(a))))F10F10="F6:F12"&" "&FORMULATEXT(F11)J11:L17J11=ABYROW(A11:D17,LAMBDA(a,FILTER(a,ISNUMBER(a))))F11:G11,F17:G17,F14:H15,F12:F13,F16F11=FILTER(A11:D11,ISNUMBER(A11:D11))F22F22=SUM(--(J11:J17=""))J22:L26J22=FILTER(J11#,J11:J17<>"")F27:H33F27=IF(J11:J17="",0,J11#)F36F36=AVERAGE(F27#)F41F41=AVERAGE(A11:D17)Dynamic array formulas.


----------



## Xlambda (Jan 18, 2022)

Glimpse of *ABYCOL, AROW.*
As i said before, a separate thread will follow with all the concepts, designs, explanations, formulas unfolding, examples. Formulas are short and simple.
ABYROW is designed to "append" or "accumulate the horizontally spilled results, row by row.
ABYCOL is designed to "append" or "accumulate the vertically spilled results, column by column.
Lesson learned from recursive functions, because of its limitations and calculation times: find ways and solutions to do it in both directions, horiz. and vert., and, depending on the case scenario, always use the shorter dimension.
(ARRANGEMENTS thread covers this concept, 100's of thousands of rows and several columns)
Therefore, for scenarios that have lot more rows than columns, AROW is a function that spills the results horizontally but appends them not row by row, appends them column by column.
*ABYCOL(ar,y)*

```
=LAMBDA(ar,y,
    LET(nr,NA(),a,IF(ar="","",ar),
      r,REDUCE(0,SEQUENCE(COLUMNS(a)),
         LAMBDA(v,i,LET(w,INDEX(a,,i),f,FILTER(w,w<>""),x,IFERROR(y(f),""),j,ROWS(x)-1,z,IF(j,x,IF({1;0},x,"")),IFNA(IF(SEQUENCE(,i)=i,z,v),"")))),b,IFNA(r,""),
   IFS(AND(r=""),nr,AND(ROWS(b)=2,INDEX(b,2,)=""),INDEX(b,1,),TRUE,b)
    )
)
```
*AROW(ar,y) *calls ABYCOL

```
=LAMBDA(ar,y,
   LET(a,IF(ar="","",ar),r,ROWS(a),c,COLUMNS(a),sr,SEQUENCE(r),sc,SEQUENCE(,c),
      x,ABYCOL(sc,LAMBDA(i,INDEX(BYROW(a,LAMBDA(x,INDEX(y(x),i))),sr))),
      FILTER(x,NOT(BYCOL(x,LAMBDA(b,AND(b="")))))
   )
)
```
Let`s put them to a test.
*Setup that anybody can reproduce with ease. Setup time should not take more than 10min, testing time though…..some time.*
*Step1. Define all the functions
Step 2. Build sample random array
Step 3. "spin value(E4)   = "y"
Step 4. Set tot nr. of rows value (E3)
Step 5. "spin" = "n"
Step 6. call one of the functions, note down calc. time, place a space bar in front of " ="
Step 7. go to step 3. (delete the leading space to activate the calculation for new nr. rows value), note the results.*
ABYROW.xlsxABCDEFGHIJKLMNOPQRSTUVWXY1arraySet up array, randomly created Function to be testedFunctions to test:2elementsfixed 10 clms  wideLAMBDA(a,UNIQUE(a,1))3nr. rows200000(unique "byrows")1. =LET(
    thunks,
        BYROW(C6#,
            LAMBDA(row_,
                LAMBDA(TRANSPOSE(UNIQUE(TRANSPOSE(row_))))
            )),
    rows_,ROWS(thunks),
    cols_,MAX(MAP(thunks,LAMBDA(thunk,COLUMNS(thunk())))),
    MAKEARRAY(rows_,cols_,
        LAMBDA(i,j,
            INDEX(
                INDEX(thunks,i,1)(),
                1,
                j
            )
        )
    )
)4Aspinntboulden's5B=IF(E4="n",C6#,LET(a,INDEX(A4:A49,RANDARRAY(E3,10,1,46,1)),IFERROR(--a,a)))thunks6C7C432EXSCL7DK1Q9AKOPQW8EON23HXWZSU9FJCM3C93OVE10GURKFMGW44311HBE4T7HD85M12II457J4421713J33S3W06N2614K2QVL1YD4D915LV5GCTL172116MRUJ3GM7A9817N010BJ3SBGW18O62GY9Q2NTF19P15ME12ERTW20Q366PHR642T21RZE3964KB8B22SSIZVC4965B2. =ABYROW(C6#,LAMBDA(a,UNIQUE(a,1)))23T4UYTSH373P3. =AROW(C6#,LAMBDA(a,UNIQUE(a,1)))calls ABYCOL24U8XMH917L8E4. =AFUSBYROW(C6#:L15,,1)calls AFLAT25VZ32GH8J13C(4. classic, no lambda helper functions used)26WXH1NWSJ2QA27XGU6V1RZD5LNote: 2,3 and 4 functions, perform lot more intestine calculations28Y8J0QFUGC46since are designed as "functional" functions.29Z74JYTS8R9J(can handle , errors, null strings, wrong arguments)300811OGYTPD331163O9FOH9UJ3222E2RHZCS14links to other functions:333T0868NJLLV(use  latest posts/variants on respective threads)3442F6EEYO29XABYROWthis thread355K5TPS1JHQ0ABYCOLthis thread3667W827JGX40AROWthis thread377I89BXP53FWAFUSBYROW388IS9KQRES3KAFLAT39900GX166OF94009B18J8YAKN411T9MPK3BEZD4224CJX157AYIResultsthunksABYROWAROWAFUSBYROW433689CC85VK2array rows1. calc time2. calc time3. calc time4. calc time444JH4WGJ301D1,00010.03 s1.12 sinstantinstant45518VJ0UQEVP2,00041.35 s5.43 sinstantinstant466WASRNQZ9Z53,00094.44 s12.31 sinstantinstant477S20XK4A2VX4,000192.29 s23.88 sinstantinstant488P6W0GVJXO45,000298.22 s37.28 s0.23 sinstant4993SXXW2CLJQ10,000??1.35 sinstant503WIZ28H2PO100,000??12.27 s4.6 s51I10NPUIBE3200,000??22.37 s  !!!error *52073A4D3ZFItiming errors aprox. +0.5s*using AFLAT, 200,000 rows * 10 clms=53DJE6B16A6D!!! Quite a lot, but so far,= 2M rows (excedes 1.5M rows spreadsheet real estate545GIQ13V280I do not have a better solution, yet!(horiz/vert size do not affect calc. time, only r*c value)554XFFPF8E8V563580B82103Important note: 57J91O1N6RO6If anybody has other functions, other designs, other results, please feel free to share it with us here.583EKU4IBC4259KEOGT2WFGGGlimpse of results returned by AROWcheck200000=ROWS(O61#)609N4WPCC0MK=AROW(C6#,LAMBDA(a,UNIQUE(a,1)))612S4G887XN27C432EXSL62AAW63P5IZ0K1Q9AOPW6327NV9F27I9ON23HXWZSU642F8HDN3QEQJCM39OVE6560FEU0YSD0URKFMGW43660S7YEZPHGEBE4T7HD85M67BBT0325HP5I457J21688PV741YOVU3SW06N2694MLDIU13882QVL1YD4970I6AYY4FACQV5GCTL17271AFQMK8TC3FRUJ3GM7A9872WXW76N2N8L01BJ3SGW73R55YN4370762GY9QNTF74N1FAVI1H0J15ME2RTW7500T58XKJX436PHR42T764FWT129963ZE3964KB87791OP8QZ080SIZVC4965B7861201GGSR64UYTSH37P7942ZSS7NKLY8XMH917LEABYCOL  peek 1Cell FormulasRangeFormulaC5,O60C5=FORMULATEXT(C6)C6:L200005C6=IF(E4="n",C6#,LET(a,INDEX(A4:A49,RANDARRAY(E3,10,1,46,1)),IFERROR(--a,a)))T59T59=ROWS(O61#)U59U59=FORMULATEXT(T59)O61:X200060O61=AROW(C6#,LAMBDA(a,UNIQUE(a,1)))Dynamic array formulas.


----------



## Xlambda (Jan 20, 2022)

New *AROW* , does not call any custom-made function. 18-20% faster than previous AROW ( AROW(old))
Added calculation time for sample array of 300,000 rows.

```
=LAMBDA(ar,y,
    LET(a,IF(ar="","",ar),
      r,REDUCE(0,SEQUENCE(,COLUMNS(a)),LAMBDA(v,i,IF(SEQUENCE(,i)=i,
        BYROW(a,LAMBDA(x,INDEX(y(FILTER(x,x<>"")),,i))),v))),b,IFERROR(r,""),
        FILTER(b,BYCOL(b,LAMBDA(x,NOT(AND(x="")))))
    )
)
```
ABYROW.xlsxABCDEFGHIJKLMNOPQRSTUVWXY1arraySet up array, randomly created Function to be testedFunctions to test:2elementsfixed 10 clms  wideLAMBDA(a,UNIQUE(a,1))3nr. rows300000(unique "byrows")1. =LET(
    thunks,
        BYROW(C6#,
            LAMBDA(row_,
                LAMBDA(TRANSPOSE(UNIQUE(TRANSPOSE(row_))))
            )),
    rows_,ROWS(thunks),
    cols_,MAX(MAP(thunks,LAMBDA(thunk,COLUMNS(thunk())))),
    MAKEARRAY(rows_,cols_,
        LAMBDA(i,j,
            INDEX(
                INDEX(thunks,i,1)(),
                1,
                j
            )
        )
    )
)4Aspinntboulden's5B=IF(E4="n",C6#,LET(a,INDEX(A4:A49,RANDARRAY(E3,10,1,46,1)),IFERROR(--a,a)))thunks6C4H7E7HE8DN7DK8Z5W8N39Y8E0464D5QB7Z9F0JJD08QKA410G7K36B4N9N911HQA66ONJRC712I420LKPL0FC13J5BU9A4205414K5XP6S1QJQZ15LSOP42M10UO16M24T1CHG7N417NDY8BP2E82718OC55CPTG5FY19PUQL73XA6K220QEZ87FU8O4Z21R8DAVL0K38222S4X0PYJAU2T2. =ABYROW(C6#,LAMBDA(a,UNIQUE(a,1)))23TF99RW560MD3. =AROW(C6#,LAMBDA(a,UNIQUE(a,1))) (old one)calls ABYCOL24UP5XZ203SH54. =AFUSBYROW(C6#:L15,,1)calls AFLAT25VH002EQP71J5. =AROW(C6#,LAMBDA(a,UNIQUE(a,1))) (new one)26W19FM7J90Z2(4. classic, no lambda helper functions used)27XL97D3UR8I628Y0LG5K5R937Note: 2,3 and 4 functions, perform lot more intestine calculations29ZL4PKYMDW49since are designed as "functional" functions.3008U1KB33WH7(can handle , errors, null strings, wrong arguments)31105NSHLKY3F322GJEJ4GH52L333NK8WS6Q03Rlinks to other functions:344MAOHV1U7D8(use  latest posts/variants on respective threads)355G1RZ74N1C8ABYROWthis thread366G7105WUU2BABYCOLthis thread3779314FX6QT1AROW (old,new))this thread3884VPCDVU7D2AFUSBYROW39901D6NBE76FAFLAT400B504BKY000411C19X0W9SZ8422LV10C5CG5NNEW !!433FTQ0039736ResultsthunksABYROWAROW(old)AROW(new)AFUSBYROW4440V89A3D1U6array rows1. calc time2. calc time3. calc time5. calc time4. calc time455GY163O0C9R1,00010.03 s1.12 sinstantinstantinstant466084ADS1F602,00041.35 s5.43 sinstantinstantinstant477N78T9G5Y913,00094.44 s12.31 sinstantinstantinstant488RSCDVWCOZO4,000192.29 s23.88 sinstantinstantinstant4992IKPM6MRE65,000298.22 s37.28 s0.23 sinstantinstant5042324I414410,000??1.35 s1.13 sinstant51OJW5HEVY5P100,000??12.27 s10.31 s4.6 s520D15LE95Y7200,000??22.37 s18.91 !!!error *537E9U910C70300,000??34.64 s29.11 !!!error *54L55JIIOBZ9timing errors aprox. +0.5s*using AFLAT, 200,000 rows * 10 clms=559HBSEULCA6!!! Quite a lot, but so far,= 2M rows (excedes 1.5M rows spreadsheet real estate5638I1A8HIG2I do not have a better solution, yet !(horiz/vert size do not affect calc. time, only r*c value)57G6T749YFSW588NXZF7K9UVImportant note: 59MO2O8NAP5FIf anybody has other functions, other designs, other results, please feel free to share it with us here.603IWHVG958961HYX3X45TCLGlimpse of results returned by AROWcheck300000=ROWS(O63#)62BMKR7D5YP6=AROW(C6#,LAMBDA(a,UNIQUE(a,1)))638J8PEJ7G754H7E8DN64OEWC350A36K8Z5WN39Y65QSY2ZJ15CX046D5QB7Z6662NX7Q4U5W0JD8QKA4673HVZEHHA9P7K36B4N9683B51P967PUQA6ONJRC769H8P0401859420LKPFC70973FC6RH325BU9A42071W5EHO0ZH035XP6S1QJZ72K885CM8A49SOP42M10U7383MB4LD3L524T1CHG7N74M5087D102SDY8BP2E7758CQ31A5OQ6C5PTGFY760D9O1O1VVGUQL73XA6K277GO75J22M20EZ87FUO478I6KER0VM2R8DAVL0K3279EYC5K978E24X0PYJAU2T808WYZZ802G8F9RW560MD812N057EJE3DP5XZ203SH82B4877W8QLGH02EQP71J8331F4LXZYJ819FM7J0Z284B7N5PIVH40L97D3UR8I6ABYCOL  peek 1Cell FormulasRangeFormulaC5,O62C5=FORMULATEXT(C6)C6:L300005C6=IF(E4="n",C6#,LET(a,INDEX(A4:A49,RANDARRAY(E3,10,1,46,1)),IFERROR(--a,a)))T61T61=ROWS(O63#)U61U61=FORMULATEXT(T61)O63:X300062O63=AROW(C6#,LAMBDA(a,UNIQUE(a,1)))Dynamic array formulas.


----------



## Xlambda (Jan 20, 2022)

*1,000,000* (1 milion) *rows* tested, *AROW* took 209.95 seconds. During this time *thunks* method can calculate *4,000* something *rows*.
ABYROW.xlsxABCDEFGHIJKLMNOPQRSTUVWXY1arraySet up array, randomly created Function to be testedFunctions to test:2elementsfixed 10 clms  wideLAMBDA(a,UNIQUE(a,1))3nr. rows1000000(unique "byrows")1. =LET(
    thunks,
        BYROW(C6#,
            LAMBDA(row_,
                LAMBDA(TRANSPOSE(UNIQUE(TRANSPOSE(row_))))
            )),
    rows_,ROWS(thunks),
    cols_,MAX(MAP(thunks,LAMBDA(thunk,COLUMNS(thunk())))),
    MAKEARRAY(rows_,cols_,
        LAMBDA(i,j,
            INDEX(
                INDEX(thunks,i,1)(),
                1,
                j
            )
        )
    )
)4Aspinytboulden's5B=IF(E4="n",C6#,LET(a,INDEX(A4:A49,RANDARRAY(E3,10,1,46,1)),IFERROR(--a,a)))thunks6CKHPZ85OPJN7D64GY5K9CJT8E0RF9P0IA169FD1MCC83MSL10G8LG6RE034X11HS7F8788YC812INQ51NW328X13J25P88R915714K49UM4K0IFR15LGBH6Z624MB16MV1JFEQ2QUG17NSI4369FWR018OJU98A3744B19P4610UIVH4520QZC6B1D25GV21RQ41IB9I67T22SD10FKNV8OG2. =ABYROW(C6#,LAMBDA(a,UNIQUE(a,1)))23TCHBECHLM2D3. =AROW(C6#,LAMBDA(a,UNIQUE(a,1))) (old one)calls ABYCOL24U2LWIFTFNYJ4. =AFUSBYROW(C6#:L15,,1)calls AFLAT25VOX0RTZRK0O5. =AROW(C6#,LAMBDA(a,UNIQUE(a,1))) (new one)26W5L7B4NO679(4. classic, no lambda helper functions used)27XU05EY990C128Y1HCNHICI78Note: 2,3 and 4 functions, perform lot more intestine calculations29Z935KI5NZI8since are designed as "functional" functions.300M8WW0P8O6C(can handle , errors, null strings, wrong arguments)3113CFN47060932215TS76TWM5333LX02V4D0R5links to other functions:344Y079SLAQEE(use  latest posts/variants on respective threads)355CCPS19XR5IABYROWthis thread366ITL9XTAQ7PABYCOLthis thread377BCF4GGM48RAROW (old,new))this thread388I1X3WLDHRBAFUSBYROW3992T3R9UQS9JAFLAT40070285GMCX2411WEDLA9FP554224BIHG2DU78NEW !!433R7F4BPXE1PResultsthunksABYROWAROW(old)AROW(new)AFUSBYROW444720SSHGEBQarray rows1. calc time2. calc time3. calc time5. calc time4. calc time455H4FFYI89W41,00010.03 s1.12 sinstantinstantinstant46696Q525MUJ42,00041.35 s5.43 sinstantinstantinstant4770382U6Y8QT3,00094.44 s12.31 sinstantinstantinstant488MC0X9Z0X1P4,000192.29 s23.88 sinstantinstantinstant499G69D0RVWRD5,000298.22 s37.28 s0.23 sinstantinstant507UI938G50710,000??1.35 s1.13 sinstant51C2SG5Q7K60100,000??12.27 s10.31 s4.6 s52O498T75U4Y200,000??22.37 s18.91 !!!error *53P5T0435RC0300,000??34.64 s29.11 !!!error *543B8T1JMNC4timing errors aprox. +0.5s*using AFLAT, 200,000 rows * 10 clms=55V8W1BBF49T!!! Quite a lot, but so far,= 2M rows (excedes 1.5M rows spreadsheet real estate565CI62LZI7FI do not have a better solution, yet !(horiz/vert size do not affect calc. time, only r*c value)578PQEQ2256B58N4QR3D9CPJImportant note: 5961O8J9JHYJIf anybody has other functions, other designs, other results, please feel free to share it with us here.606VQ08HG1EO6112C9115IM0Glimpse of results returned by AROWcheck1000000=ROWS(O63#)62Q30OZ6U78Z=AROW(C6#,LAMBDA(a,UNIQUE(a,1)))632IFTHFTXB6KHPZ85OJN64BGEFBXJCCU64GY5K9CJT650NQ15M304W0RF9PIA1666D3N6WDA64JD1MC83SL67L89SPR111B8LG6RE034X688ZIMQ8J4OES7F8YC697904TPD7W4NQ51W328X707TDJSM5F8O25P8R91771860QQ0GC7949UMK0IFR72SZ4014J9XIGBH6Z24M73HT82HW0317V1JFEQ2UG749ZS1ER6N7CSI4369FWR07547EB7Z1SL2JU98A374B7667MVYHVJAI4610UIVH577YH7U2518O4ZC6B1D25GV78RY7049H02CQ41IB967T79E0ZOPKBF57D10FKNV8OG8078Z1OHKC2FCHBELM2D812MF4F3IGYX2LWIFTNYJ825U53GTEB58OX0RTZK83VU3D2V47BW5L7B4NO6984K903QGR09WU05EY9C185KW41PN1M131HCNI78ABYCOL  peek 1Cell FormulasRangeFormulaC5,O62C5=FORMULATEXT(C6)C6:L1000005C6=IF(E4="n",C6#,LET(a,INDEX(A4:A49,RANDARRAY(E3,10,1,46,1)),IFERROR(--a,a)))T61T61=ROWS(O63#)U61U61=FORMULATEXT(T61)O63:X1000062O63=AROW(C6#,LAMBDA(a,UNIQUE(a,1)))Dynamic array formulas.


----------



## Xlambda (Jan 21, 2022)

Another example, inspired from Mike's today's YT (21-Jan-22) Excel Statistical Analysis 15: 5-Number Summary / Box & Whiskers Chart: Power Query & Array Formulas
*ACOL* is easy to deduct since it is "perpendicular" to *AROW*.

```
=LAMBDA(ar,y,LET(a,IF(ar="","",ar),
     r,REDUCE(0,SEQUENCE(ROWS(a)),LAMBDA(v,i,IF(SEQUENCE(i)=i,
       BYCOL(a,LAMBDA(x,INDEX(y(FILTER(x,x<>"")),i,))),v))),b,IFERROR(r,""),
          FILTER(b,BYROW(b,LAMBDA(x,NOT(AND(x="")))))
    )
)
```
ABYROW.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZ1cpaTblformula to unpivot table2CPA Test 1CPA Test 2CPA Test 3CPA Test 4CPA Test 5=LET(a,cpaTbl,r,ROWS(a),c,COLUMNS(a),s,SEQUENCE(r*c),q,QUOTIENT(s-1,c)+1,m,MOD(s-1,c)+1,IF({1,0},INDEX(cpaTbl[#Headers],m),INDEX(a,q,m)))32228433555CPA Test 12244914324553CPA Test 2281. if input data table format (cpaTbl)5705526130CPA Test 343Note: Sample table has less rows than original, to fit real-estate of mini-sheet6136553768CPA Test 435lambda argument quartile.inc(a,sequence(5)-1) spills vertically75280515258CPA Test 55584467625531CPA Test 149lambda formula91881564561CPA Test 214=REDUCE(0,SEQUENCE(5),LAMBDA(v,i,IF(SEQUENCE(i)=i,BYCOL(cpaTbl,LAMBDA(a,QUARTILE.INC(a,i-1))),v)))10524731757CPA Test 3321355413114951474487CPA Test 4454449343753123767125760CPA Test 5535158494459137866645866CPA Test 1707066625366147581615066CPA Test 257881896187157063495364CPA Test 352165115673932CPA Test 461=ACOL(cpaTbl,LAMBDA(a,QUARTILE.INC(a,SEQUENCE(5)-1)))175054343462CPA Test 5301355413187050896017CPA Test 1134449343753197154404013CPA Test 265515849445920724973459CPA Test 357066625366211762474377CPA Test 4377881896187224558694356CPA Test 568236264313674CPA Test 1522. if input data as 2 column array24CPA Test 280=LET(a,H3:H107,b,I3:I107,c,COUNTA(UNIQUE(a)),r,ROWS(a)/c,q,INDEX(b,SEQUENCE(r,c)),ACOL(q,LAMBDA(x,QUARTILE.INC(x,SEQUENCE(5)-1))))25CPA Test 351135541326CPA Test 452444934375327CPA Test 558515849445928CPA Test 144706662536629CPA Test 267788189618730CPA Test 36231CPA Test 45532CPA Test 53133CPA Test 11834CPA Test 28135CPA Test 35636CPA Test 44537CPA Test 56138CPA Test 15239CPA Test 24740CPA Test 33141CPA Test 4742CPA Test 55743CPA Test 14944CPA Test 25145CPA Test 34746CPA Test 44447CPA Test 58748CPA Test 13749CPA Test 26750CPA Test 31251CPA Test 45752CPA Test 56053CPA Test 17854CPA Test 26655CPA Test 36456CPA Test 45857CPA Test 56658CPA Test 17559CPA Test 28160CPA Test 36161CPA Test 45062CPA Test 56663CPA Test 17064CPA Test 26365CPA Test 34966CPA Test 45367CPA Test 56468CPA Test 15169CPA Test 21570CPA Test 36771CPA Test 43972CPA Test 53273CPA Test 15074CPA Test 25475CPA Test 33476CPA Test 43477CPA Test 56278CPA Test 17079CPA Test 25080CPA Test 38981CPA Test 46082CPA Test 51783CPA Test 17184CPA Test 25485CPA Test 34086CPA Test 44087CPA Test 51388CPA Test 17289CPA Test 24990CPA Test 37391CPA Test 4492CPA Test 55993CPA Test 11794CPA Test 26295CPA Test 34796CPA Test 44397CPA Test 57798CPA Test 14599CPA Test 258100CPA Test 369101CPA Test 443102CPA Test 556103CPA Test 162104CPA Test 264105CPA Test 331106CPA Test 436107CPA Test 574108ACOLCell FormulasRangeFormulaH2,K24,K16,K9H2=FORMULATEXT(H3)H3:I107H3=LET(a,cpaTbl,r,ROWS(a),c,COLUMNS(a),s,SEQUENCE(r*c),q,QUOTIENT(s-1,c)+1,m,MOD(s-1,c)+1,IF({1,0},INDEX(cpaTbl[#Headers],m),INDEX(a,q,m)))K10:O14K10=REDUCE(0,SEQUENCE(5),LAMBDA(v,i,IF(SEQUENCE(i)=i,BYCOL(cpaTbl,LAMBDA(a,QUARTILE.INC(a,i-1))),v)))K17:O21K17=ACOL(cpaTbl,LAMBDA(a,QUARTILE.INC(a,SEQUENCE(5)-1)))K25:O29K25=LET(a,H3:H107,b,I3:I107,c,COUNTA(UNIQUE(a)),r,ROWS(a)/c,q,INDEX(b,SEQUENCE(r,c)),ACOL(q,LAMBDA(x,QUARTILE.INC(x,SEQUENCE(5)-1))))Dynamic array formulas.Named RangesNameRefers ToCellsExternalData_1=ACOL!$B$2:$F$23H3


----------



## Xlambda (Jan 21, 2022)

ABYROW.xlsxABCDEFGHIJKLMNOPQRSTUVWX1both orientations, when data 2clms array2CPA Test 1223CPA Test 228=UNIQUE(A2:A106)4CPA Test 343↓↓=LET(a,A2:A106,b,B2:B106,r,ROWS(UNIQUE(a)),AROW(INDEX(b,SEQUENCE(r)+(SEQUENCE(,ROWS(a)/r)-1)*r),LAMBDA(x,QUARTILE.INC(x,SEQUENCE(,5)-1))))5CPA Test 435CPA Test 113445170786CPA Test 555CPA Test 25495866817CPA Test 149CPA Test 35344962898CPA Test 214CPA Test 44374453619CPA Test 332CPA Test 5135359668710CPA Test 44511CPA Test 553=TRANSPOSE(UNIQUE(A2:A106))12CPA Test 170CPA Test 1CPA Test 2CPA Test 3CPA Test 4CPA Test 513CPA Test 25135541314CPA Test 352444934375315CPA Test 461515849445916CPA Test 530706662536617CPA Test 113788189618718CPA Test 265E13=LET(a,A2:A106,b,B2:B106,c,ROWS(UNIQUE(a)),ACOL(INDEX(b,SEQUENCE(,c)+(SEQUENCE(ROWS(a)/c)-1)*c),LAMBDA(x,QUARTILE.INC(x,SEQUENCE(5)-1))))19CPA Test 3520CPA Test 43721CPA Test 56822CPA Test 15223CPA Test 28024CPA Test 35125CPA Test 45226CPA Test 55827CPA Test 14428CPA Test 26729CPA Test 36230CPA Test 45531CPA Test 53132CPA Test 11833CPA Test 28134CPA Test 35635CPA Test 44536CPA Test 56137CPA Test 15238CPA Test 24739CPA Test 33140CPA Test 4741CPA Test 55742CPA Test 14943CPA Test 25144CPA Test 34745CPA Test 44446CPA Test 58747CPA Test 13748CPA Test 26749CPA Test 31250CPA Test 45751CPA Test 56052CPA Test 17853CPA Test 26654CPA Test 36455CPA Test 45856CPA Test 56657CPA Test 17558CPA Test 28159CPA Test 36160CPA Test 45061CPA Test 56662CPA Test 17063CPA Test 26364CPA Test 34965CPA Test 45366CPA Test 56467CPA Test 15168CPA Test 21569CPA Test 36770CPA Test 43971CPA Test 53272CPA Test 15073CPA Test 25474CPA Test 33475CPA Test 43476CPA Test 56277CPA Test 17078CPA Test 25079CPA Test 38980CPA Test 46081CPA Test 51782CPA Test 17183CPA Test 25484CPA Test 34085CPA Test 44086CPA Test 51387CPA Test 17288CPA Test 24989CPA Test 37390CPA Test 4491CPA Test 55992CPA Test 11793CPA Test 26294CPA Test 34795CPA Test 44396CPA Test 57797CPA Test 14598CPA Test 25899CPA Test 369100CPA Test 443101CPA Test 556102CPA Test 162103CPA Test 264104CPA Test 331105CPA Test 436106CPA Test 574107ACOL 2Cell FormulasRangeFormulaD3D3=FORMULATEXT(D5)E4,E11E4=FORMULATEXT(E5)D5:D9D5=UNIQUE(A2:A106)E5:I9E5=LET(a,A2:A106,b,B2:B106,r,ROWS(UNIQUE(a)),AROW(INDEX(b,SEQUENCE(r)+(SEQUENCE(,ROWS(a)/r)-1)*r),LAMBDA(x,QUARTILE.INC(x,SEQUENCE(,5)-1))))E12:I12E12=TRANSPOSE(UNIQUE(A2:A106))E13:I17E13=LET(a,A2:A106,b,B2:B106,c,ROWS(UNIQUE(a)),ACOL(INDEX(b,SEQUENCE(,c)+(SEQUENCE(ROWS(a)/c)-1)*c),LAMBDA(x,QUARTILE.INC(x,SEQUENCE(5)-1))))E18E18=FORMULATEXT(E13)Dynamic array formulas.


----------



## Xlambda (Mar 27, 2022)

As you all know already, we have a set of 14 amazing new functions. Excel Team really means business. 
Having more and more built-in function tools, very specific for very specific simple tasks, each with a lot of arguments for fine tuning, strengthens the idea I had here, a while ago: excel future will be more about lambda "formulas" and not about custom made lambda functions. I think that more than 90% of common excel tasks spectrum covered by all Excel content creators, can be solved now with simple short lambda formulas. And this thread is living proof of the idea.
In this context, if we still want to design competitive cm lambdas (custom-made), they should push the limits and address concepts that Excel cannot cover, not because they can't, because they must play safe.
That's why we do not have, yet, lambda helper functions that can spill byrow or bycol. It's safer to deliver a single result.
This should not discourage us from writing cm lambda functions, quite the opposite, but, will not be too much about the function itself anymore. 
It will be more about the concept design, its simplicity, versatility, and efficiency.
It will be more about the techniques used and studies of the spectrum of tasks it can solve with as many examples as possible.
Why ? Because a cm function will always need to be updated with every new release of new built-in functions (or even replaced entirely), while the concept and techniques will stay.
Mastering the designs and the techniques will build up our confidence and skills to solve complicated tasks with simple short formulas that hold the concept idea.
A cm function is only a volatile framework holder of its concept.
However, there is a fundamental difference between cm functions and built-in ones, apart from the obvious reasons, and I am positive that Excel Team is aware of that.
A cm function does not look good if it calls too many other cm functions or if has too many arguments. But if it calls for example 10 "native" ones it's ok, it's "legit", no complaints.
It's like translating a word using only words that also need translation. Defeats the purpose of communication.
What the Excel Team is offering us is a well written comprehensive dictionary and we should be grateful for that.
More words in the dictionary, more sentences or even phrases can be developed, and everybody will understand them.
I have so many functions that I did not "publish" because I did not want them to be "lost in translation" and now they can be shared because they can be translated into "native" Excel.
My functions will no longer need to call AFLAT, APP2H, APP2V, ARESIZE and AFILL, ASTACK, AUNSTACK, ASELECT and ARRANGE, they will call TOCOL, HSTACK, VSTACK, DROP and TAKE and EXPAND, WRAPROWS, WRAPCOLS, CHOOSECOLS and CHOOSEROWS.
Needless to say, the versatility of built-in functions, which can support multiple arguments like array1,array2,…. is priceless and more difficult for us to reproduce, though, not imposible.
In this respect I dare to wish for arrays of arguments/variables with an index tool to be able to access them iteratively, like in second syntax of INDEX where we can write an array of ranges INDEX((range1, range2…),row_num,col_num,area_num) and call them one by one using area_num index tool and count them with AREAS function.
But not only with ranges that reside in same spreadsheet, should be possible for any arguments or variables.
Even if it was pure coincidence, I wished before for more recursion iterations and it happened. ✌️?
To conclude, fantastic job Excel Team, Excel future looks brighter and brighter, can't wait to see what is still to come, can't wait to see them widely implemented for all users.
This niche one-of-a-kind forum will soon become a very crowded place.


----------



## Xlambda (Mar 27, 2022)

14 new functions but still no lambda helper function that can spill byrow/bycol.
I have addressed this functionality before in all the ways possible since day one on this forum, 1 year ago.
Before recursion, in an "analogical" way, functions like AFILTER, AUNQSRT, AUNIQUE and later AFUSBYROW, still "analog".
After recursion, with a third party "accumulator" that appends or stacks row by row the results recursively, like AAGREGATE that uses APPEND2V, or without a stacking accumulator, using native IF stacking or appending, like the functions designed using the DIY Array Recursive Function Kit techniques, ARF.
After lambda helper functions, AGGR and here presented drafts of ABYROW , AROW.
Now we can use a built-in "legit" "accumulator" for staking, VSTACK function.
These are the latest refined drafts of custom-made lambda helper spill capable byrow functions, 2 dif approaches, w or w/o VSTACK, more versatile than ever before.
Looking forward to find the right amount of spare time to present and explain them in a separate thread. Till then, they belong here.
They have the same functionality, and both share the same arguments:
*ar: array
fn: function lambda helper argument LAMBDA(x,fn(x))
[er]: error message argument, if the function delivers no results*
*ABYROW(ar,fn,[er])* 
- does not call any of the new functions, uses native IF functionality to stack the rows, single REDUCE function construction and some mandatory cosmetics tricks. (with BYCOL)

```
=LAMBDA(ar,fn,[er],
    LET(
        a, IF(ar = "", "", ar),
        r, REDUCE(
            0,
            SEQUENCE(ROWS(a)),
            LAMBDA(v,i,
                LET(
                    x, INDEX(a, i, ),
                    y, IF(
                        COLUMNS(x) = 1,
                        fn(INDEX(a, i, 1)),
                        fn(FILTER(x, x <> ""))
                    ),
                    c, COLUMNS(y),
                    z, IF(c = 1, INDEX(y, {1,2}), y),
                    IF(SEQUENCE(i) = i, z, v)
                )
            )
        ),
        b, IFERROR(r, ""),
        d, BYCOL(b, LAMBDA(x, AND(x = ""))),
        FILTER(b, NOT(d), IF(ISOMITTED(er), NA(), er))
    )
)
```

*SPILLBYROWS(ar,fn,[er])* 
- calls new, now "native" VSTACK, same structure single REDUCE, not so many cosmetics needed, (used other of the new ones, DROP), therefore even shorter.

```
=LAMBDA(ar,fn,[er],
    LET(
        e, IF(ISOMITTED(er), NA(), er),
        a, IF(ar = "", "", ar),
        r, REDUCE(
            0,
            SEQUENCE(ROWS(a)),
            LAMBDA(v,i,
                LET(
                    x, INDEX(a, i, ),
                    y, IFERROR(
                        IF(
                            COLUMNS(x) = 1,
                            fn(INDEX(a, i, 1)),
                            fn(FILTER(x, x <> ""))
                        ),
                        ""
                    ),
                    VSTACK(v, y)
                )
            )
        ),
        d, DROP(IFNA(r, ""), 1),
        IF(AND(d = ""), e, d)
    )
)
```

Notes: Finally, with the help of Advanced Formula Environment add in, I can present them in a decent way.
            Until Excel comes with a bult-in lambda helper that will do the same I will use them (probably the 2nd one) extensively on any construction possible because they can help a great deal.


----------



## Xlambda (Sep 30, 2021)

*ASCAN**, * *A*rray *SCAN*, *3 in 1 function*, *SCAN* *by row*, *by column*, *by array*. This is my take of tboulden's SCANBYROW/BYCOL.
Uses *only* *new!! SCAN* lambda helper function, no need of byrow,bycol,makearray, or lambda as arguments.

```
=LAMBDA(a,[d],
    LET(y,IF(d=1,TRANSPOSE(a),a),s,SCAN(0,y,LAMBDA(v,a,v+a)),r,ROWS(s),c,COLUMNS(s),sr,SEQUENCE(r)-1,
       x,s-IF(d,IFERROR(INDEX(INDEX(s,,c),sr)*sr^0,0),0),
       IF(d=1,TRANSPOSE(x),x)
   )
)
```
LAMBDA 1.1.3.xlsxABCDEFGHIJKLMNOPQRST1d,omitted (by array)d,-1 (by rows)d,1 (by clms)2sample 1a=ASCAN(A3:D6)=ASCAN(A3:D6,-1)=ASCAN(A3:D6,1)312341361013610123445678152128365111826681012591011124555667891930421518212461314151691105120136132742582832364078d,omitted (by array)d,-1 (by rows)d,1 (by clms)9sample 2a=ASCAN(A10#)=ASCAN(A10#,-1)=ASCAN(A10#,1)1012341361013610123411567815212836511182668101212910111245556678919304215182124131314151691105120136132742582832364014171819201531711902101735547445505560152122232423125327630021436690667278841617Note: Did not set an extra argument for any kind of "initial value", since the only thing it does is too simple,18adds a constant value to the final outcome , like in 10+ASCAN(a)19ASCAN postCell FormulasRangeFormulaF2,P9,K9,F9,P2,K2F2=FORMULATEXT(F3)F3:I6F3=ASCAN(A3:D6)K3:N6K3=ASCAN(A3:D6,-1)P3:S6P3=ASCAN(A3:D6,1)A10:D15A10=SEQUENCE(6,4)F10:I15F10=ASCAN(A10#)K10:N15K10=ASCAN(A10#,-1)P10:S15P10=ASCAN(A10#,1)Dynamic array formulas.


----------



## Xlambda (Apr 7, 2022)

*Inspired by latest Mr.Excel YT* (7-Apr-2022): Find Next True And The Matching Records Above And Below - 2480
It's not about reduce or scan techniques, is about defining an "inside" lambda that can be called by an argument value of main function.
Challenge description:
*"Michele has 46000 rows of data. Each ID has several records in a row. Many records are marked as "Changed". When there is a change, Michele needs to check the record marked, plus the matching IDs just above and below. In this video, I try Find All and 2 Advanced Filters. I bet you have something better."*
A lambda that solves the challenge:
*IDIXFLT(ar,[n]*  ID Index Filter function, can handle unsorted array, or TRUE dups for same ID's
ar: array
[n]: integer
- if n omitted or n=0, function returns total nr. of matches and the list of all matched ID's
- if 1<=n<=t (total nr. of unique matches), function returns as 1st clm, an array of rows indexes stacked horizontally to records extraction (all fields) of respective "n" value
- if n>t => n=t
- if n<0 => n=abs( n )

```
=LAMBDA(ar, [n],
    LET(
        a, IF(ar = "", "", ar),
        id, INDEX(a, , 2),
        tc, INDEX(a, , 4),
        ft, IF(tc = TRUE, 1, 0),
        f, SORT(UNIQUE(FILTER(id, ft))),
        t, ROWS(f),
        s, SEQUENCE(ROWS(a)),
        z, LAMBDA(k,
            LET(
                x, INDEX(f, k),
                y, id = x,
                SORT(HSTACK(FILTER(s, y), FILTER(a, y)))
            )
        ),
        IF(n, z(MIN(ABS(n), t)), IFNA(HSTACK(t, f), ""))
    )
)
```
part of defining the lambda z(k), variable "z" : 
*z,LAMBDA(k,LET(x,INDEX(f,k),y,id=x,SORT(HSTACK(FILTER(s,y),FILTER(a,y)))))*
part of calling z( n ), where "n" is one of main's function argument :
*z(MIN(ABS( n ),t))*
Mr.Excel 2480 challenge 2nd draft.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAAB1sample array, sortedsample array, unsorted2no TRUE dupshas TRUE dups for same IDindex arrayomitted or 0n,1n,33=SEQUENCE(ROWS(F4:I35))=IDIXFLT(F4:I35)=IDIXFLT(F4:I35,1)=IDIXFLT(F4:I35,3)41761Q70913521763Q709135215E373692031E3736171122124L6272643TRUE51762Q70915341764Q7091534TRUE2H7453102032E3736388TRUE132125L627256561763Q70915651765Q70915653L6272112033E373619671764Q7091631TRUE1922X39146314Q7091↑↑↑n,481765Q70915021923X3914502TRUE5X3914first clm, all rows indexes of first id match (for n=1)=IDIXFLT(F4:I35,4)91766Q70912191924X39142196↑↑11763Q7091352101917X39142131967H7453213TRUE7top left cell, total nr. matches and their values21764Q7091534TRUE111918X39142481968H74532488n,231765Q7091565121919X39141712031E37361719=IDIXFLT(F4:I35,2)141766Q7091365131920X39143882032E3736388TRUE1071967H7453213TRUE271761Q7091291141921X39141962033E37361961181968H7453248281762Q7091457151922X39146432124L6272643TRUE12171972H7453721161923X3914565TRUE2125L627256513181973H7453444n,5171924X39143651766Q709136514191974H7453491=IDIXFLT(F4:I35,5)181967H7453708TRUE1917X391470815201975H745324441922X3914631191968H74536051918X391460516241969H745362351923X3914502TRUE201969H74537211972H745372117TRUE251970H745361461924X3914219211970H74534441973H745344418duplicates TRUE261971H7453239151917X3914708221971H74534911974H745349119for same ID "X3914"291976H7453351161918X3914605231972H74532441975H745324420301977H7453305211919X3914568241973H74535681919X391456821311978H7453683221920X3914331TRUE251974H74533311920X3914331TRUE22321979H7453395231921X3914236261975H74532361921X391423623271976H74536231969H745362324calling the function for sorted array A4:D35281977H74536141970H745361425n,omittedn,5n,-100 => n=abs(n)=100 > 5 => n=5291978H74532391971H745323926=IDIXFLT(A4:D35)=IDIXFLT(A4:D35,5)=IDIXFLT(F4:I35,-100)301979H74532911761Q7091291275E373671917X391421341922X3914631312031E37364571762Q709145728H745381918X391424851923X3914502TRUE322032E3736351TRUE1976H745335129L627291919X391417161924X3914219332033E37363051977H745330530Q7091101920X3914388151917X3914708342124L6272683TRUE1978H745368331X3914111921X3914196161918X3914605352125L62723951979H745339532121922X3914643211919X391456836131923X3914565TRUE221920X3914331TRUE37141924X3914365231921X39142363839As we see, both arrays, sorted and unsorted return same values, but of course,40as 1st clm, the array of row indexes, is different41Also, notice that sorted array has no TRUE dups.42last draftCell FormulasRangeFormulaK3,N3,Q3,W29,N29,Q29,W17,Q12,W8,W3K3=FORMULATEXT(K4)K4:K35K4=SEQUENCE(ROWS(F4:I35))N4:O8N4=IDIXFLT(F4:I35)Q4:U6Q4=IDIXFLT(F4:I35,1)W4:AA5W4=IDIXFLT(F4:I35,3)W9:AA14W9=IDIXFLT(F4:I35,4)Q13:U25Q13=IDIXFLT(F4:I35,2)W18:AA25W18=IDIXFLT(F4:I35,5)N30:O34N30=IDIXFLT(A4:D35)Q30:U37Q30=IDIXFLT(A4:D35,5)W30:AA37W30=IDIXFLT(F4:I35,-100)Dynamic array formulas.


----------



## Xlambda (Apr 7, 2022)

Mr.Excel 2480 challenge 2nd draft.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZ1samplesimulation for 100000 rows, dealing with whole array comparison and no iterations involved, function speed is lighting fast.21761Q7091352=SEQUENCE(100000)check results array nr. rows31762Q7091534↓↓↓↓=MOD(F4#-1,ROWS(A2:D33))+1=INDEX(IF(A2:D33="","",A2:D33),G4#,SEQUENCE(,4))=IDIXFLT(I4#)=IDIXFLT(I4#,5)=ROWS(R4#)41763Q7091565111761Q70913525E373671917X39142132500051764Q7091631TRUE221762Q7091534H745381918X391424861765Q7091502331763Q7091565L627291919X391417171766Q7091219441764Q7091631TRUEQ7091101920X391438881917X3914213551765Q7091502X3914111921X391419691918X3914248661766Q7091219121922X3914643101919X3914171771917X3914213check rows131923X3914565TRUE111920X3914388881918X3914248=ROWS(I4#)141924X3914365121921X3914196991919X3914171100000391917X3914213131922X391464310101920X3914388401918X3914248141923X3914565TRUE11111921X3914196411919X3914171151924X391436512121922X3914643421920X3914388161967H7453708TRUE13131923X3914565TRUE431921X3914196171968H745360514141924X3914365441922X3914643181969H745372115151967H7453708TRUE451923X3914565TRUE191970H745344416161968H7453605461924X3914365201971H745349117171969H7453721711917X3914213211972H745324418181970H7453444721918X3914248221973H745356819191971H7453491731919X3914171231974H745333120201972H7453244741920X3914388241975H745323621211973H7453568751921X3914196251976H745362322221974H7453331761922X3914643261977H745361423231975H7453236771923X3914565TRUE271978H745323924241976H7453623781924X3914365281979H745329125251977H74536141031917X3914213292031E373645726261978H74532391041918X3914248302032E3736351TRUE27271979H74532911051919X3914171312033E373630528282031E37364571061920X3914388322124L6272683TRUE29292032E3736351TRUE1071921X3914196332125L627239530302033E37363051081922X39146433431312124L6272683TRUE1091923X3914565TRUE3532322125L62723951101924X3914365363311761Q70913521351917X3914213373421762Q70915341361918X3914248383531763Q70915651371919X3914171393641764Q7091631TRUE1381920X3914388403751765Q70915021391921X3914196413861766Q70912191401922X3914643423971917X39142131411923X3914565TRUE434081918X39142481421924X3914365444191919X39141711671917X39142134542101920X39143881681918X39142484643111921X39141961691919X39141714744121922X39146431701920X39143884845131923X3914565TRUE1711921X39141964946141924X39143651721922X39146435047151967H7453708TRUE1731923X3914565TRUESheet1Cell FormulasRangeFormulaF2F2=FORMULATEXT(F4)G3,I3,O3,N11,X3,R3G3=FORMULATEXT(G4)F4:F100003F4=SEQUENCE(100000)G4:G100003G4=MOD(F4#-1,ROWS(A2:D33))+1I4:L100003I4=INDEX(IF(A2:D33="","",A2:D33),G4#,SEQUENCE(,4))O4:P8O4=IDIXFLT(I4#)R4:V25003R4=IDIXFLT(I4#,5)X4X4=ROWS(R4#)N12N12=ROWS(I4#)Dynamic array formulas.


----------



## Xlambda (Apr 14, 2022)

Inspired by latest Mr.Excel YT (14-Apr-2022): Count Number Of Weekdays Per Month using Excel - 2481
*Task: "Ronak asks: How can I use Excel to find out how many weekdays per month for the whole year?"*
2 simple lambdas to solve it using MAKEARRAY , main core function used to reproduce PT functionality in general.
*DNY( y )* Day Names/Year y: year 4 digits

```
=LAMBDA(y,
    LET(
        f, DATE(y, 1, 1),
        l, DATE(y, 12, 31),
        s, SEQUENCE(l - f + 1, , f),
        MAKEARRAY(12, 7, LAMBDA(r, c, SUM((c = WEEKDAY(s, 1)) * (r = MONTH(s)))))
    )
)
```
*PTDNY( y )* Pivot Table Day Names/Year, calls DNY( y )

```
=LAMBDA(y,
    LET(
        g, "GT",
        a, DNY(y),
        VSTACK(
            HSTACK(y, TEXT(SEQUENCE(, 7), "ddd"), g),
            HSTACK(TEXT(SEQUENCE(12, , , 31), "mmm"), a, BYROW(a, LAMBDA(x, SUM(x)))),
            HSTACK(g, BYCOL(a, LAMBDA(x, SUM(x))), SUM(a))
        )
    )
)
```
Book1ABCDEFGHIJKLMNOPQRS12inner array calculationentire PT3y,20214y,2021=PTDNY(2021)5=DNY(2021)2021SunMonTueWedThuFriSatGT65444455Jan54444553174444444Feb44444442884555444Mar45554443194444554Apr444455430105544445May554444531114455444Jun445544430124444555Jul444455531135554444Aug555444431144445544Sep444554430155444455Oct544445531164554444Nov455444430174445554Dec44455543118GT525252525253523651920y,2020 (2020 was a leap year)21y,2020=PTDNY(2020)22=DNY(2020)2020SunMonTueWedThuFriSatGT234445554Jan444555431244444445Feb444444529255554444Mar555444431264445544Apr444554430275444455May544445531284554444Jun455444430294445554Jul444555431305544445Aug554444531314455444Sep445544430324444555Oct444455531335544444Nov554444430344455544Dec44555443135GT525252535352523663637y,202238=PTDNY(2022)392022SunMonTueWedThuFriSatGT40Jan55444453141Feb44444442842Mar44555443143Apr44444553044May55544443145Jun44455443046Jul54444553147Aug45554443148Sep44445543049Oct55444453150Nov44554443051Dec44445553152GT5252525252525336553DNYCell FormulasRangeFormulaJ4,J38,B22,J21,B5J4=FORMULATEXT(J5)J5:R18J5=PTDNY(2021)B6:H17B6=DNY(2021)J22:R35J22=PTDNY(2020)B23:H34B23=DNY(2020)J39:R52J39=PTDNY(2022)Dynamic array formulas.


----------



## Xlambda (Apr 14, 2022)

If we need to change 1st weekday from Sun to Mon, 2 minor changes (in bold)
*DNY( y )*:   .....SUM((c=WEEKDAY(s,*2*))*(r=MONTH(s)))))))

```
=LAMBDA(y,
    LET(
        f, DATE(y, 1, 1),
        l, DATE(y, 12, 31),
        s, SEQUENCE(l - f + 1, , f),
        MAKEARRAY(12, 7, LAMBDA(r, c, SUM((c = WEEKDAY(s, 2)) * (r = MONTH(s)))))
    )
)
```
*PTDNY( y )*:    ....TEXT(SEQUENCE(,7)*+1*,"ddd").....

```
=LAMBDA(y,
    LET(
        g, "GT",
        a, DNY(y),
        VSTACK(
            HSTACK(y, TEXT(SEQUENCE(, 7) + 1, "ddd"), g),
            HSTACK(TEXT(SEQUENCE(12, , , 31), "mmm"), a, BYROW(a, LAMBDA(x, SUM(x)))),
            HSTACK(g, BYCOL(a, LAMBDA(x, SUM(x))), SUM(a))
        )
    )
)
```
Book1ABCDEFGHIJKLMNOPQRSTUV12weekdays or day names sequenceinner array calculationentire PT31st day Suny,20214=TEXT(SEQUENCE(7),"ddd")y,2021=PTDNY(2021)5Sun=DNY(2021)2021MonTueWedThuFriSatSunGT6Mon4444555Jan4444555317Tue4444444Feb4444444288Wed5554444Mar5554444319Thu4445544Apr44455443010Fri5444455May54444553111Sat4554444Jun455444430124445554Jul444555431131st day Mon5544445Aug55444453114=TEXT(SEQUENCE(7)+1,"ddd")4455444Sep44554443015Mon4444555Oct44445553116Tue5544444Nov55444443017Wed4455544Dec44555443118ThuGT5252525253525236519Fri20Saty,2020 (2020 was a leap year)21Suny,2020=PTDNY(2020)22=DNY(2020)2020MonTueWedThuFriSatSunGT23month sequence4455544Jan44555443124=TEXT(SEQUENCE(12,,,31),"mmm")4444454Feb44444542925Jan5544445Mar55444453126Feb4455444Apr44554443027Mar4444555May44445553128Apr5544444Jun55444443029May4455544Jul44555443130Jun5444455Aug54444553131Jul4554444Sep45544443032Aug4445554Oct44455543133Sep5444445Nov54444453034Oct4555444Dec45554443135NovGT5252535352525236636Dec37y,202238=PTDNY(2022)392022MonTueWedThuFriSatSunGT40Jan54444553141Feb44444442842Mar45554443143Apr44445543044May55444453145Jun44554443046Jul44445553147Aug55544443148Sep44455443049Oct54444553150Nov45544443051Dec44455543152GT5252525252535236553DNY 2Cell FormulasRangeFormulaB4,M38,B24,E22,M21,B14,E5,M4B4=FORMULATEXT(B5)B5:B11B5=TEXT(SEQUENCE(7),"ddd")M5:U18M5=PTDNY(2021)E6:K17E6=DNY(2021)B15:B21B15=TEXT(SEQUENCE(7)+1,"ddd")M22:U35M22=PTDNY(2020)E23:K34E23=DNY(2020)B25:B36B25=TEXT(SEQUENCE(12,,,31),"mmm")M39:U52M39=PTDNY(2022)Dynamic array formulas.


----------



## Xlambda (Apr 14, 2022)

On the comments section *Laza Lazarevic* asked a good question:
*"How to exclude the holidays?"*
These are the new functions that take holidays into consideration:
*EHDNY(y,[h])* Exclude Holidays Day Names/Year (y: year ; [h]: holidays array)

```
=LAMBDA(y, [h],
    LET(
        f, DATE(y, 1, 1),
        l, DATE(y, 12, 31),
        d, SEQUENCE(l - f + 1, , f),
        s, IF(ISOMITTED(h), d, FILTER(d, NOT(IFNA(XMATCH(d, h), 0)))),
        MAKEARRAY(12, 7, LAMBDA(r, c, SUM((c = WEEKDAY(s, 2)) * (r = MONTH(s)))))
    )
)
```
*PTEHDNY(y,[h])* (we only must add the extra argument "h" and replace DNY( y ) in the formula with EHDNY(y,h) )

```
=LAMBDA(y, [h],
    LET(
        g, "GT",
        a, EHDNY(y, h),
        VSTACK(
            HSTACK(y, TEXT(SEQUENCE(, 7) + 1, "ddd"), g),
            HSTACK(TEXT(SEQUENCE(12, , , 31), "mmm"), a, BYROW(a, LAMBDA(x, SUM(x)))),
            HSTACK(g, BYCOL(a, LAMBDA(x, SUM(x))), SUM(a))
        )
    )
)
```
Book1ABCDEFGHIJ1Exclude holidays.EHDNY(y,[h]) function concept.23sample date arrayholydays array4=SEQUENCE(31,,"1-1-22")=IFNA(XMATCH(B5#,D5:D12),0)=FILTER(B5#,NOT(F5#))501-01-2214-01-22001-01-22602-01-2215-01-22002-01-22703-01-2216-01-22003-01-22804-01-2217-01-22004-01-22905-01-2218-01-22005-01-221006-01-2219-01-22006-01-221107-01-2229-01-22007-01-221208-01-2231-01-22008-01-221309-01-22009-01-221410-01-22010-01-221511-01-22011-01-221612-01-22012-01-221713-01-22013-01-221814-01-22120-01-221915-01-22221-01-222016-01-22322-01-222117-01-22423-01-222218-01-22524-01-222319-01-22625-01-222420-01-22026-01-222521-01-22027-01-222622-01-22028-01-222723-01-22030-01-222824-01-2202925-01-2203026-01-2203127-01-2203228-01-2203329-01-2273430-01-2203531-01-22836DNY 4Cell FormulasRangeFormulaB4,F4,H4B4=FORMULATEXT(B5)B5:B35B5=SEQUENCE(31,,"1-1-22")F5:F35F5=IFNA(XMATCH(B5#,D5:D12),0)H5:H27H5=FILTER(B5#,NOT(F5#))Dynamic array formulas.


----------



## Xlambda (Apr 14, 2022)

Book1ABCDEFGHIJKLMNOPQRSTUVWXYZAAAB12021-2022y,2021,h,omittedy,2021,h,B3:B402holydays array=PTEHDNY(2021)=PTEHDNY(2021,B3:B40)check results301-01-212021MonTueWedThuFriSatSunGT2021MonTueWedThuFriSatSunGTnr. holydays 2021402-01-21Jan444455531Jan444444428=ROWS(FILTER(B3:B40,YEAR(B3:B40)=2021))503-01-21Feb444444428Feb44444442819615-08-21Mar555444431Mar555444431=L16-V16716-08-21Apr444554430Apr44455443019817-08-21May544445531May544445531918-08-21Jun455444430Jun4554444301019-08-21Jul444555431Jul4445554311120-08-21Aug554444531Aug4433333231221-08-21Sep445544430Sep4455444301322-08-21Oct444455531Oct4444555311424-12-21Nov554444430Nov5544444301525-12-21Dec445554431Dec3344333231626-12-21GT52525252535252365GT505050504949483461727-12-211828-12-21y,2022,h,omittedy,2022,h,B3:B401929-12-21=PTEHDNY(2022)=PTEHDNY(2022,B3:B40)check results2030-12-212022MonTueWedThuFriSatSunGT2022MonTueWedThuFriSatSunGTnr. holydays 20222131-12-21Jan544445531Jan444444428=ROWS(FILTER(B3:B40,YEAR(B3:B40)=2022))2201-01-22Feb444444428Feb444444428192302-01-22Mar455544431Mar455544431=L33-V332403-01-22Apr444455430Apr444455430192515-08-22May554444531May5544445312616-08-22Jun445544430Jun4455444302717-08-22Jul444455531Jul4444555312818-08-22Aug555444431Aug3443333232919-08-22Sep444554430Sep4445544303020-08-22Oct544445531Oct5444455313121-08-22Nov455444430Nov4554444303222-08-22Dec444555431Dec3334433233324-12-22GT52525252525352365GT485050505049493463425-12-22DNY 5Cell FormulasRangeFormulaD2,X23,X21,N19,D19,X6,X4,N2D2=FORMULATEXT(D3)D3:L16D3=PTEHDNY(2021)N3:V16N3=PTEHDNY(2021,B3:B40)X5X5=ROWS(FILTER(B3:B40,YEAR(B3:B40)=2021))X7,X24X7=L16-V16D20:L33D20=PTEHDNY(2022)N20:V33N20=PTEHDNY(2022,B3:B40)X22X22=ROWS(FILTER(B3:B40,YEAR(B3:B40)=2022))Dynamic array formulas.


----------



## Xlambda (Apr 14, 2022)

*What if we have an array of dates (more years) and we want to extract the distribution of weekdays for each month (all years together) ?
Concept:*
Book1ABCDEFGHIJKLMNOP1random dates btw 1-1-21,31-12-22 300 rowswkdy distribution entire array2=RANDARRAY(300,,"1-1-21","31-12-22",1)=HSTACK(TEXT(SEQUENCE(7),"ddd"),DROP(FREQUENCY(D5#,SEQUENCE(7)),-1))3↓↓↓wkdys entire array↓↓↓wkdy distribution only Jan4↓↓↓=WEEKDAY(B5#)↓↓↓=FILTER(B5#,MONTH(B5#)=1)511-09-221↓↓↓↓↓↓=DROP(FREQUENCY(WEEKDAY(I6#),SEQUENCE(7)),-1)622-10-227Sun4321-01-227723-07-227Mon3831-01-211810-08-224Tue4427-01-212922-12-214Wed4923-01-2131018-07-222Thu3826-01-2151111-04-211Fri3712-01-2231220-08-216Sat5121-01-2271313-06-21104-01-211411-09-22103-01-211512-11-21614-01-221613-09-21214-01-211703-08-22409-01-211820-11-21716-01-211918-04-22220-01-222008-04-22613-01-222104-08-21414-01-212211-03-21502-01-222320-03-21723-01-222420-08-21601-01-222505-12-21126-01-222609-08-22325-01-222721-01-22627-01-222805-06-21722-01-222924-07-21708-01-223017-06-215down to 300 rows16-01-223103-03-214↓↓↓↓↓↓↓↓↓15-01-223223-10-21709-01-223322-03-22310-01-213409-04-227Sheet7Cell FormulasRangeFormulaB2B2=FORMULATEXT(B5)F2F2=FORMULATEXT(F6)D4,K5D4=FORMULATEXT(D5)I4I4=FORMULATEXT(I6)B5:B304B5=RANDARRAY(300,,"1-1-21","31-12-22",1)D5:D304D5=WEEKDAY(B5#)F6:G12F6=HSTACK(TEXT(SEQUENCE(7),"ddd"),DROP(FREQUENCY(D5#,SEQUENCE(7)),-1))I6:I33I6=FILTER(B5#,MONTH(B5#)=1)K6:K12K6=DROP(FREQUENCY(WEEKDAY(I6#),SEQUENCE(7)),-1)Dynamic array formulas.


----------



## Xlambda (Apr 14, 2022)

We have seen how we can calculate the distribution of weekdays for 1 month. 
So we can use the *custom-made lambda helper function* that can spill byrow, *SPILLBYROW* (this thread post#50), a function that can spill rows even if source array is 1D, this format: 
*SPILLBYROW(SEQUENCE(12),LAMBDA(i,fn(i)))*
Here are 2 methods, *2 simple lambda formulas*, 2 different ways to solve the inner array (distribution values w/o the rest of other PT elements)
Book1ABCDEFGHIJKLMNOPQR1random dates btw 1-1-21,31-12-22 300 rows2=RANDARRAY(300,,"1-1-21","31-12-22",1)3↓↓↓4↓↓↓SunMonTueWedThuFriSat507-09-221st method602-11-22=SPILLBYROW(SEQUENCE(12),LAMBDA(i,TOROW(DROP(FREQUENCY(WEEKDAY(FILTER(B5#,MONTH(B5#)=i)),SEQUENCE(7)),-1))))708-02-216335185Jan829-07-226353223Feb906-11-212244121Mar1026-06-213335413Apr1131-05-216545231May1217-05-214144634Jun1320-06-225453653Jul1426-07-212454332Aug1514-10-222366455Sep1602-06-212381344Oct1726-11-212435435Nov1802-01-215351221Dec1931-08-212018-10-212nd method2104-07-21=SPILLBYROW(SEQUENCE(12),LAMBDA(i,BYCOL((MONTH(B5#)=i)*(WEEKDAY(B5#)=SEQUENCE(,7)),LAMBDA(x,SUM(x)))))2208-05-226335185Jan2323-03-226353223Feb2412-05-222244121Mar2512-12-223335413Apr2626-09-216545231May2708-09-224144634Jun2807-10-215453653Jul2907-01-222454332Aug3023-07-212366455Sep3129-07-212381344Oct3224-12-222435435Nov3301-08-225351221Dec3402-01-223527-09-223618-02-21checking 1st method3703-02-21=BYCOL(D7#,LAMBDA(x,SUM(x)))3829-06-21453855463841373918-05-224014-07-21checking 2nd method4106-01-21=BYCOL(D22#,LAMBDA(x,SUM(x)))4206-02-21453855463841374313-07-224429-03-21cheking wkdy distribution entire array4501-10-22=HSTACK(TEXT(SEQUENCE(7),"ddd"),DROP(FREQUENCY(WEEKDAY(B5#),SEQUENCE(7)),-1))4622-09-21Sun454708-01-22Mon384830-11-21Tue554917-01-21Wed465003-11-21Thu385106-12-22Fri415222-05-22Sat375302-01-225405-10-215502-12-22down to 300 rows5611-06-22↓↓↓↓↓↓↓↓↓5731-10-22Sheet7Cell FormulasRangeFormulaB2B2=FORMULATEXT(B5)D4:J4D4=TEXT(SEQUENCE(,7),"ddd")B5:B304B5=RANDARRAY(300,,"1-1-21","31-12-22",1)D6,D45,D41,D37,D21D6=FORMULATEXT(D7)D7:J18D7=SPILLBYROW(SEQUENCE(12),LAMBDA(i,TOROW(DROP(FREQUENCY(WEEKDAY(FILTER(B5#,MONTH(B5#)=i)),SEQUENCE(7)),-1))))D22:J33D22=SPILLBYROW(SEQUENCE(12),LAMBDA(i,BYCOL((MONTH(B5#)=i)*(WEEKDAY(B5#)=SEQUENCE(,7)),LAMBDA(x,SUM(x)))))D38:J38D38=BYCOL(D7#,LAMBDA(x,SUM(x)))D42:J42D42=BYCOL(D22#,LAMBDA(x,SUM(x)))D46:E52D46=HSTACK(TEXT(SEQUENCE(7),"ddd"),DROP(FREQUENCY(WEEKDAY(B5#),SEQUENCE(7)),-1))Dynamic array formulas.


----------



## Xlambda (Apr 14, 2022)

*Task: Using a lambda formula (SPILLBYROW versatility) extract all weekdays dates from an array of dates.*
Nr weekdays 2481.xlsxABCDEFGHIJKLMNO1random dates btw 1-1-21,31-12-22 50 rows2=RANDARRAY(50,,"1-1-21","31-12-22",1)3↓↓↓4↓↓↓=LET(a,B5#,s,SEQUENCE(7),HSTACK(TEXT(s,"ddd"),SPILLBYROW(s,LAMBDA(i,TOROW(SORT(FILTER(a,WEEKDAY(a)=i)))))))520-03-21Sun13-06-2101-08-2123-01-2231-07-2225-09-2225-12-22623-04-21Mon10-05-2104-04-2211-07-2208-08-2212-09-22727-07-21Tue27-07-2107-12-2130-08-2227-09-22807-12-21Wed13-01-2105-05-2107-07-2113-10-2102-03-2204-05-2224-08-2214-09-2214-09-2230-11-22927-09-22Thu18-03-2122-04-2106-05-2125-11-2102-12-2113-01-2210-02-2209-06-2229-12-221001-01-22Fri19-03-2123-04-2101-10-2129-04-2220-05-2210-06-2229-07-221120-05-22Sat02-01-2120-03-2104-09-2118-09-2125-12-2101-01-2222-01-2222-10-2205-11-221205-11-221319-03-21check1430-08-22=IFERROR(WEEKDAY(DROP(D5#,,1)),"")1531-07-221111111609-06-22222221725-12-2133331808-08-2244444444441901-10-215555555552022-01-2266666662125-12-227777777772229-12-222322-10-222412-09-222513-01-212623-01-222704-04-222829-07-222910-02-223010-05-213124-08-223213-06-213304-09-213410-06-223511-07-223614-09-223725-11-213818-09-213929-04-224001-08-214102-03-224214-09-224313-10-214405-05-214504-05-224602-12-214707-07-214806-05-214922-04-215025-09-225102-01-215213-01-225318-03-215430-11-2255DNY 7Cell FormulasRangeFormulaB2B2=FORMULATEXT(B5)D4,E14D4=FORMULATEXT(D5)B5:B54B5=RANDARRAY(50,,"1-1-21","31-12-22",1)D5:N11D5=LET(a,B5#,s,SEQUENCE(7),HSTACK(TEXT(s,"ddd"),SPILLBYROW(s,LAMBDA(i,TOROW(SORT(FILTER(a,WEEKDAY(a)=i)))))))E15:N21E15=IFERROR(WEEKDAY(DROP(D5#,,1)),"")Dynamic array formulas.


----------



## Xlambda (Apr 14, 2022)

Forgot to add UNIQUE, in case dates array has dups.
*.....SPILLBYROW(s,LAMBDA(i,TOROW(SORT(UNIQUE(FILTER(a,WEEKDAY(a)=i))))))))*
Nr weekdays 2481.xlsxABCDEFGHIJKLMNOPQ1random dates btw 1-1-21,31-12-22 50 rows2=RANDARRAY(50,,"1-1-21","31-12-22",1)3↓↓↓4↓↓↓=LET(a,B5#,s,SEQUENCE(7),HSTACK(TEXT(s,"ddd"),SPILLBYROW(s,LAMBDA(i,TOROW(SORT(UNIQUE(FILTER(a,WEEKDAY(a)=i))))))))518-04-22Sun11-04-2124-10-2109-01-2213-03-2226-06-2204-12-22611-04-21Mon05-07-2119-07-2129-11-2127-12-2103-01-2211-04-2218-04-2219-09-22707-01-21Tue26-01-2106-07-2119-10-2107-12-2108-02-2218-10-2229-11-22801-09-22Wed10-02-2110-03-2102-06-2122-09-2108-12-2102-02-2216-02-2209-03-2218-05-2203-08-22903-06-21Thu07-01-2125-02-2103-06-2111-11-2103-02-2224-02-2207-04-2223-06-2201-09-2229-09-221003-09-22Fri15-01-2121-01-2201-07-221103-02-22Sat04-06-2203-09-2222-10-2210-12-2231-12-221229-11-211321-01-22check1408-12-21=IFERROR(WEEKDAY(DROP(D5#,,1)),"")1526-06-221111111610-12-22222222221729-11-2233333331803-08-2244444444441919-09-2255555555552008-02-226662118-05-22777772213-03-222304-06-222402-06-212527-12-212605-07-212723-06-222825-02-212922-09-213024-10-213106-07-213201-07-223319-07-213418-10-223509-01-223610-02-213729-09-223826-01-213909-03-224025-02-214115-01-214203-01-224310-03-214416-02-224522-10-224604-12-224711-04-224824-02-224907-12-215011-11-215102-02-225207-04-225331-12-225419-10-2155DNY 7Cell FormulasRangeFormulaB2B2=FORMULATEXT(B5)D4,E14D4=FORMULATEXT(D5)B5:B54B5=RANDARRAY(50,,"1-1-21","31-12-22",1)D5:N11D5=LET(a,B5#,s,SEQUENCE(7),HSTACK(TEXT(s,"ddd"),SPILLBYROW(s,LAMBDA(i,TOROW(SORT(UNIQUE(FILTER(a,WEEKDAY(a)=i))))))))E15:N21E15=IFERROR(WEEKDAY(DROP(D5#,,1)),"")Dynamic array formulas.


----------



## Xlambda (Sep 30, 2021)

*ASCAN**, * *A*rray *SCAN*, *3 in 1 function*, *SCAN* *by row*, *by column*, *by array*. This is my take of tboulden's SCANBYROW/BYCOL.
Uses *only* *new!! SCAN* lambda helper function, no need of byrow,bycol,makearray, or lambda as arguments.

```
=LAMBDA(a,[d],
    LET(y,IF(d=1,TRANSPOSE(a),a),s,SCAN(0,y,LAMBDA(v,a,v+a)),r,ROWS(s),c,COLUMNS(s),sr,SEQUENCE(r)-1,
       x,s-IF(d,IFERROR(INDEX(INDEX(s,,c),sr)*sr^0,0),0),
       IF(d=1,TRANSPOSE(x),x)
   )
)
```
LAMBDA 1.1.3.xlsxABCDEFGHIJKLMNOPQRST1d,omitted (by array)d,-1 (by rows)d,1 (by clms)2sample 1a=ASCAN(A3:D6)=ASCAN(A3:D6,-1)=ASCAN(A3:D6,1)312341361013610123445678152128365111826681012591011124555667891930421518212461314151691105120136132742582832364078d,omitted (by array)d,-1 (by rows)d,1 (by clms)9sample 2a=ASCAN(A10#)=ASCAN(A10#,-1)=ASCAN(A10#,1)1012341361013610123411567815212836511182668101212910111245556678919304215182124131314151691105120136132742582832364014171819201531711902101735547445505560152122232423125327630021436690667278841617Note: Did not set an extra argument for any kind of "initial value", since the only thing it does is too simple,18adds a constant value to the final outcome , like in 10+ASCAN(a)19ASCAN postCell FormulasRangeFormulaF2,P9,K9,F9,P2,K2F2=FORMULATEXT(F3)F3:I6F3=ASCAN(A3:D6)K3:N6K3=ASCAN(A3:D6,-1)P3:S6P3=ASCAN(A3:D6,1)A10:D15A10=SEQUENCE(6,4)F10:I15F10=ASCAN(A10#)K10:N15K10=ASCAN(A10#,-1)P10:S15P10=ASCAN(A10#,1)Dynamic array formulas.


----------



## Xlambda (May 8, 2022)

Example, 2 ways of using expandable ranges in single cell formulas, inspired by Leila's latest YT Excel LOOKUP Function Only PROS Use (simple to complex examples)
Cell by cell solution:
Lookup-Function-XelPlus.xlsxABCDE1BOM LevelPart NumberImmediate Parent PartXLOOKUP Version2010420-1001--3110112-100110420-100110420-10014210409-100110112-100110112-10015210800-100110112-100110112-10016210800-100210112-100110112-10017110410-100110420-100110420-10018210410-100310410-100110410-10019310107-100110410-100310410-100310310800-100310410-100310410-100311310800-100410410-100310410-100312310108-100010410-100310410-100313210410-100210410-100110410-100114310107-100110410-100210410-100215310800-100310410-100210410-100216310800-100410410-100210410-100217310108-100010410-100210410-1002181920Lookup AdvancedCell FormulasRangeFormulaC2:C17C2=IFERROR(LOOKUP(2,1/(A2-1=$A$2:A2),$B$2:B2),"-")D2:D17D2=XLOOKUP(A2-1, $A$2:A2, $B$2:B2, "-", 0, -1)Cells with Conditional FormattingCellConditionCell FormatStop If TrueB2:F17Cell Value=$B$13textNOB2:F17Cell Value=$B$8textNOB2:F17Cell Value=$B$7textNOB2:F17Cell Value=$B$3textNOB2:F17Cell Value=$B$2textNO


----------



## Xlambda (May 8, 2022)

Single cell formula C2, 1st method using SCAN

```
=LET(b,A2:A17,p,B2:B17,SCAN(0,SEQUENCE(ROWS(b)),LAMBDA(v,i,LET(s,SEQUENCE(i),XLOOKUP(INDEX(b,i)-1,INDEX(b,s),INDEX(p,s),"-",,-1)))))
```
Lookup-Function-XelPlus.xlsxABCD1BOM LevelPart NumberImmediate Parent Part2010420-1001-3110112-100110420-10014210409-100110112-10015210800-100110112-10016210800-100210112-10017110410-100110420-10018210410-100310410-10019310107-100110410-100310310800-100310410-100311310800-100410410-100312310108-100010410-100313210410-100210410-100114310107-100110410-100215310800-100310410-100216310800-100410410-100217310108-100010410-10021819Lookup Advanced (2)Cell FormulasRangeFormulaC2:C17C2=LET(b,A2:A17,p,B2:B17,SCAN(0,SEQUENCE(ROWS(b)),LAMBDA(v,i,LET(s,SEQUENCE(i),XLOOKUP(INDEX(b,i)-1,INDEX(b,s),INDEX(p,s),"-",,-1)))))Dynamic array formulas.Cells with Conditional FormattingCellConditionCell FormatStop If TrueB3:C17,B2,G2Cell Value=$B$13textNOB3:C17,B2,G2Cell Value=$B$8textNOB3:C17,B2,G2Cell Value=$B$7textNOB3:C17,B2,G2Cell Value=$B$3textNOB3:C17,B2,G2Cell Value=$B$2textNO


----------



## Xlambda (May 8, 2022)

Single cell formula C2, 2nd method, using MAP with 2 arguments.
Manual expandable ranges are ok if the array lives in a spreadsheet. If the array lives or is calculated inside a function or formula, we need single cell formulas.

```
=LET(b,A2:A17,p,B2:B17,MAP(b,SEQUENCE(ROWS(b)),LAMBDA(x,i,LET(s,SEQUENCE(i),XLOOKUP(x-1,INDEX(b,s),INDEX(p,s),"-",,-1)))))
```
Lookup-Function-XelPlus.xlsxABCD1BOM LevelPart NumberImmediate Parent Part2010420-1001-3110112-100110420-10014210409-100110112-10015210800-100110112-10016210800-100210112-10017110410-100110420-10018210410-100310410-10019310107-100110410-100310310800-100310410-100311310800-100410410-100312310108-100010410-100313210410-100210410-100114310107-100110410-100215310800-100310410-100216310800-100410410-100217310108-100010410-10021819Lookup Advanced (2)Cell FormulasRangeFormulaC2:C17C2=LET(b,A2:A17,p,B2:B17,MAP(b,SEQUENCE(ROWS(b)),LAMBDA(x,i,LET(s,SEQUENCE(i),XLOOKUP(x-1,INDEX(b,s),INDEX(p,s),"-",,-1)))))Dynamic array formulas.Cells with Conditional FormattingCellConditionCell FormatStop If TrueB2:C17Cell Value=$B$13textNOB2:C17Cell Value=$B$8textNOB2:C17Cell Value=$B$7textNOB2:C17Cell Value=$B$3textNOB2:C17Cell Value=$B$2textNO


----------



## Xlambda (Jun 12, 2022)

This is my take of solving Jon's Excel Campus YT challenge: Excel Challenge: Baby Shower Guessing Game
followed by the next YT with the solutions to the Guessing Game challenge: Excel Formula Training: IF, ABS, RANK, ROUND, LET & more
I have raised the stakes of the challenge and wrote a function that can solve not only the particular initial scenario, but any array of any guessing game that complies with the general set of rules.
The function is named *GUESS* and consists in a main REDUCE formula built around 2 other lambdas (EN extract numbers and PC points calculation) that are not defined or called separately, are embedded in the main function.
Can deal with any data types, numeric integers or with decimals, dates or time format, any text (any units, wrote correctly or even misspelled)
*GUESS(a,v,p,s,b,[ti])*
*a*: estimations array
*v*: values actual stats array
*p*: penalties array
*s*: starting points (50 in our case)
*b*: bonus points (100)
*[ti]*: column index of time column only if is in numeric format. (if there is a time column in text format, ti can be omitted)

```
=LAMBDA(a,v,p,s,b,[ti],
    LET(
        EN, LAMBDA(c,
            LET(
                b, IFERROR(--MID(c, SEQUENCE(, MAX(LEN(c))), 1), " "),
                d, TRIM(BYROW(b, LAMBDA(x, TEXTJOIN(, , x)))),
                --SUBSTITUTE(d, " ", ".")
            )
        ),
        PC, LAMBDA(c,v,p,
            LET(
                x, ABS(INT(c) - INT(v)),
                y, ABS(MOD(c, 1) - MOD(v, 1)),
                IF((x = 0) + (y = 0) = 2, b, s - x * p)
            )
        ),
        REDUCE(0, SEQUENCE(COLUMNS(a)),
            LAMBDA(x,i,
                LET(
                    y, INDEX(a, , i),
                    z, INDEX(v, i),
                    x +PC(EN(IF(OR(i = ti), y * 24, y)),EN(IF(OR(i = ti), z * 24, z)),INDEX(p, i))
                )
            )
        )
    )
)
```
Excelλambda Guessing Game.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAA1Excelλambda Guessing Gamesingle cell F7:=GUESS(B7:E29,B4:E4,B3:E3,50,100,2)2Array :↓3penalties2152↓Top resultsRankCategoryResults distribution: single cell formula V74Actual Stats05-07-2219:3010lbs 2oz22.00↓(Ranking,19  ←  input cell DVTime of Birth      ←  DVpenalties2152sum5↓ties incl.)Actual Stats05-07-2219:3010lbs 2oz22.00byrow6NameDate of BirthTime of BirthWeightLength (in)PointsWinnerNr.PointsName MatchRankNamePointsTime of BirthNameNameDate of BirthTime of BirthWeightLength (in)7Dasie04-07-223:139lbs 10oz22.0022771299Annmarie1Annmarie29934DasieDasie4834451002278Holly10-07-2211:429lbs 10oz21.40175132296Grayce2Grayce29642HollyHolly404245481759Katheryn10-07-228:429lbs 11oz21.00172143289Dotti3Dotti28939KatherynKatheryn4039454817210Doralynne01-07-221:309lbs 5oz23.50167154281Mariya4Mariya28132DoralynneDoralynne4232454816711Grayce03-07-2219:3010lbs22.0029625278Carolee5Carolee278100GrayceGrayce461005010029612Micki03-07-2223:5510lbs23.0019096230Janice6Janice23046MickiMicki4646504819013Carrie04-07-229:309lbs 7oz18.00175137227Dasie7Dasie22740CarrieCarrie4840454217514Brianna04-07-223:379lbs 3oz21.75175138226Gillian8Gillian22634BriannaBrianna4834454817515Dotti05-07-228:2010lbs 8oz22.0028939190Micki9Micki19039DottiDotti100395010028916Annmarie05-07-2218:3510lbs22.00299110186Shandra10Shandra18649AnnmarieAnnmarie100495010029917Helene18-07-221:2010lbs 4oz20.001521811184Brianne11Brianne18432HeleneHelene2432504615218Krystalle07-07-226:009lbs23.001761212176Krystalle12Krystalle17637KrystalleKrystalle4637454817619Brianne04-07-227:0410lbs 7oz21.001841113175Brianna,Carrie,Holly13Brianna17538BrianneBrianne4838504818420Cathi09-07-2211:308 lbs23.001721414172Cathi,Katheryn13Carrie17542CathiCathi4242404817221Gillian10-07-225:0010lbs 8oz22.00226815167Doralynne13Holly17536GillianGillian40365010022622Sherill12-07-223:479lbs 9oz22.501651616165Rosabelle,Sherill14Cathi17234SherillSherill3634455016523Carolee05-07-222:309lbs 14oz22.00278517154Sheree14Katheryn17233CaroleeCarolee100334510027824Janice10-07-229:3010lbs 5oz22.00230618152Helene15Doralynne16740JaniceJanice40405010023025Eveline18-07-222:2311lbs 3oz21.001501919150Eveline16Rosabelle16533EvelineEveline2433454815026Sheree03-07-2219:536lbs 3oz33.001541716Sherill16550ShereeSheree4650302815427Rosabelle03-07-220:288lbs 7 oz23.001651617Sheree15431RosabelleRosabelle4631404816528Mariya05-07-2210:538lbs 9oz22.00281418Helene15241MariyaMariya100414010028129Shandra08-07-2220:459lbs 4oz21.001861019Eveline15049ShandraShandra444945481863031Challenge (2)Cell FormulasRangeFormulaG1G1=FORMULATEXT(F7)Q6Q6=Q4F7:F29F7=GUESS(B7:E29,B4:E4,B3:E3,50,100,2)G7:G29G7=XMATCH(F7#,SORT(UNIQUE(F7#),,-1))I7:I25I7=SEQUENCE(I4)J7:J25J7=INDEX(SORT(UNIQUE(F7#),,-1),SEQUENCE(I4))K7:K25K7=BYROW(J7#,LAMBDA(x,TEXTJOIN(",",,SORT(IF(ISNUMBER(XMATCH(F7#,x)),A7:A29,"")))))M7:M29M7=XMATCH(O7:O29,SORT(UNIQUE(O7:O29),,-1))N7:O29N7=SORT(HSTACK(A7:A29,F7#),{2,1},{-1,1})Q7:Q29Q7=LET(x,XMATCH(Q6,B6:E6),a,INDEX(B7:E29,,x),v,INDEX(B4:E4,x),p,INDEX(B3:E3,x),ti,IF(x=2,1,0),GUESS(a,v,p,50,100,ti))R7:R29R7=A7:A29T7:T29T7=A7:A29U7:X29U7=REDUCE(0,SEQUENCE(,4),LAMBDA(v,i,IF(SEQUENCE(,i)=i,GUESS(INDEX(B7:E29,,i),INDEX(U5:X5,i),INDEX(U4:X4,i),50,100,IF(i=2,1,0)),v)))Z7:Z29Z7=BYROW(U7#,LAMBDA(x,SUM(x)))Dynamic array formulas.Cells with Conditional FormattingCellConditionCell FormatStop If TrueI7:K25Expression=ROW($I7)-ROW($I$6)=$I$4textNOCells with Data ValidationCellAllowCriteriaU13List1,2,3,4I4Whole numberbetween 1 and ROWS(UNIQUE(O7:O29))Q4List=$B$6:$E$6


----------



## Xlambda (Jun 12, 2022)

All formulas used to reveal all kinds of stats of the game, *only single cell formulas:*
Excelλambda Guessing Game.xlsxBCD32Single cell formulas3334cells=FORMULATEXT(INDIRECT(B35:B46))35F7=GUESS(B7:E29,B4:E4,B3:E3,50,100,2)36G7=XMATCH(F7#,SORT(UNIQUE(F7#),,-1))37I7=SEQUENCE(I4)38J7=INDEX(SORT(UNIQUE(F7#),,-1),SEQUENCE(I4))39K7=BYROW(J7#,LAMBDA(x,TEXTJOIN(",",,SORT(IF(ISNUMBER(XMATCH(F7#,x)),A7:A29,"")))))40M7=XMATCH(O7:O29,SORT(UNIQUE(O7:O29),,-1))41N7=SORT(HSTACK(A7:A29,F7#),{2,1},{-1,1})42Q7=LET(x,XMATCH(Q6,B6:E6),a,INDEX(B7:E29,,x),v,INDEX(B4:E4,x),p,INDEX(B3:E3,x),ti,IF(x=2,1,0),GUESS(a,v,p,50,100,ti))43R7=A7:A2944T7=A7:A2945U7=REDUCE(0,SEQUENCE(,4),LAMBDA(v,i,IF(SEQUENCE(,i)=i,GUESS(INDEX(B7:E29,,i),INDEX(U5:X5,i),INDEX(U4:X4,i),50,100,IF(i=2,1,0)),v)))46Z7=BYROW(U7#,LAMBDA(x,SUM(x)))47Challenge (2)Cell FormulasRangeFormulaC34C34=FORMULATEXT(C35)C35:C46C35=FORMULATEXT(INDIRECT(B35:B46))Dynamic array formulas.


----------



## Xlambda (Jun 12, 2022)

The function also can be used in the case of *table format*:
Excelλambda Guessing Game.xlsxABCDEFGHIJKLM3233Table :=GUESS(Bs[@[Date of Birth]:[Length (in)]],$B$35:$E$35,$B$34:$E$34,50,100,2)34penalties2152↓35Actual Stats05-07-2219:3010lbs 2oz22.00↓36↓37NameDate of BirthTime of BirthWeightLength (in)Points38Dasie04-07-223:139lbs 10oz22.0022739Holly10-07-2211:429lbs 10oz21.4017540Katheryn10-07-228:429lbs 11oz21.0017241Doralynne01-07-221:309lbs 5oz23.5016742Grayce03-07-2219:3010lbs22.0029643Micki03-07-2223:5510lbs23.0019044Carrie04-07-229:309lbs 7oz18.0017545Brianna04-07-223:379lbs 3oz21.7517546Dotti05-07-228:2010lbs 8oz22.0028947Annmarie05-07-2218:3510lbs22.0029948Helene18-07-221:2010lbs 4oz20.0015249Krystalle07-07-226:009lbs23.0017650Brianne04-07-227:0410lbs 7oz21.0018451Cathi09-07-2211:308 lbs23.0017252Gillian10-07-225:0010lbs 8oz22.0022653Sherill12-07-223:479lbs 9oz22.5016554Carolee05-07-222:309lbs 14oz22.0027855Janice10-07-229:3010lbs 5oz22.0023056Eveline18-07-222:2311lbs 3oz21.0015057Sheree03-07-2219:536lbs 3oz33.0015458Rosabelle03-07-220:288lbs 7 oz23.0016559Mariya05-07-2210:538lbs 9oz22.0028160Shandra08-07-2220:459lbs 4oz21.0018661ChallengeCell FormulasRangeFormulaF33F33=FORMULATEXT(F38)F38:F60F38=GUESS(Bs[@[Date of Birth]:[Length (in)]],$B$35:$E$35,$B$34:$E$34,50,100,2)


----------



## Xlambda (Jun 12, 2022)

*Function's Concep*t
Excelλambda Guessing Game.xlsxABCDEFGHIJKLMNOPQRSTUVWX1Excelλambda Guessing Game2Function Concept: only 2 embedded lambdas EN,PC and a REDUCE formula for iterations. No other variables needed3Versatility4 - can handle different dimensions of array/tables5 - can handle data in numeric format, time format and any text format (2(or single) clusters of digits with any text in-between, before or after)67GUESS(a,v,p,s,b,[ti]8arguments =LAMBDA(a,v,p,s,b,[ti],
    LET(
        EN, LAMBDA(c,
            LET(
                b, IFERROR(--MID(c, SEQUENCE(, MAX(LEN(c))), 1), " "),
                d, TRIM(BYROW(b, LAMBDA(x, TEXTJOIN(, , x)))),
                --SUBSTITUTE(d, " ", ".")
            )
        ),
        PC, LAMBDA(c,v,p,
            LET(
                x, ABS(INT(c) - INT(v)),
                y, ABS(MOD(c, 1) - MOD(v, 1)),
                IF((x = 0) + (y = 0) = 2, b, s - x * p)
            )
        ),
        REDUCE(0, SEQUENCE(COLUMNS(a)),
            LAMBDA(x,i,
                LET(
                    y, INDEX(a, , i),
                    z, INDEX(v, i),
                    x +PC(EN(IF(OR(i = ti), y * 24, y)),EN(IF(OR(i = ti), z * 24, z)),INDEX(p, i))
                )
            )
        )
    )
)9a: estimations arrayembedded lambda EN(c) - Extract Numbers [ c: column or single value ]10v: values actual stats array - removes any text btw 2 cluster of digits and joins them as a number, integer or with decimals11p: penalties array (or constant array)12s: starting points (50)← expands horiz all chars with MID, if not numbers. => spaces13b: bonus points (100)← joins BYROW and TRIMs the result 14[ti]: time column index← replaces the middle space left by trim with "." decimal point => initial column transformed in a numeric column15(only if time in numeric format,Note: If the column has integers or decimal numbers EN will not affect them.16if in text format can be omitted)17embedded lambda PC(c,v,p) - Points Calculations [ c: column ; v: reference value (actual stats) ; p: points penalty ]1819← variance integers20← variance decimal part21← points calculation, if both var. are 0 => bonus points b, if not => starting points - dif of integers*penalty points p222324REDUCE iterates PC calculations for all columns, summing the results25 - since EN does not affect numeric columns but excludes any text wherever is found will use PC(EN(c),EN(v),p)26Only exception is for numeric time columns that have to be multiplied by 24, hence the time index argument ti27If time column in text format (ex:TEXT(t,"hh:mm") or 19h30m ) ti can be omitted, EN will do the job2829Note: EN's purpose is for creating numeric values for PC calculations, that can be separated 30in integer/decimal parts wits ease, is not an unit converter, only for variance calculations.3132333435Concept


----------



## Xlambda (Jun 12, 2022)

Proof that the function works for *any arrays* and any text format (any units)
To check the consistency of the results I have doubled and tripled the initial array to the right.
Excelλambda Guessing Game.xlsxABCDEFGHIJKLMNOPQRST1Excelλambda Guessing Game2Checking versatility for any arrays34Doubled the array to the right5penalties21522152=GUESS(C9:J31,C6:J6,C5:J5,50,100,{2,6})6Actual Stats05-07-2219:3010lbs 2oz22.0005-07-2219:3010lbs 2oz22.00↓7↓resultscheck for double 8NameDate of BirthTime of BirthWeightLength (in)Date of BirthTime of BirthWeightLength (in)Pointsinitial array=AND(O9:O31*2=M9#)9Dasie04-07-223:139lbs 10oz22.0004-07-223:139lbs 10oz22.00454227TRUE10Holly10-07-2211:429lbs 10oz21.4010-07-2211:429lbs 10oz21.4035017511Katheryn10-07-228:429lbs 11oz21.0010-07-228:429lbs 11oz21.0034417212Doralynne01-07-221:309lbs 5oz23.5001-07-221:309lbs 5oz23.5033416713Grayce03-07-2219:3010lbs22.0003-07-2219:3010lbs22.0059229614Micki03-07-2223:5510lbs23.0003-07-2223:5510lbs23.0038019015Carrie04-07-229:309lbs 7oz18.0004-07-229:309lbs 7oz18.0035017516Brianna04-07-223:379lbs 3oz21.7504-07-223:379lbs 3oz21.7535017517Dotti05-07-228:2010lbs 8oz22.0005-07-228:2010lbs 8oz22.0057828918Annmarie05-07-2218:3510lbs22.0005-07-2218:3510lbs22.0059829919Helene18-07-221:2010lbs 4oz20.0018-07-221:2010lbs 4oz20.0030415220Krystalle07-07-226:009lbs23.0007-07-226:009lbs23.0035217621Brianne04-07-227:0410lbs 7oz21.0004-07-227:0410lbs 7oz21.0036818422Cathi09-07-2211:308 lbs23.0009-07-2211:308 lbs23.0034417223Gillian10-07-225:0010lbs 8oz22.0010-07-225:0010lbs 8oz22.0045222624Sherill12-07-223:479lbs 9oz22.5012-07-223:479lbs 9oz22.5033016525Carolee05-07-222:309lbs 14oz22.0005-07-222:309lbs 14oz22.0055627826Janice10-07-229:3010lbs 5oz22.0010-07-229:3010lbs 5oz22.0046023027Eveline18-07-222:2311lbs 3oz21.0018-07-222:2311lbs 3oz21.0030015028Sheree03-07-2219:536lbs 3oz33.0003-07-2219:536lbs 3oz33.0030815429Rosabelle03-07-220:288lbs 7 oz23.0003-07-220:288lbs 7 oz23.0033016530Mariya05-07-2210:538lbs 9oz22.0005-07-2210:538lbs 9oz22.0056228131Shandra08-07-2220:459lbs 4oz21.0008-07-2220:459lbs 4oz21.00372186323334Tripled the array to the right, this time messed up big time with the data (red ), and time format as text35penalties21522152215236Actual Stats05-07-2219:3010lbs 2oz22.0005-07-2219:3010lbs 2oz22.0005-07-2219h30m10lbs 2oz22.0037=GUESS(C39:N61,C36:N36,C35:N35,50,100,{2,6})38NameDate of BirthTime of BirthWeightLength (in)Date of BirthTime of BirthWeightLength (in)Date of BirthTime of BirthWeightLength (in)Points39Dasie04-07-223:139lbs 10oz22.0004-07-223:139lbs 10oz22.0004-07-223 ; 139 l 10 o22,068140Holly10-07-2211:429lbs 10oz21.4010-07-2211:429lbs 10oz21.4010-07-22h11 m429l10o21;4525check for triple41Katheryn10-07-228:429lbs 11oz21.0010-07-228:429lbs 11oz21.0010-07-228h42m9.11in21.0516=AND(O9:O31*3=P39#)42Doralynne01-07-221:309lbs 5oz23.5001-07-221:309lbs 5oz23.5001-07-221h30m9,523 50501TRUE43Grayce03-07-2219:3010lbs22.0003-07-2219:3010lbs22.0003-07-2219h30m1022 ;88844Micki03-07-2223:5510lbs23.0003-07-2223:5510lbs23.0003-07-2223h55m10lbsinch2357045Carrie04-07-229:309lbs 7oz18.0004-07-229:309lbs 7oz18.0004-07-229h30m9 bs 7oz18,,,0052546Brianna04-07-223:379lbs 3oz21.7504-07-223:379lbs 3oz21.7504-07-223h37m9…321x7552547Dotti05-07-228:2010lbs 8oz22.0005-07-228:2010lbs 8oz22.0005-07-228h20m10x 8y22.0086748Annmarie05-07-2218:3510lbs22.0005-07-2218:3510lbs22.0005-07-2218h35m10 l b s22.0089749Helene18-07-221:2010lbs 4oz20.0018-07-221:2010lbs 4oz20.0018-07-221h20m10xx420.0045650Krystalle07-07-226:009lbs23.0007-07-226:009lbs23.0007-07-226hlbs923.0052851Brianne04-07-227:0410lbs 7oz21.0004-07-227:0410lbs 7oz21.0004-07-227h4mlbs10oz721.0055252Cathi09-07-2211:308 lbs23.0009-07-2211:308 lbs23.0009-07-2211h30mlbs8, 0 o z23.0051653Gillian10-07-225:0010lbs 8oz22.0010-07-225:0010lbs 8oz22.0010-07-225h10lbs 8oz22.0067854Sherill12-07-223:479lbs 9oz22.5012-07-223:479lbs 9oz22.5012-07-223h47m9lbs 9oz22.5049555Carolee05-07-222:309lbs 14oz22.0005-07-222:309lbs 14oz22.0005-07-222h30m9lbs 14oz22.0083456Janice10-07-229:3010lbs 5oz22.0010-07-229:3010lbs 5oz22.0010-07-229h30m10lbs 5oz22.0069057Eveline18-07-222:2311lbs 3oz21.0018-07-222:2311lbs 3oz21.0018-07-222h23m11lbs 3oz21.0045058Sheree03-07-2219:536lbs 3oz33.0003-07-2219:536lbs 3oz33.0003-07-2219h53m6lbs 3oz33.0046259Rosabelle03-07-220:288lbs 7 oz23.0003-07-220:288lbs 7 oz23.0003-07-220h28m8lbs 7 oz23.0049560Mariya05-07-2210:538lbs 9oz22.0005-07-2210:538lbs 9oz22.0005-07-2210h53m8lbs 9oz22.0084361Shandra08-07-2220:459lbs 4oz21.0008-07-2220:459lbs 4oz21.0008-07-2220h45m9lbs 4oz21.0055862Any arraysCell FormulasRangeFormulaM5M5=FORMULATEXT(M9)Q8,R41Q8=FORMULATEXT(Q9)M9:M31M9=GUESS(C9:J31,C6:J6,C5:J5,50,100,{2,6})Q9Q9=AND(O9:O31*2=M9#)P37P37=FORMULATEXT(P39)P39:P61P39=GUESS(C39:N61,C36:N36,C35:N35,50,100,{2,6})R42R42=AND(O9:O31*3=P39#)Dynamic array formulas.


----------



## Xlambda (Jun 14, 2022)

Inspired by today's ExcelIsFun YT (14Jun22): Duplicates in Row? Conditional Formatting, Counting and Logical Formula. Excel Magic Trick 1793
EMT1793.xlsxABCDEFGHIJKLMN12Data1Data2Data3Data4Data5Data6Data7Data8Data9Dups?Count Unique3601491322819709292TRUE8423883514931643886FALSE95FredChinChantelMikiChantelJenoTyroneTyroneLimTRUE76FredChinChantelMikiDinoJenoBiggyTyroneLimFALSE97QuadQuadMBCarlotaQuadSunshineAspenCarlotaYanakiTRUE68QadCarlotaSunshineAspenYanakiSunsetSunspotBellenMBFALSE9919637022529458441FALSE910518852184175138047FALSE911298570178132848398FALSE912801226508862271525FALSE913276266593897138457FALSE914244340298721992619FALSE9159537249010143471TRUE816901165782033654997TRUE817132446292338387371TRUE81873976191293741123FALSE9199927195853162448FALSE920766228214662732214TRUE821874141167074766971TRUE822955069259350285039TRUE723119063172093488690TRUE82492533219721514367FALSE92526single cell formula K3, no countifs27=LET(a,B3:J24,c,COLUMNS(a),f,LAMBDA([k],BYROW(a,LAMBDA(x,LET(y,COUNTA(UNIQUE(x,1)),IF(k,y,y<>c))))),HSTACK(f(),f(1)))2829Using an embedded lambda f(k) we do not need to use 2 separate BYROWs => HSTACK(f(),f(1)) 3031Note: Using countifs for CF is ok, for formulas, if we want our constructions to be used inside other formula constructions,32should be avoided (range argument can not work with array calculations)331793Cell FormulasRangeFormulaK3:L24K3=LET(a,B3:J24,c,COLUMNS(a),f,LAMBDA([k],BYROW(a,LAMBDA(x,LET(y,COUNTA(UNIQUE(x,1)),IF(k,y,y<>c))))),HSTACK(f(),f(1)))B27B27=FORMULATEXT(K3)Dynamic array formulas.Cells with Conditional FormattingCellConditionCell FormatStop If TrueB3:J24Expression=COUNTIFS($B3:$J3,B3)>1textNO


----------



## Xlambda (Jun 14, 2022)

*Extracting unique byrows in ascending order* using SPILLBYROWS (this thread post #50)
EMT1793.xlsxABCDEFGHIJKLMNOPQRSTU12Data1Data2Data3Data4Data5Data6Data7Data8Data9=SPILLBYROWS(B3:J24,LAMBDA(x,SORT(UNIQUE(x,1),,,1)))36014913228197092921419283260709192423883514931643886123313538496486885FredChinChantelMikiChantelJenoTyroneTyroneLimChantelChinFredJenoLimMikiTyrone6FredChinChantelMikiDinoJenoBiggyTyroneLimBiggyChantelChinDinoFredJenoLimMikiTyrone7QuadQuadMBCarlotaQuadSunshineAspenCarlotaYanakiAspenCarlotaMBQuadSunshineYanaki8QadCarlotaSunshineAspenYanakiSunsetSunspotBellenMBAspenBellenCarlotaMBQadSunsetSunshineSunspotYanaki91963702252945844151922294145637084105188521841751380471318414751527580881129857017813284839817293270818384859812801226508862271525121525262750628088132762665938971384571327385759626684971424434029872199261919212426294043879915953724901014347111024374347909516901165782033654997112033496578909717132446292338387371132324293846717318739761912937411233112329617374919719992719585316244826243148718595992076622821466273221414212228466273762187414116707476697116416970717476872295506925935028503925283950699395231190631720934886901117204863869093249253321972151436731415193253677292251793 (2)Cell FormulasRangeFormulaL2L2=FORMULATEXT(L3)L3:T24L3=SPILLBYROWS(B3:J24,LAMBDA(x,SORT(UNIQUE(x,1),,,1)))Dynamic array formulas.Cells with Conditional FormattingCellConditionCell FormatStop If TrueB3:J24Expression=COUNTIFS($B3:$J3,B3)>1textNO


----------



## Xlambda (Sep 30, 2021)

*ASCAN**, * *A*rray *SCAN*, *3 in 1 function*, *SCAN* *by row*, *by column*, *by array*. This is my take of tboulden's SCANBYROW/BYCOL.
Uses *only* *new!! SCAN* lambda helper function, no need of byrow,bycol,makearray, or lambda as arguments.

```
=LAMBDA(a,[d],
    LET(y,IF(d=1,TRANSPOSE(a),a),s,SCAN(0,y,LAMBDA(v,a,v+a)),r,ROWS(s),c,COLUMNS(s),sr,SEQUENCE(r)-1,
       x,s-IF(d,IFERROR(INDEX(INDEX(s,,c),sr)*sr^0,0),0),
       IF(d=1,TRANSPOSE(x),x)
   )
)
```
LAMBDA 1.1.3.xlsxABCDEFGHIJKLMNOPQRST1d,omitted (by array)d,-1 (by rows)d,1 (by clms)2sample 1a=ASCAN(A3:D6)=ASCAN(A3:D6,-1)=ASCAN(A3:D6,1)312341361013610123445678152128365111826681012591011124555667891930421518212461314151691105120136132742582832364078d,omitted (by array)d,-1 (by rows)d,1 (by clms)9sample 2a=ASCAN(A10#)=ASCAN(A10#,-1)=ASCAN(A10#,1)1012341361013610123411567815212836511182668101212910111245556678919304215182124131314151691105120136132742582832364014171819201531711902101735547445505560152122232423125327630021436690667278841617Note: Did not set an extra argument for any kind of "initial value", since the only thing it does is too simple,18adds a constant value to the final outcome , like in 10+ASCAN(a)19ASCAN postCell FormulasRangeFormulaF2,P9,K9,F9,P2,K2F2=FORMULATEXT(F3)F3:I6F3=ASCAN(A3:D6)K3:N6K3=ASCAN(A3:D6,-1)P3:S6P3=ASCAN(A3:D6,1)A10:D15A10=SEQUENCE(6,4)F10:I15F10=ASCAN(A10#)K10:N15K10=ASCAN(A10#,-1)P10:S15P10=ASCAN(A10#,1)Dynamic array formulas.


----------



## Xlambda (Jun 14, 2022)

Extra challenge: *Extract only dups and nr. of dups for each row. *
EMT1793.xlsxABCDEFGHIJKLMNOP12Data1Data2Data3Data4Data5Data6Data7Data8Data936014913228197092922,92423883514931643886 5FredChinChantelMikiChantelJenoTyroneTyroneLim2,Chantel2,Tyrone6FredChinChantelMikiDinoJenoBiggyTyroneLim 7QuadQuadMBCarlotaQuadSunshineAspenCarlotaYanaki3,Quad2,Carlota8QadCarlotaSunshineAspenYanakiSunsetSunspotBellenMB 919637022529458441 10518852184175138047 11298570178132848398 12801226508862271525 13276266593897138457 14244340298721992619 1595372490101434712,1169011657820336549972,65171324462923383873712,381873976191293741123 199927195853162448 207662282146627322142,62218741411670747669712,41229550692593502850393,50231190631720934886902,902492533219721514367 2526Formulas range L3:L24, a formula for each row27=LET(x,B3:J3,u,UNIQUE(FILTER(x,ISNA(XMATCH(x,UNIQUE(x,1,1))),""),1),m,MAP(u,LAMBDA(y,SUM(--(y=x)))),IF(m,m&","&u,""))281793 (3)Cell FormulasRangeFormulaL3:L4,L6,L8:L24,L7:M7,L5:M5L3=LET(x,B3:J3,u,UNIQUE(FILTER(x,ISNA(XMATCH(x,UNIQUE(x,1,1))),""),1),m,MAP(u,LAMBDA(y,SUM(--(y=x)))),IF(m,m&","&u,""))B27B27=FORMULATEXT(L3)Cells with Conditional FormattingCellConditionCell FormatStop If TrueB3:J24Expression=COUNTIFS($B3:$J3,B3)>1textNO


----------



## Xlambda (Jun 14, 2022)

If we can write a formula for each row, no matter how complex it looks, we can use the same construction as the lambda helper part in SPILLBYROWS => a single cell formula that spills.
EMT1793.xlsxABCDEFGHIJKLMNOPQRS12Data1Data2Data3Data4Data5Data6Data7Data8Data936014913228197092922,924238835149316438865FredChinChantelMikiChantelJenoTyroneTyroneLim2,Chantel2,Tyrone6FredChinChantelMikiDinoJenoBiggyTyroneLim7QuadQuadMBCarlotaQuadSunshineAspenCarlotaYanaki3,Quad2,Carlota8QadCarlotaSunshineAspenYanakiSunsetSunspotBellenMB91963702252945844110518852184175138047112985701781328483981280122650886227152513276266593897138457142443402987219926191595372490101434712,1169011657820336549972,65171324462923383873712,381873976191293741123199927195853162448207662282146627322142,62218741411670747669712,41229550692593502850393,50231190631720934886902,9024925332197215143672526Single cell formula L3:27=SPILLBYROWS(B3:J24,LAMBDA(x,LET(u,UNIQUE(FILTER(x,ISNA(XMATCH(x,UNIQUE(x,1,1))),""),1),m,MAP(u,LAMBDA(y,SUM(--(y=x)))),IF(m,m&","&u,""))))281793 (3)Cell FormulasRangeFormulaL3:M24L3=SPILLBYROWS(B3:J24,LAMBDA(x,LET(u,UNIQUE(FILTER(x,ISNA(XMATCH(x,UNIQUE(x,1,1))),""),1),m,MAP(u,LAMBDA(y,SUM(--(y=x)))),IF(m,m&","&u,""))))B27B27=FORMULATEXT(L3)Dynamic array formulas.Cells with Conditional FormattingCellConditionCell FormatStop If TrueB3:J24Expression=COUNTIFS($B3:$J3,B3)>1textNO


----------



## Xlambda (Jun 14, 2022)

*To do the same things using a construction BYROW(a,LAMBDA(x,MAP(UNIQUE(x,1),LAMBDA(y, .......*
Because BYROW cannot spill we have to use TEXTJOIN
EMT1793.xlsxABCDEFGHIJKLMNOPQRST12Data1Data2Data3Data4Data5Data6Data7Data8Data936014913228197092922,924238835149316438865FredChinChantelMikiChantelJenoTyroneTyroneLim2,Chantel;2,Tyrone6FredChinChantelMikiDinoJenoBiggyTyroneLim7QuadQuadMBCarlotaQuadSunshineAspenCarlotaYanaki3,Quad;2,Carlota8QadCarlotaSunshineAspenYanakiSunsetSunspotBellenMB91963702252945844110518852184175138047112985701781328483981280122650886227152513276266593897138457142443402987219926191595372490101434712,1169011657820336549972,65171324462923383873712,381873976191293741123199927195853162448207662282146627322142,62218741411670747669712,41229550692593502850393,50231190631720934886902,9024925332197215143672526Single cell formula L3:27=LET(a,B3:J24,BYROW(a,LAMBDA(x,LET(m,MAP(UNIQUE(x,1),LAMBDA(y,LET(z,SUM(--(y=x)),a,z>1,b,FILTER(z,a,""),c,FILTER(y,a,""),r,b&","&c,r))),TEXTJOIN(";",,FILTER(m,m<>",",""))))))281793 (3)Cell FormulasRangeFormulaL3:L24L3=LET(a,B3:J24,BYROW(a,LAMBDA(x,LET(m,MAP(UNIQUE(x,1),LAMBDA(y,LET(z,SUM(--(y=x)),a,z>1,b,FILTER(z,a,""),c,FILTER(y,a,""),r,b&","&c,r))),TEXTJOIN(";",,FILTER(m,m<>",",""))))))B27B27=FORMULATEXT(L3)Dynamic array formulas.Cells with Conditional FormattingCellConditionCell FormatStop If TrueB3:J24Expression=COUNTIFS($B3:$J3,B3)>1textNO


----------



## Xlambda (Jun 14, 2022)

*Extracting items byrow that appear exactly once, ascending order.*
EMT1793.xlsxABCDEFGHIJKLMNOPQRSTU12Data1Data2Data3Data4Data5Data6Data7Data8Data9=SPILLBYROWS(B3:J24,LAMBDA(x,SORT(UNIQUE(x,1,1),,,1)))360149132281970929214192832607091423883514931643886123313538496486885FredChinChantelMikiChantelJenoTyroneTyroneLimChinFredJenoLimMiki6FredChinChantelMikiDinoJenoBiggyTyroneLimBiggyChantelChinDinoFredJenoLimMikiTyrone7QuadQuadMBCarlotaQuadSunshineAspenCarlotaYanakiAspenMBSunshineYanaki8QadCarlotaSunshineAspenYanakiSunsetSunspotBellenMBAspenBellenCarlotaMBQadSunsetSunshineSunspotYanaki9196370225294584415192229414563708410518852184175138047131841475152758088112985701781328483981729327081838485981280122650886227152512152526275062808813276266593897138457132738575962668497142443402987219926191921242629404387991595372490101434711024374347909516901165782033654997112033497890971713244629233838737113232429467173187397619129374112331123296173749197199927195853162448262431487185959920766228214662732214142122284673762187414116707476697116697071747687229550692593502850392528396993952311906317209348869011172048638693249253321972151436731415193253677292251793 (3)Cell FormulasRangeFormulaL2L2=FORMULATEXT(L3)L3:T24L3=SPILLBYROWS(B3:J24,LAMBDA(x,SORT(UNIQUE(x,1,1),,,1)))Dynamic array formulas.Cells with Conditional FormattingCellConditionCell FormatStop If TrueB3:J25Expression=COUNTIFS($B3:$J3,B3)>1textNO


----------



## Xlambda (Jun 15, 2022)

Another formula that extracts unique byrow and spills, not as elegant and not good for large arrays because *textjoin in limited in chars joining*, but works:
EMT1793.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAAB12Data1Data2Data3Data4Data5Data6Data7Data8Data9=IFNA(TEXTSPLIT(TEXTJOIN(";",,BYROW(B3:J24,LAMBDA(x,TEXTJOIN(",",,SORT(UNIQUE(x,1),,,1))))),",",";"),"")36014913228197092921419283260709192423883514931643886123313538496486885FredChinChantelMikiChantelJenoTyroneTyroneLimChantelChinFredJenoLimMikiTyrone6FredChinChantelMikiDinoJenoBiggyTyroneLimBiggyChantelChinDinoFredJenoLimMikiTyrone7QuadQuadMBCarlotaQuadSunshineAspenCarlotaYanakiAspenCarlotaMBQuadSunshineYanaki8QadCarlotaSunshineAspenYanakiSunsetSunspotBellenMBAspenBellenCarlotaMBQadSunsetSunshineSunspotYanaki91963702252945844151922294145637084105188521841751380471318414751527580881129857017813284839817293270818384859812801226508862271525121525262750628088132762665938971384571327385759626684971424434029872199261919212426294043879915953724901014347111024374347909516901165782033654997112033496578909717132446292338387371132324293846717318739761912937411233112329617374919719992719585316244826243148718595992076622821466273221414212228466273762187414116707476697116416970717476872295506925935028503925283950699395231190631720934886901117204863869093249253321972151436731415193253677292251793 (4)Cell FormulasRangeFormulaL2L2=FORMULATEXT(L3)L3:T24L3=IFNA(TEXTSPLIT(TEXTJOIN(";",,BYROW(B3:J24,LAMBDA(x,TEXTJOIN(",",,SORT(UNIQUE(x,1),,,1))))),",",";"),"")Dynamic array formulas.Cells with Conditional FormattingCellConditionCell FormatStop If TrueB3:J25Expression=COUNTIFS($B3:$J3,B3)>1textNO


----------



## Xlambda (Jun 20, 2022)

To overcome the limitation of textjoining the whole array in a single cell we can use ATEXTSPLIT, a function that splits each row "analogical"
EMT1793.xlsxABCDEFGHIJKLMNOPQRSTUVWX1step 1step 22Data1Data2Data3Data4Data5Data6Data7Data8Data9=BYROW(B3:J24,LAMBDA(x,TEXTJOIN(",",,SORT(UNIQUE(x,1),,,1))))=ATEXTSPLIT(L3#,",")360149132281970929214,19,28,32,60,70,91,9214192832607091924238835149316438861,23,31,35,38,49,64,86,88123313538496486885FredChinChantelMikiChantelJenoTyroneTyroneLimChantel,Chin,Fred,Jeno,Lim,Miki,TyroneChantelChinFredJenoLimMikiTyrone6FredChinChantelMikiDinoJenoBiggyTyroneLimBiggy,Chantel,Chin,Dino,Fred,Jeno,Lim,Miki,TyroneBiggyChantelChinDinoFredJenoLimMikiTyrone7QuadQuadMBCarlotaQuadSunshineAspenCarlotaYanakiAspen,Carlota,MB,Quad,Sunshine,YanakiAspenCarlotaMBQuadSunshineYanaki8QadCarlotaSunshineAspenYanakiSunsetSunspotBellenMBAspen,Bellen,Carlota,MB,Qad,Sunset,Sunshine,Sunspot,YanakiAspenBellenCarlotaMBQadSunsetSunshineSunspotYanaki9196370225294584415,19,22,29,41,45,63,70,84519222941456370841051885218417513804713,18,41,47,51,52,75,80,881318414751527580881129857017813284839817,29,32,70,81,83,84,85,981729327081838485981280122650886227152512,15,25,26,27,50,62,80,881215252627506280881327626659389713845713,27,38,57,59,62,66,84,971327385759626684971424434029872199261919,21,24,26,29,40,43,87,991921242629404387991595372490101434711,10,24,37,43,47,90,951102437434790951690116578203365499711,20,33,49,65,78,90,9711203349657890971713244629233838737113,23,24,29,38,46,71,73132324293846717318739761912937411233,11,23,29,61,73,74,91,97311232961737491971999271958531624482,6,24,31,48,71,85,95,9926243148718595992076622821466273221414,21,22,28,46,62,73,7614212228466273762187414116707476697116,41,69,70,71,74,76,8716416970717476872295506925935028503925,28,39,50,69,93,95252839506993952311906317209348869011,17,20,48,63,86,90,93111720486386909324925332197215143673,14,15,19,32,53,67,72,92314151932536772922526Extracting unique byrow ascending orderExtracting unique that appear only once, byrow, ascending order27single cellsingle cell28=ATEXTSPLIT(BYROW(B3:J24,LAMBDA(x,TEXTJOIN(",",,SORT(UNIQUE(x,1),,,1)))),",")=ATEXTSPLIT(BYROW(B3:J24,LAMBDA(x,TEXTJOIN(",",,SORT(UNIQUE(x,1,1),,,1)))),",")2914192832607091921419283260709130123313538496486881233135384964868831ChantelChinFredJenoLimMikiTyroneChinFredJenoLimMiki32BiggyChantelChinDinoFredJenoLimMikiTyroneBiggyChantelChinDinoFredJenoLimMikiTyrone33AspenCarlotaMBQuadSunshineYanakiAspenMBSunshineYanaki34AspenBellenCarlotaMBQadSunsetSunshineSunspotYanakiAspenBellenCarlotaMBQadSunsetSunshineSunspotYanaki35519222941456370845192229414563708436131841475152758088131841475152758088371729327081838485981729327081838485983812152526275062808812152526275062808839132738575962668497132738575962668497401921242629404387991921242629404387994111024374347909510243743479095421120334965789097112033497890974313232429384671731323242946717344311232961737491973112329617374919745262431487185959926243148718595994614212228466273761421222846737647164169707174768716697071747687482528395069939525283969939549111720486386909311172048638693503141519325367729231415193253677292511793 (5)Cell FormulasRangeFormulaL2,N2,N28,B28L2=FORMULATEXT(L3)L3:L24L3=BYROW(B3:J24,LAMBDA(x,TEXTJOIN(",",,SORT(UNIQUE(x,1),,,1))))N3:V24N3=ATEXTSPLIT(L3#,",")B29:J50B29=ATEXTSPLIT(BYROW(B3:J24,LAMBDA(x,TEXTJOIN(",",,SORT(UNIQUE(x,1),,,1)))),",")N29:V50N29=ATEXTSPLIT(BYROW(B3:J24,LAMBDA(x,TEXTJOIN(",",,SORT(UNIQUE(x,1,1),,,1)))),",")Dynamic array formulas.Cells with Conditional FormattingCellConditionCell FormatStop If TrueB3:J25,B26,I26:J26Expression=COUNTIFS($B3:$J3,B3)>1textNO


----------



## Xlambda (Nov 24, 2022)

Cool example to share.
Mike asked me on the comments section of his latest YT:
*"a formula that will enact a running sub-count for consecutive numbers. I have a none spill formula like this:

G10 contains: 2
and I use this formula in Column H starting in cell H10: =IF(G10=G9,H9+1,1) to get: 1
G11 contains: 1
and I use this formula in Column H starting in cell H10: =IF(G11=G10,H10+1,1) to get: 1
G12 contains: 3
and I use this formula in Column H starting in cell H10: =IF(G12=G11,H11+1,1) to get: 1
G13 contains: 1
and I use this formula in Column H starting in cell H10: =IF(G13=G12,H12+1,1) to get: 1
G14 contains: 3
and I use this formula in Column H starting in cell H10: =IF(G14=G13,H13+1,1) to get: 1
G15 contains: 3
and I use this formula in Column H starting in cell H10: =IF(G15=G14,H14+1,1) to get: 2
G16 contains: 2
and I use this formula in Column H starting in cell H10: =IF(G16=G15,H15+1,1) to get: 1
G17 contains: 2
and I use this formula in Column H starting in cell H10: =IF(G17=G16,H16+1,1) to get: 2 "*

Since it will be a SCAN/REDUCE lambda formula scenario like so many other examples here, belongs to this thread.
1 on 1 as requested:
AUNIQUE.xlsxFGHIJKLMNOP91021=IF(G10=G9,H9+1,1)1111=IF(G11=G10,H10+1,1)1231=IF(G12=G11,H11+1,1)1311=IF(G13=G12,H12+1,1)1431=IF(G14=G13,H13+1,1)1532=IF(G15=G14,H14+1,1)1621=IF(G16=G15,H15+1,1)1722=IF(G17=G16,H16+1,1)1819single cell lambda formula20=LET(a,G10:G17,SCAN(1,SEQUENCE(ROWS(a)),LAMBDA(v,i,IF(i=1,v,IF(INDEX(a,i-1)=INDEX(a,i),v+1,1)))))21122123124125126227128229ASCAN 1Cell FormulasRangeFormulaH10:H17H10=IF(G10=G9,H9+1,1)I10:I17I10=FORMULATEXT(H10)H20H20=FORMULATEXT(H21)H21:H28H21=LET(a,G10:G17,SCAN(1,SEQUENCE(ROWS(a)),LAMBDA(v,i,IF(i=1,v,IF(INDEX(a,i-1)=INDEX(a,i),v+1,1)))))Dynamic array formulas.


----------



## Xlambda (Dec 31, 2022)

Happy New Year 2023 All !! ✌️🎆🥳
Last post 2022, inspired by a question on YT comment section. latest Mike's YT. Excel LAMBDA function – Every Single Things You Ever Wanted To Know - 365 MECS 10
*Running totals for 2 columns array. *
Lambda formulas, 3 scenarios:
Book1ABCDEFGHIJKLMNOP1Running totals double column array23sales 1sales 2=LET(a,B4:C10,r,REDUCE(0,SEQUENCE(ROWS(a)),LAMBDA(v,i,VSTACK(v,INDEX(v,i)+INDEX(a,i)))),DROP(r,1))4120451204552302335068643083780151752045130019687128920122859439562451341104923729433781112Running totals single column , 2 values joined1314=LET(a,C15#,r,REDUCE(0,SEQUENCE(ROWS(a)),LAMBDA(v,i,VSTACK(v,INDEX(v,i,)+TEXTSPLIT(INDEX(a,i,1),",")))),DROP(r,1))15120,451204516230,233506817430,8378015118520,45130019619712,89201228520439,56245134121492,3729433782223Running totals, cumulative 2 clms 2425=SCAN(0,B4:C10,LAMBDA(v,i,v+i))261201652739541828848931291451149630220822973127362792323284332133Sheet1Cell FormulasRangeFormulaE3,E25,E14E3=FORMULATEXT(E4)E4:F10E4=LET(a,B4:C10,r,REDUCE(0,SEQUENCE(ROWS(a)),LAMBDA(v,i,VSTACK(v,INDEX(v,i)+INDEX(a,i)))),DROP(r,1))C15:C21C15=B4:B10&","&C4:C10E15:F21E15=LET(a,C15#,r,REDUCE(0,SEQUENCE(ROWS(a)),LAMBDA(v,i,VSTACK(v,INDEX(v,i,)+TEXTSPLIT(INDEX(a,i,1),",")))),DROP(r,1))E26:F32E26=SCAN(0,B4:C10,LAMBDA(v,i,v+i))Dynamic array formulas.


----------



## Xlambda (Jan 1, 2023)

...one year later..first post 2023
When comes to running totals for multiple columns we have 3 ways,*3 different techniques/concepts*
- main function of this thread ASCAN
- adding row by row
- scanning column by column
last post 2022 ASCAN.xlsxABCDEFGHIJKLMNOPQRSTUVWX1defined function, any scenario, very eficient2=RANDARRAY(10,10,10,99,1)=ASCAN(B3#,1)3705093585259472772637050935852594727726342535948335411435141895851871418710061628681577918271825668871887172176269212169156129149104168663374381809828605526235213312293249254157209159194717727790992621879849252285389383348280178296257243862824718453375875858314367436401393313253383315301999306911267646544454413397505412419389299437359355101943543617196277426143244055944843640836151440141611669016831854212559794985305755314544623825394604951211994398416622766017509629618629495528404615520512131415lambda formula when nr of columns way larger than the number of rows16=LET(a,B3#,r,REDUCE(0,SEQUENCE(ROWS(a)),LAMBDA(v,i,VSTACK(v,INDEX(v,i)+INDEX(a,i)))),DROP(r,1))1770509358525947277263189585187141871006162868119172176269212169156129149104168202352133122932492541572091591942125228538938334828017829625724322314367436401393313253383315301234133975054124193892994373593552443244055944843640836151440141625498530575531454462382539460495265096296186294955284046155205122728lambda formula when nr. of rows way larger than nr. of columns29=LET(a,B3#,LET(s,LAMBDA(x,SCAN(0,x,LAMBDA(v,i,v+i))),r,REDUCE(0,SEQUENCE(COLUMNS(a)),LAMBDA(v,i,HSTACK(v,s(INDEX(a,,i))))),DROP(r,,1)))30705093585259472772633195851871418710061628681321721762692121691561291491041683323521331229324925415720915919434252285389383348280178296257243353143674364013933132533833153013641339750541241938929943735935537432440559448436408361514401416384985305755314544623825394604953950962961862949552840461552051240Sheet2Cell FormulasRangeFormulaB2,M2B2=FORMULATEXT(B3)B3:K12B3=RANDARRAY(10,10,10,99,1)M3:V12M3=ASCAN(B3#,1)J16J16=FORMULATEXT(M17)M17:V26M17=LET(a,B3#,r,REDUCE(0,SEQUENCE(ROWS(a)),LAMBDA(v,i,VSTACK(v,INDEX(v,i)+INDEX(a,i)))),DROP(r,1))D29D29=FORMULATEXT(M30)M30:V39M30=LET(a,B3#,LET(s,LAMBDA(x,SCAN(0,x,LAMBDA(v,i,v+i))),r,REDUCE(0,SEQUENCE(COLUMNS(a)),LAMBDA(v,i,HSTACK(v,s(INDEX(a,,i))))),DROP(r,,1)))Dynamic array formulas.


----------



## bines53 (Tuesday at 6:28 PM)

Hi Xlambda,

regarding to *Running totals for 2 columns array,I tried to do with the function TAKE, I think you can develop this without the need for recursion.*
=MAP(TAKE(B4:C10,SEQUENCE(7,,1,1)),LAMBDA(x,SUM(INDEX(x,,1))))

TAKE(B4:C10,SEQUENCE(7,,1,1)) ,
Creates the cumulative amount.

David


----------

