SCANBYROW

[tboulden]

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.

=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
	A	B	C	D	E	F	G	H	I	J
1
2		1	2	3	4
3		5	6	7	8
4		9	10	11	12
5		13	14	15	16
6
7		=SCANBYROW(,$B$2#, LAMBDA(acc,val,acc+val))					=SCANBYROW(10,$B$2#, LAMBDA(acc,val,acc+val))
8
9		1	3	6	10		11	13	16	20
10		5	11	18	26		15	21	28	36
11		9	19	30	42		19	29	40	52
12		13	27	42	58		23	37	52	68
13
14		=SCANBYCOL(,$B$2#, LAMBDA(acc,val,acc+val))					=SCANBYCOL(10,$B$2#, LAMBDA(acc,val,acc+val))
15
16		1	2	3	4		11	12	13	14
17		6	8	10	12		16	18	20	22
18		15	18	21	24		25	28	31	34
19		28	32	36	40		38	42	46	50
Sheet1

Cell Formulas
Range	Formula
B2:E5	B2	=SEQUENCE(4,4)
B7,G14,B14,G7	B7	=FORMULATEXT(B9)
B9:E12	B9	=SCANBYROW(,$B$2#, LAMBDA(acc,val,acc+val))
G9:J12	G9	=SCANBYROW(10,$B$2#, LAMBDA(acc,val,acc+val))
B16:E19	B16	=SCANBYCOL(,$B$2#, LAMBDA(acc,val,acc+val))
G16:J19	G16	=SCANBYCOL(10,$B$2#, LAMBDA(acc,val,acc+val))
Dynamic array formulas.

 

[tboulden]

SCANBYCOL

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

 

[tboulden]

Additional examples:

LAMBDA_SCANBY.xlsx
	A	B	C	D	E	F	G	H	I	J
1
2		A	B	C	D
3		E	F	G	H
4		I	J	K	L
5		M	N	O	P
6
7		=SCANBYROW(,$B$2#, LAMBDA(acc,val,acc&val))					=SCANBYROW("Test_",$B$2#, LAMBDA(acc,val,acc&val))
8
9		A	AB	ABC	ABCD		Test_A	Test_AB	Test_ABC	Test_ABCD
10		E	EF	EFG	EFGH		Test_E	Test_EF	Test_EFG	Test_EFGH
11		I	IJ	IJK	IJKL		Test_I	Test_IJ	Test_IJK	Test_IJKL
12		M	MN	MNO	MNOP		Test_M	Test_MN	Test_MNO	Test_MNOP
13
14		=SCANBYCOL(,$B$2#, LAMBDA(acc,val,val&acc))					=SCANBYCOL("_Test",$B$2#, LAMBDA(acc,val,val&acc))
15
16		A	B	C	D		A_Test	B_Test	C_Test	D_Test
17		EA	FB	GC	HD		EA_Test	FB_Test	GC_Test	HD_Test
18		IEA	JFB	KGC	LHD		IEA_Test	JFB_Test	KGC_Test	LHD_Test
19		MIEA	NJFB	OKGC	PLHD		MIEA_Test	NJFB_Test	OKGC_Test	PLHD_Test
Alpha

Cell Formulas
Range	Formula
B2:E5	B2	=CHAR(SEQUENCE(4,4,65,1))
B7,G14,B14,G7	B7	=FORMULATEXT(B9)
B9:E12	B9	=SCANBYROW(,$B$2#, LAMBDA(acc,val,acc&val))
G9:J12	G9	=SCANBYROW("Test_",$B$2#, LAMBDA(acc,val,acc&val))
B16:E19	B16	=SCANBYCOL(,$B$2#, LAMBDA(acc,val,val&acc))
G16:J19	G16	=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
	A	B	C	D	E	F	G	H	I
1						COMPOSE := LAMBDA(f,g,LAMBDA(x,g(f(x))))
2
3						CHOOSE functions array
4	5	6	3	6		IDENTITY	ADDONE	MINUSTHREE	TIMESTWO
5	2	4	6	18		MINUSTHREE	TIMESTWO	ADDTWO	TIMESTHREE
6	7	7	8	5		ADDTWO	IDENTITY	ADDONE	MINUSTHREE
7	15	12	12	24		TIMESTHREE	MINUSTHREE	IDENTITY	TIMESTWO
8
9						Composed across the row
10						IDENTITY	ADDONE(IDENTITY)	MINUSTHREE(ADDONE(IDENTITY))	TIMESTWO(MINUSTHREE(ADDONE(IDENTITY)))
11						MINUSTHREE	TIMESTWO(MINUSTHREE)	ADDTWO(TIMESTWO(MINUSTHREE))	TIMESTHREE(ADDTWO(TIMESTWO(MINUSTHREE)))
12						ADDTWO	IDENTITY(ADDTWO)	ADDONE(IDENTITY(ADDTWO))	MINUSTHREE(ADDONE(IDENTITY(ADDTWO)))
13						TIMESTHREE	MINUSTHREE(TIMESTHREE)	IDENTITY(MINUSTHREE(TIMESTHREE))	TIMESTWO(IDENTITY(MINUSTHREE(TIMESTHREE)))
14
15						Composed down the column
16	5	6	2	10		IDENTITY	ADDONE	MINUSTHREE	TIMESTWO
17	2	12	4	30		MINUSTHREE(IDENTITY)	TIMESTWO(ADDONE)	ADDTWO(MINUSTHREE)	TIMESTHREE(TIMESTWO)
18	4	12	5	27		ADDTWO(MINUSTHREE(IDENTITY))	IDENTITY(TIMESTWO(ADDONE))	ADDONE(ADDTWO(MINUSTHREE))	MINUSTHREE(TIMESTHREE(TIMESTWO))
19	12	9	5	54		TIMESTHREE(ADDTWO(MINUSTHREE(IDENTITY)))	MINUSTHREE(IDENTITY(TIMESTWO(ADDONE)))	IDENTITY(ADDONE(ADDTWO(MINUSTHREE)))	TIMESTWO(MINUSTHREE(TIMESTHREE(TIMESTWO)))
COMPOSE

Cell Formulas
Range	Formula
A4:D7	A4	=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:I7	F4	=CHOOSE({1,2,3,4;3,4,5,6;5,1,2,3;6,3,1,4},"IDENTITY","ADDONE","MINUSTHREE","TIMESTWO","ADDTWO","TIMESTHREE")
F10:I13	F10	=SCANBYROW(,F4#,LAMBDA(acc,fn,fn&"("&acc&")"))
A16:D19	A16	=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:I19	F16	=SCANBYCOL(,F4#,LAMBDA(acc,fn,fn&"("&acc&")"))
Dynamic array formulas.

 

[vincethesun]

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
	A	B	C	D	E	F	G	H	I
1						COMPOSE := LAMBDA(f,g,LAMBDA(x,g(f(x))))
2
3						CHOOSE functions array
4	#VALUE!	#VALUE!	#VALUE!	#VALUE!		IDENTITY	ADDONE	MINUSTHREE	TIMESTWO
5	#VALUE!	#VALUE!	#VALUE!	#VALUE!		MINUSTHREE	TIMESTWO	ADDTWO	TIMESTHREE
6	#VALUE!	#VALUE!	#VALUE!	#VALUE!		ADDTWO	IDENTITY	ADDONE	MINUSTHREE
7	#VALUE!	#VALUE!	#VALUE!	#VALUE!		TIMESTHREE	MINUSTHREE	IDENTITY	TIMESTWO
8
9						Composed across the row
10						IDENTITY	ADDONE(IDENTITY)	MINUSTHREE(ADDONE(IDENTITY))	TIMESTWO(MINUSTHREE(ADDONE(IDENTITY)))
11						MINUSTHREE	TIMESTWO(MINUSTHREE)	ADDTWO(TIMESTWO(MINUSTHREE))	TIMESTHREE(ADDTWO(TIMESTWO(MINUSTHREE)))
12						ADDTWO	IDENTITY(ADDTWO)	ADDONE(IDENTITY(ADDTWO))	MINUSTHREE(ADDONE(IDENTITY(ADDTWO)))
13						TIMESTHREE	MINUSTHREE(TIMESTHREE)	IDENTITY(MINUSTHREE(TIMESTHREE))	TIMESTWO(IDENTITY(MINUSTHREE(TIMESTHREE)))
14
15						Composed down the column
16	#NAME?	#NAME?	#NAME?	#NAME?		IDENTITY	TIMESTWO	IDENTITY	IDENTITY
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
Range	Formula
A4:D7	A4	=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:I7	F4	=CHOOSE({1,2,3,4;3,4,5,6;5,1,2,3;6,3,1,4},"IDENTITY","ADDONE","MINUSTHREE","TIMESTWO","ADDTWO","TIMESTHREE")
F10:I13	F10	=SCANBYROW(,F4#,LAMBDA(acc,fn,fn&"("&acc&")"))
A16:D19	A16	=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:I19	F16	=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.

 

[tboulden]

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!

 

[vincethesun]

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!

 

[ExcelToDAX]

@tboulden YOU ARE A SUPERHERO!!!! LEGENDARY!!!!!!!!!!!

 

[Xlambda]

These are amazing designs, but…
…when comes to combining lambda helper techniques, thunks , nested lambdas, time complexity should be taken in consideration because can increase exponentially, decreasing the performance also exponentially.
Believe it or not, 3 hours compared with 1 second, this is the proportion.
From the beginning , 3 y ago, me and tboulden were comparing formulas here : ASCAN
Lessons learned after all these years of Excel real life tasks ; the golden rules of lambda helper environment:

- There is always a more efficient solution. And the best one is the one that can work without them at all.
- Always test your solutions for time complexity, predicting the largest data set for your scenario.
- If not too many repetitive tasks, minimalistic formulas can be a lot faster than a custom made lambda based on the same concept design.
An extra argument or a single tiny extra not needed calculation can slow down the entire algorithm drastically. (an extra IF doing nothing in a lambda, for a specific task, slowed down an algorithm 3 times)
- Instead of complex and slow, better simple and fast.

On this case, SCAN already scans, by its default nature, doesn't need to spill or shrink or pass values, so the only thing we need to scan byrow is to reset the initial value at the beginning of each row.
It's like a SCAN but with a "manual" shift transmission, still as fast as SCAN
I've always used this concept ASCAN for this kind of tasks since 2021 in endless scenarios as task adapted formulas.
Latest was for Excel Off The Grid. Advanced Excel Functions | BYROW vs MAP vs SCAN vs REDUCE | Excel Off The Grid
Anyhow , whomever needs lambdas this is how they look:
I will publish, hopefully, at ASCAN, if anybody needs, lambda that does it all, regular scan row/clm direction, or/and by each row/clm. Or CSCAN lambdas , Consecutive conditional scanning , pair, triple or n elements scanning, for vectors or arrays.

SCANR(v₀, ▦, λ) Scan ByRow

=LAMBDA(v₀, ▦, λ,
    LET(
        c, COLUMNS(▦),
        SCAN(0, SEQUENCE(ROWS(▦), c), LAMBDA(v, i, LET(m, MOD(i - 1, c), q, QUOTIENT(i - 1, c) + 1, x, INDEX(▦, q, m + 1), IF(m, λ(v, x), IF(ISOMITTED(v₀), x, λ(v₀, x))))))
    )
)

SCANC(v₀, ▦, λ) Scan ByCol (scan and scanr are double transpose compatible)

=LAMBDA(v₀,▦,λ,TRANSPOSE(SCANR(v₀,TRANSPOSE(▦),λ)))

v₀: initial value ; ▦: array ; λ: fn(x,y) function of 2 var like in regular SCAN
Some other timing results and more tests:

SCANBY.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1
2		▦												=SCANBYROW(0,B4#,SUM)										n rows	timing results
3		=SEQUENCE(1000000,10)											=SCANR(0,B4#,SUM)											seq(n,10)	SCANBYROW	SCANR
4		1	2	3	4	5	6	7	8	9	10		1	3	6	10	15	21	28	36	45	55		10,000	16m27s	0s
5		11	12	13	14	15	16	17	18	19	20		11	23	36	50	65	81	98	116	135	155		20,000	01h16m32s	1s
6		21	22	23	24	25	26	27	28	29	30		21	43	66	90	115	141	168	196	225	255		30,000	03h18m48s	1s
7		31	32	33	34	35	36	37	38	39	40		31	63	96	130	165	201	238	276	315	355		100,000	???	2s
8		41	42	43	44	45	46	47	48	49	50		41	83	126	170	215	261	308	356	405	455		1,000,000	???	15s
9		51	52	53	54	55	56	57	58	59	60		51	103	156	210	265	321	378	436	495	555
10		61	62	63	64	65	66	67	68	69	70		61	123	186	250	315	381	448	516	585	655		=ROWS(B4#)
11		71	72	73	74	75	76	77	78	79	80		71	143	216	290	365	441	518	596	675	755		1000000
12		81	82	83	84	85	86	87	88	89	90		81	163	246	330	415	501	588	676	765	855
13		91	92	93	94	95	96	97	98	99	100		91	183	276	370	465	561	658	756	855	955		=ROWS(M4#)
14		101	102	103	104	105	106	107	108	109	110		101	203	306	410	515	621	728	836	945	1055		1000000
15		111	112	113	114	115	116	117	118	119	120		111	223	336	450	565	681	798	916	1035	1155
16		121	122	123	124	125	126	127	128	129	130		121	243	366	490	615	741	868	996	1125	1255
Sheet1

Cell Formulas
Range	Formula
B3,X13,X10,M3	B3	=FORMULATEXT(B4)
B4:K1000003	B4	=SEQUENCE(1000000,10)
M4:V1000003	M4	=SCANR(0,B4#,SUM)
X11	X11	=ROWS(B4#)
X14	X14	=ROWS(M4#)
Dynamic array formulas.

Lambda Functions
Name	Formula
SCANBY	=LAMBDA(v₀,▦,λ,[·⇉],[↕],LET(a,IF(↕,TRANSPOSE(▦),▦),c,COLUMNS(a),s,IF(·⇉,SCAN(0,SEQUENCE(ROWS(▦),c),LAMBDA(v,i,LET(m,MOD(i-1,c),q,QUOTIENT(i-1,c)+1,x,INDEX(▦,q,m+1),IF(m,λ(v,x),IF(ISOMITTED(v₀),x,λ(v₀,x)))))),SCAN(v₀,a,λ)),IF(↕,TRANSPOSE(s),s)))
SCANBYROW	=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 ) ) ) ) )
SCANC	=LAMBDA(v₀,▦,λ,TRANSPOSE(SCANR(v₀,TRANSPOSE(▦),λ)))
SCANR	=LAMBDA(v₀,▦,λ,LET(c,COLUMNS(▦),SCAN(0,SEQUENCE(ROWS(▦),c),LAMBDA(v,i,LET(m,MOD(i-1,c),q,QUOTIENT(i-1,c)+1,x,INDEX(▦,q,m+1),IF(m,λ(v,x),IF(ISOMITTED(v₀),x,λ(v₀,x))))))))
TIMEIT	=LAMBDA(·🕑,ƒ,IF(ROWS(ƒ),TEXT(MOD(NOW()-TODAY()-·🕑,1),"[hh]:mm:ss.000")))

SCANBY.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1
2							SCAN				SCANR				SCANC				dblT SCAN
3						=SCAN(,B4:D6,CONCAT)				=SCANR(,B4:D6,CONCAT)				=SCANC(,B4:D6,CONCAT)				=TRANSPOSE(SCAN(,TRANSPOSE(B4:D6),CONCAT))
4		a	b	c		a	ab	abc		a	ab	abc		a	b	c		a	adgb	adgbehc
5		d	e	f		abcd	abcde	abcdef		d	de	def		ad	be	cf		ad	adgbe	adgbehcf
6		g	h	i		abcdefg	abcdefgh	abcdefghi		g	gh	ghi		adg	beh	cfi		adg	adgbeh	adgbehcfi
7
8						=SCAN(,B4:D6,LAMBDA(v,i,i&v))				=SCANR(,B4:D6,LAMBDA(v,i,i&v))				=SCANC(,B4:D6,LAMBDA(v,i,i&v))				=TRANSPOSE(SCAN(,TRANSPOSE(B4:D6),LAMBDA(v,i,i&v)))
9						a	ba	cba		a	ba	cba		a	b	c		a	bgda	chebgda
10						dcba	edcba	fedcba		d	ed	fed		da	eb	fc		da	ebgda	fchebgda
11						gfedcba	hgfedcba	ihgfedcba		g	hg	ihg		gda	heb	ifc		gda	hebgda	ifchebgda
12
13						=SCAN("|",B4:D6,CONCAT)				=SCANR("|",B4:D6,CONCAT)				=SCANC("|",B4:D6,CONCAT)				=TRANSPOSE(SCAN("|",TRANSPOSE(B4:D6),CONCAT))
14						|a	|ab	|abc		|a	|ab	|abc		|a	|b	|c		|a	|adgb	|adgbehc
15						|abcd	|abcde	|abcdef		|d	|de	|def		|ad	|be	|cf		|ad	|adgbe	|adgbehcf
16						|abcdefg	|abcdefgh	|abcdefghi		|g	|gh	|ghi		|adg	|beh	|cfi		|adg	|adgbeh	|adgbehcfi
17
18						=SCAN("|",B4:D6,LAMBDA(v,i,i&v))				=SCANR("|",B4:D6,LAMBDA(v,i,i&v))				=SCANC("|",B4:D6,LAMBDA(v,i,i&v))				=TRANSPOSE(SCAN("|",TRANSPOSE(B4:D6),LAMBDA(v,i,i&v)))
19						a|	ba|	cba|		a|	ba|	cba|		a|	b|	c|		a|	bgda|	chebgda|
20						dcba|	edcba|	fedcba|		d|	ed|	fed|		da|	eb|	fc|		da|	ebgda|	fchebgda|
21						gfedcba|	hgfedcba|	ihgfedcba|		g|	hg|	ihg|		gda|	heb|	ifc|		gda|	hebgda|	ifchebgda|
22
23
24						=SCAN(,B25#,LAMBDA(v,i,v+2*i))				=SCANR(,B25#,LAMBDA(v,i,v+2*i))				=SCANC(,B25#,LAMBDA(v,i,v+2*i))				=TRANSPOSE(SCAN(,TRANSPOSE(B25#),LAMBDA(v,i,v+2*i)))
25		2	4	6		2	10	22		2	10	22		2	4	6		2	54	118
26		8	10	12		38	58	82		8	28	52		18	24	30		18	74	142
27		14	16	18		110	142	178		14	46	82		46	56	66		46	106	178
28
29						=SCAN(,B25#,LAMBDA(v,i,v*2+i))				=SCANR(,B25#,LAMBDA(v,i,v*2+i))				=SCANC(,B25#,LAMBDA(v,i,v*2+i))				=TRANSPOSE(SCAN(,TRANSPOSE(B25#),LAMBDA(v,i,v*2+i)))
30						2	8	22		2	8	22		2	4	6		2	80	718
31						52	114	240		8	26	64		12	18	24		12	170	1448
32						494	1004	2026		14	44	106		38	52	66		38	356	2914
33
34						=SCAN(10,B25#,LAMBDA(v,i,v+2*i))				=SCANR(10,B25#,LAMBDA(v,i,v+2*i))				=SCANC(10,B25#,LAMBDA(v,i,v+2*i))				=TRANSPOSE(SCAN(10,TRANSPOSE(B25#),LAMBDA(v,i,v+2*i)))
35						14	22	34		14	22	34		14	18	22		14	66	130
36						50	70	94		26	46	70		30	38	46		30	86	154
37						122	154	190		38	70	106		58	70	82		58	118	190
38
39						=SCAN(10,B25#,LAMBDA(v,i,v*2+i))				=SCANR(10,B25#,LAMBDA(v,i,v*2+i))				=SCANC(10,B25#,LAMBDA(v,i,v*2+i))				=TRANSPOSE(SCAN(10,TRANSPOSE(B25#),LAMBDA(v,i,v*2+i)))
40						22	48	102		22	48	102		22	24	26		22	240	1998
41						212	434	880		28	66	144		52	58	64		52	490	4008
42						1774	3564	7146		34	84	186		118	132	146		118	996	8034
43
Sheet2

Cell Formulas
Range	Formula
F3,R39,N39,J39,F39,R34,N34,J34,F34,R29,N29,J29,F29,R24,N24,J24,F24,R18,N18,J18,F18,R13,N13,J13,F13,R8,N8,J8,F8,R3,N3,J3	F3	=FORMULATEXT(F4)
F4:H6	F4	=SCAN(,B4:D6,CONCAT)
J4:L6	J4	=SCANR(,B4:D6,CONCAT)
N4:P6	N4	=SCANC(,B4:D6,CONCAT)
R4:T6	R4	=TRANSPOSE(SCAN(,TRANSPOSE(B4:D6),CONCAT))
F9:H11	F9	=SCAN(,B4:D6,LAMBDA(v,i,i&v))
J9:L11	J9	=SCANR(,B4:D6,LAMBDA(v,i,i&v))
N9:P11	N9	=SCANC(,B4:D6,LAMBDA(v,i,i&v))
R9:T11	R9	=TRANSPOSE(SCAN(,TRANSPOSE(B4:D6),LAMBDA(v,i,i&v)))
F14:H16	F14	=SCAN("|",B4:D6,CONCAT)
J14:L16	J14	=SCANR("|",B4:D6,CONCAT)
N14:P16	N14	=SCANC("|",B4:D6,CONCAT)
R14:T16	R14	=TRANSPOSE(SCAN("|",TRANSPOSE(B4:D6),CONCAT))
F19:H21	F19	=SCAN("|",B4:D6,LAMBDA(v,i,i&v))
J19:L21	J19	=SCANR("|",B4:D6,LAMBDA(v,i,i&v))
N19:P21	N19	=SCANC("|",B4:D6,LAMBDA(v,i,i&v))
R19:T21	R19	=TRANSPOSE(SCAN("|",TRANSPOSE(B4:D6),LAMBDA(v,i,i&v)))
B25:D27	B25	=SEQUENCE(3,3,2,2)
F25:H27	F25	=SCAN(,B25#,LAMBDA(v,i,v+2*i))
J25:L27	J25	=SCANR(,B25#,LAMBDA(v,i,v+2*i))
N25:P27	N25	=SCANC(,B25#,LAMBDA(v,i,v+2*i))
R25:T27	R25	=TRANSPOSE(SCAN(,TRANSPOSE(B25#),LAMBDA(v,i,v+2*i)))
F30:H32	F30	=SCAN(,B25#,LAMBDA(v,i,v*2+i))
J30:L32	J30	=SCANR(,B25#,LAMBDA(v,i,v*2+i))
N30:P32	N30	=SCANC(,B25#,LAMBDA(v,i,v*2+i))
R30:T32	R30	=TRANSPOSE(SCAN(,TRANSPOSE(B25#),LAMBDA(v,i,v*2+i)))
F35:H37	F35	=SCAN(10,B25#,LAMBDA(v,i,v+2*i))
J35:L37	J35	=SCANR(10,B25#,LAMBDA(v,i,v+2*i))
N35:P37	N35	=SCANC(10,B25#,LAMBDA(v,i,v+2*i))
R35:T37	R35	=TRANSPOSE(SCAN(10,TRANSPOSE(B25#),LAMBDA(v,i,v+2*i)))
F40:H42	F40	=SCAN(10,B25#,LAMBDA(v,i,v*2+i))
J40:L42	J40	=SCANR(10,B25#,LAMBDA(v,i,v*2+i))
N40:P42	N40	=SCANC(10,B25#,LAMBDA(v,i,v*2+i))
R40:T42	R40	=TRANSPOSE(SCAN(10,TRANSPOSE(B25#),LAMBDA(v,i,v*2+i)))
Dynamic array formulas.

Lambda Functions
Name	Formula
SCANBY	=LAMBDA(v₀,▦,λ,[·⇉],[↕],LET(a,IF(↕,TRANSPOSE(▦),▦),c,COLUMNS(a),s,IF(·⇉,SCAN(0,SEQUENCE(ROWS(▦),c),LAMBDA(v,i,LET(m,MOD(i-1,c),q,QUOTIENT(i-1,c)+1,x,INDEX(▦,q,m+1),IF(m,λ(v,x),IF(ISOMITTED(v₀),x,λ(v₀,x)))))),SCAN(v₀,a,λ)),IF(↕,TRANSPOSE(s),s)))
SCANBYROW	=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 ) ) ) ) )
SCANC	=LAMBDA(v₀,▦,λ,TRANSPOSE(SCANR(v₀,TRANSPOSE(▦),λ)))
SCANR	=LAMBDA(v₀,▦,λ,LET(c,COLUMNS(▦),SCAN(0,SEQUENCE(ROWS(▦),c),LAMBDA(v,i,LET(m,MOD(i-1,c),q,QUOTIENT(i-1,c)+1,x,INDEX(▦,q,m+1),IF(m,λ(v,x),IF(ISOMITTED(v₀),x,λ(v₀,x))))))))
TIMEIT	=LAMBDA(·🕑,ƒ,IF(ROWS(ƒ),TEXT(MOD(NOW()-TODAY()-·🕑,1),"[hh]:mm:ss.000")))

 

[djclements]

@Xlambda Good stuff! It's nice to see the algorithms for this, to be used with INDEX.

Another approach I've seen is to use the EXPAND function to add a column to the array and use that as the reset point for each row, then DROP the additional column afterwards. This method is easy enough to customize on a case-by-case basis, but as a generalized lambda function it might look something like this:

SCANR:
=LAMBDA(init_val,arr,func,LET(rs,CHAR(30),DROP(SCAN(init_val,EXPAND(arr,,COLUMNS(arr)+1,rs),LAMBDA(a,v,IF(v=rs,v,func(IF(a=rs,init_val,a),v)))),,-1)))

SCANC:
=LAMBDA(init_val,arr,func,LET(rs,CHAR(30),DROP(TRANSPOSE(SCAN(init_val,TRANSPOSE(EXPAND(arr,ROWS(arr)+1,,rs)),LAMBDA(a,v,IF(v=rs,v,func(IF(a=rs,init_val,a),v))))),-1)))

Alternatively, recursive lambda functions could be defined using bisection to work around stack limits:

SCANBiROW:
=LAMBDA(init_val,arr,func,
    IF(
        ROWS(arr) = 1,
        SCAN(init_val, arr, func),
        VSTACK(
            SCANBiROW(init_val, TAKE(arr, ROWS(arr) / 2), func),
            SCANBiROW(init_val, DROP(arr, ROWS(arr) / 2), func)
        )
    )
)

SCANBiCOL:
=LAMBDA(init_val,arr,func,
    IF(
        COLUMNS(arr) = 1,
        SCAN(init_val, arr, func),
        HSTACK(
            SCANBiCOL(init_val, TAKE(arr,, COLUMNS(arr) / 2), func),
            SCANBiCOL(init_val, DROP(arr,, COLUMNS(arr) / 2), func)
        )
    )
)

Source: Recursive LAMBDA implementation of Excel's REDUCE function

 

[Xlambda]

Another reason why this place is the best excel laboratory in the world. It's so easy to post examples next to any custom made or copy-pasted formulas from other sources.
Every new array resizing functions are built on top of INDEX.
First test, first function. Function wrong.
I have no time for speed tests.

SCANBY.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1
2										regular SCAN function for each row => no doubts that this is the correct result
3
4		78	10	45	80	30	80	52		78	98	188	348	408	568	672	=SCAN(,B4:H4,LAMBDA(v,i,v+2*i))
5		81	45	72	58	96	53	70		81	171	315	431	623	729	869	=SCAN(,B5:H5,LAMBDA(v,i,v+2*i))
6		10	30	90	81	58	99	14		10	70	250	412	528	726	754	=SCAN(,B6:H6,LAMBDA(v,i,v+2*i))
7		26	94	94	50	13	72	60		26	214	402	502	528	672	792	=SCAN(,B7:H7,LAMBDA(v,i,v+2*i))
8		63	61	44	46	97	34	64		63	185	273	365	559	627	755	=SCAN(,B8:H8,LAMBDA(v,i,v+2*i))
9
10							=AND(J4:P8=J11#)			=SCANR(,B4:H8,LAMBDA(v,i,v+2*i))
11							TRUE			78	98	188	348	408	568	672
12										81	171	315	431	623	729	869
13										10	70	250	412	528	726	754
14										26	214	402	502	528	672	792
15										63	185	273	365	559	627	755
16					djclement's function
17				J18:	=LAMBDA(init_val,arr,func,LET(rs,CHAR(30),DROP(SCAN(init_val,EXPAND(arr,,COLUMNS(arr)+1,rs),LAMBDA(a,v,IF(v=rs,v,func(IF(a=rs,init_val,a),v)))),,-1)))(,B4:H8,LAMBDA(v,i,v+2*i))
18										78	98	188	348	408	568	672
19					=AND(J18#=J4:P8)					162	252	396	512	704	810	950
20					FALSE	=>	wrong results			20	80	260	422	538	736	764
21										52	240	428	528	554	698	818
22										126	248	336	428	622	690	818
23
24		Recursion solution concept logic flaw: Why to use a solution that has limits that can be overrided or not, if I can use a solution that has no limits, is similar with the native one and is lighting fast.
25		Recursion is so nice when we relay on unpredictable exit conditions.
26
Sheet4

Cell Formulas
Range	Formula
J4:P8	J4	=SCAN(,B4:H4,LAMBDA(v,i,v+2*i))
Q4:Q8	Q4	=FORMULATEXT(J4)
G10,J10,E19	G10	=FORMULATEXT(G11)
G11	G11	=AND(J4:P8=J11#)
J11:P15	J11	=SCANR(,B4:H8,LAMBDA(v,i,v+2*i))
E17	E17	=FORMULATEXT(J18)
J18:P22	J18	=LAMBDA(init_val,arr,func,LET(rs,CHAR(30),DROP(SCAN(init_val,EXPAND(arr,,COLUMNS(arr)+1,rs),LAMBDA(a,v,IF(v=rs,v,func(IF(a=rs,init_val,a),v)))),,-1)))(,B4:H8,LAMBDA(v,i,v+2*i))
E20	E20	=AND(J18#=J4:P8)
Dynamic array formulas.

Lambda Functions
Name	Formula
SCANBY	=LAMBDA(v₀,▦,λ,[·⇉],[↕],LET(a,IF(↕,TRANSPOSE(▦),▦),c,COLUMNS(a),s,IF(·⇉,SCAN(0,SEQUENCE(ROWS(▦),c),LAMBDA(v,i,LET(m,MOD(i-1,c),q,QUOTIENT(i-1,c)+1,x,INDEX(▦,q,m+1),IF(m,λ(v,x),IF(ISOMITTED(v₀),x,λ(v₀,x)))))),SCAN(v₀,a,λ)),IF(↕,TRANSPOSE(s),s)))
SCANBYROW	=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 ) ) ) ) )
SCANC	=LAMBDA(v₀,▦,λ,TRANSPOSE(SCANR(v₀,TRANSPOSE(▦),λ)))
SCANR	=LAMBDA(v₀,▦,λ,LET(c,COLUMNS(▦),SCAN(0,SEQUENCE(ROWS(▦),c),LAMBDA(v,i,LET(m,MOD(i-1,c),q,QUOTIENT(i-1,c)+1,x,INDEX(▦,q,m+1),IF(m,λ(v,x),IF(ISOMITTED(v₀),x,λ(v₀,x))))))))
TIMEIT	=LAMBDA(·🕑,ƒ,IF(ROWS(ƒ),TEXT(MOD(NOW()-TODAY()-·🕑,1),"[hh]:mm:ss.000")))