SCANBYROW

SCANBYROW(initial_value,array,fn)
initial_value
first value for calculation; can be blank, same as SCAN
array
array to calculate over
fn
a 2-parameter LAMBDA, i.e. LAMBDA(acc,val,acc+val)

Performs a SCAN(initial_value,row,fn) on each row of array.

tboulden

Board Regular
Joined
Jan 14, 2021
Messages
73
Office Version
  1. 365
Platform
  1. Windows
Performs a SCAN(initial_value,row,fn) on each row of array. Corresponding SCANBYCOL is also shown.

BYROW/BYCOL are limited in returning a single value per row/column respectively, whereas SCAN returns an array of the same dimensions as the array being SCANned. We can overcome this limitation by thunk-ing the array result: this is essentially wrapping with a LAMBDA with no parameters, i.e. LAMBDA(x). Then to get the thunked x out, we do LAMBDA(x)() = x.

Excel Formula:
=LAMBDA(initial_value,array,fn,
    LET(
        rows_,ROWS(array),
        cols_,COLUMNS(array),
        row_thunks,
            BYROW(array,
                LAMBDA(row_,
                    LAMBDA(SCAN(initial_value,row_,fn))
                )
            ),
        MAKEARRAY(rows_,cols_,
            LAMBDA(i,j,
                INDEX(
                    INDEX(row_thunks,i,1)(),
                    1,
                    j
                )
            )
        )
    )
)

LAMBDA_SCANBY.xlsx
ABCDEFGHIJ
1
21234
35678
49101112
513141516
6
7=SCANBYROW(,$B$2#, LAMBDA(acc,val,acc+val))=SCANBYROW(10,$B$2#, LAMBDA(acc,val,acc+val))
8
91361011131620
10511182615212836
11919304219294052
121327425823375268
13
14=SCANBYCOL(,$B$2#, LAMBDA(acc,val,acc+val))=SCANBYCOL(10,$B$2#, LAMBDA(acc,val,acc+val))
15
16123411121314
1768101216182022
181518212425283134
192832364038424650
Sheet1
Cell Formulas
RangeFormula
B2:E5B2=SEQUENCE(4,4)
B7,G14,B14,G7B7=FORMULATEXT(B9)
B9:E12B9=SCANBYROW(,$B$2#, LAMBDA(acc,val,acc+val))
G9:J12G9=SCANBYROW(10,$B$2#, LAMBDA(acc,val,acc+val))
B16:E19B16=SCANBYCOL(,$B$2#, LAMBDA(acc,val,acc+val))
G16:J19G16=SCANBYCOL(10,$B$2#, LAMBDA(acc,val,acc+val))
Dynamic array formulas.
 
Upvote 1
SCANBYCOL
Excel Formula:
=LAMBDA(initial_value,array,fn,
    LET(
        rows_,ROWS(array),
        cols_,COLUMNS(array),
        col_thunks,
            BYCOL(array,
                LAMBDA(col_,
                    LAMBDA(SCAN(initial_value,col_,fn))
                )
            ),
        MAKEARRAY(rows_,cols_,
            LAMBDA(i,j,
                INDEX(
                    INDEX(col_thunks,1,j)(),
                    i,
                    1
                )
            )
        )
    )
)
 
Additional examples:
LAMBDA_SCANBY.xlsx
ABCDEFGHIJ
1
2ABCD
3EFGH
4IJKL
5MNOP
6
7=SCANBYROW(,$B$2#, LAMBDA(acc,val,acc&val))=SCANBYROW("Test_",$B$2#, LAMBDA(acc,val,acc&val))
8
9AABABCABCDTest_ATest_ABTest_ABCTest_ABCD
10EEFEFGEFGHTest_ETest_EFTest_EFGTest_EFGH
11IIJIJKIJKLTest_ITest_IJTest_IJKTest_IJKL
12MMNMNOMNOPTest_MTest_MNTest_MNOTest_MNOP
13
14=SCANBYCOL(,$B$2#, LAMBDA(acc,val,val&acc))=SCANBYCOL("_Test",$B$2#, LAMBDA(acc,val,val&acc))
15
16ABCDA_TestB_TestC_TestD_Test
17EAFBGCHDEA_TestFB_TestGC_TestHD_Test
18IEAJFBKGCLHDIEA_TestJFB_TestKGC_TestLHD_Test
19MIEANJFBOKGCPLHDMIEA_TestNJFB_TestOKGC_TestPLHD_Test
Alpha
Cell Formulas
RangeFormula
B2:E5B2=CHAR(SEQUENCE(4,4,65,1))
B7,G14,B14,G7B7=FORMULATEXT(B9)
B9:E12B9=SCANBYROW(,$B$2#, LAMBDA(acc,val,acc&val))
G9:J12G9=SCANBYROW("Test_",$B$2#, LAMBDA(acc,val,acc&val))
B16:E19B16=SCANBYCOL(,$B$2#, LAMBDA(acc,val,val&acc))
G16:J19G16=SCANBYCOL("_Test",$B$2#, LAMBDA(acc,val,val&acc))
Dynamic array formulas.


Functions below fairly self-explanatory, IDENTITY = LAMBDA(x,x), ADDONE = LAMBDA(x,x+1), etc.

LAMBDA_SCANBY.xlsx
ABCDEFGHI
1COMPOSE := LAMBDA(f,g,LAMBDA(x,g(f(x))))
2
3CHOOSE functions array
45636IDENTITYADDONEMINUSTHREETIMESTWO
524618MINUSTHREETIMESTWOADDTWOTIMESTHREE
67785ADDTWOIDENTITYADDONEMINUSTHREE
715121224TIMESTHREEMINUSTHREEIDENTITYTIMESTWO
8
9Composed across the row
10IDENTITYADDONE(IDENTITY)MINUSTHREE(ADDONE(IDENTITY))TIMESTWO(MINUSTHREE(ADDONE(IDENTITY)))
11MINUSTHREETIMESTWO(MINUSTHREE)ADDTWO(TIMESTWO(MINUSTHREE))TIMESTHREE(ADDTWO(TIMESTWO(MINUSTHREE)))
12ADDTWOIDENTITY(ADDTWO)ADDONE(IDENTITY(ADDTWO))MINUSTHREE(ADDONE(IDENTITY(ADDTWO)))
13TIMESTHREEMINUSTHREE(TIMESTHREE)IDENTITY(MINUSTHREE(TIMESTHREE))TIMESTWO(IDENTITY(MINUSTHREE(TIMESTHREE)))
14
15Composed down the column
1656210IDENTITYADDONEMINUSTHREETIMESTWO
17212430MINUSTHREE(IDENTITY)TIMESTWO(ADDONE)ADDTWO(MINUSTHREE)TIMESTHREE(TIMESTWO)
18412527ADDTWO(MINUSTHREE(IDENTITY))IDENTITY(TIMESTWO(ADDONE))ADDONE(ADDTWO(MINUSTHREE))MINUSTHREE(TIMESTHREE(TIMESTWO))
19129554TIMESTHREE(ADDTWO(MINUSTHREE(IDENTITY)))MINUSTHREE(IDENTITY(TIMESTWO(ADDONE)))IDENTITY(ADDONE(ADDTWO(MINUSTHREE)))TIMESTWO(MINUSTHREE(TIMESTHREE(TIMESTWO)))
COMPOSE
Cell Formulas
RangeFormula
A4:D7A4=MAP(SCANBYROW(,CHOOSE({1,2,3,4;3,4,5,6;5,1,2,3;6,3,1,4},IDENTITY,ADDONE,MINUSTHREE,TIMESTWO,ADDTWO,TIMESTHREE),COMPOSE),LAMBDA(fn,fn(5)))
F4:I7F4=CHOOSE({1,2,3,4;3,4,5,6;5,1,2,3;6,3,1,4},"IDENTITY","ADDONE","MINUSTHREE","TIMESTWO","ADDTWO","TIMESTHREE")
F10:I13F10=SCANBYROW(,F4#,LAMBDA(acc,fn,fn&"("&acc&")"))
A16:D19A16=MAP(SCANBYCOL(,CHOOSE({1,2,3,4;3,4,5,6;5,1,2,3;6,3,1,4},IDENTITY,ADDONE,MINUSTHREE,TIMESTWO,ADDTWO,TIMESTHREE),COMPOSE),LAMBDA(fn,fn(5)))
F16:I19F16=SCANBYCOL(,F4#,LAMBDA(acc,fn,fn&"("&acc&")"))
Dynamic array formulas.
 
Hi tboulden,

These functions are absolutely amazing and useful and thanks for sharing. However, I've got an error from one of the excamples above when I copied it into a speadsheet. In fact, I first put the SCANBYROW, SCANBYCOL, COMPOSE functions into the name manager. Then I got this below:

SCANBYROW+SCANBYCOL.xlsx
ABCDEFGHI
1COMPOSE := LAMBDA(f,g,LAMBDA(x,g(f(x))))
2
3CHOOSE functions array
4#VALUE!#VALUE!#VALUE!#VALUE!IDENTITYADDONEMINUSTHREETIMESTWO
5#VALUE!#VALUE!#VALUE!#VALUE!MINUSTHREETIMESTWOADDTWOTIMESTHREE
6#VALUE!#VALUE!#VALUE!#VALUE!ADDTWOIDENTITYADDONEMINUSTHREE
7#VALUE!#VALUE!#VALUE!#VALUE!TIMESTHREEMINUSTHREEIDENTITYTIMESTWO
8
9Composed across the row
10IDENTITYADDONE(IDENTITY)MINUSTHREE(ADDONE(IDENTITY))TIMESTWO(MINUSTHREE(ADDONE(IDENTITY)))
11MINUSTHREETIMESTWO(MINUSTHREE)ADDTWO(TIMESTWO(MINUSTHREE))TIMESTHREE(ADDTWO(TIMESTWO(MINUSTHREE)))
12ADDTWOIDENTITY(ADDTWO)ADDONE(IDENTITY(ADDTWO))MINUSTHREE(ADDONE(IDENTITY(ADDTWO)))
13TIMESTHREEMINUSTHREE(TIMESTHREE)IDENTITY(MINUSTHREE(TIMESTHREE))TIMESTWO(IDENTITY(MINUSTHREE(TIMESTHREE)))
14
15Composed down the column
16#NAME?#NAME?#NAME?#NAME?IDENTITYTIMESTWOIDENTITYIDENTITY
17#NAME?#NAME?#NAME?#NAME?MINUSTHREE(IDENTITY)TIMESTWO(ADDONE)ADDTWO(MINUSTHREE)TIMESTHREE(TIMESTWO)
18#NAME?#NAME?#NAME?#NAME?ADDTWO(MINUSTHREE(IDENTITY))IDENTITY(TIMESTWO(ADDONE))ADDONE(ADDTWO(MINUSTHREE))MINUSTHREE(TIMESTHREE(TIMESTWO))
19#NAME?#NAME?#NAME?#NAME?TIMESTHREE(ADDTWO(MINUSTHREE(IDENTITY)))MINUSTHREE(IDENTITY(TIMESTWO(ADDONE)))IDENTITY(ADDONE(ADDTWO(MINUSTHREE)))TIMESTWO(MINUSTHREE(TIMESTHREE(TIMESTWO)))
Sheet2
Cell Formulas
RangeFormula
A4:D7A4=MAP(SCANBYROW(,CHOOSE({1,2,3,4;3,4,5,6;5,1,2,3;6,3,1,4},IDENTITY,ADDONE,MINUSTHREE,TIMESTWO,ADDTWO,TIMESTHREE),COMPOSE),LAMBDA(fn,fn(5)))
F4:I7F4=CHOOSE({1,2,3,4;3,4,5,6;5,1,2,3;6,3,1,4},"IDENTITY","ADDONE","MINUSTHREE","TIMESTWO","ADDTWO","TIMESTHREE")
F10:I13F10=SCANBYROW(,F4#,LAMBDA(acc,fn,fn&"("&acc&")"))
A16:D19A16=MAP(SCANBYCOL(,CHOOSE({1,2,3,4;3,4,5,6;5,1,2,3;6,3,1,4},IDENTITY,ADDONE,MINUSTHREE,TIMESTWO,ADDTWO,TIMESTHREE),COMPOSE),LAMBDA(fn,fn(5)))
F16:I19F16=SCANBYCOL(,F4#,LAMBDA(acc,fn,fn&"("&acc&")"))
Dynamic array formulas.


Could you please help debug the #VALUE! error? Meanwhile, I didn't quite understand the COMPOSE function "=LAMBDA(f,g,LAMBDA(x,g(f(x))))" and how it works. May I expect you to shed some light on it?

Thank you so much in advance.
 
Could you please help debug the #VALUE! error? Meanwhile, I didn't quite understand the COMPOSE function "=LAMBDA(f,g,LAMBDA(x,g(f(x))))" and how it works. May I expect you to shed some light on it?

Thank you so much in advance.
Hi vincethesun! Did you also put IDENTITY, ADDONE, etc. in the Name Manager?

As for COMPOSE, this is a common way to create a "new" function from two existing functions; you could define a function in the Name Manager that is the combination of the two existing functions but this is 1) redundant to a certain degree and 2) time-consuming if you need to compose many different functions in different order. Function composition such as COMPOSE turns a set of a few simple functions into a tool-kit for more complex functions for a low amount of effort. Google for "function composition" and many math or comp sci results can help explain with more detail.

Hope this help!
 
Thanks tboulden. I did omit to put one of them in the Name Manager. Now it works perfectly.

Again, the LAMBDA-based UDFs and examples you shared are mind-blowing and incredibly powerful together with their simplicity and elegance. I'm litterally reshaping my understanding of Excel capacities with the amazing LAMBDA function thanks to you. Cheers and bravo!
 

Forum statistics

Threads
1,216,503
Messages
6,131,022
Members
449,616
Latest member
PsychoCube

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top