ASCAN

[Xlambda]

ASCAN, Array 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.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1						d,omitted (by array)					d,-1 (by rows)					d,1 (by clms)
2	sample 1			a		=ASCAN(A3:D6)					=ASCAN(A3:D6,-1)					=ASCAN(A3:D6,1)
3	1	2	3	4		1	3	6	10		1	3	6	10		1	2	3	4
4	5	6	7	8		15	21	28	36		5	11	18	26		6	8	10	12
5	9	10	11	12		45	55	66	78		9	19	30	42		15	18	21	24
6	13	14	15	16		91	105	120	136		13	27	42	58		28	32	36	40
7
8						d,omitted (by array)					d,-1 (by rows)					d,1 (by clms)
9	sample 2			a		=ASCAN(A10#)					=ASCAN(A10#,-1)					=ASCAN(A10#,1)
10	1	2	3	4		1	3	6	10		1	3	6	10		1	2	3	4
11	5	6	7	8		15	21	28	36		5	11	18	26		6	8	10	12
12	9	10	11	12		45	55	66	78		9	19	30	42		15	18	21	24
13	13	14	15	16		91	105	120	136		13	27	42	58		28	32	36	40
14	17	18	19	20		153	171	190	210		17	35	54	74		45	50	55	60
15	21	22	23	24		231	253	276	300		21	43	66	90		66	72	78	84
16
17	Note: Did not set an extra argument for any kind of "initial value", since the only thing it does is too simple,
18	adds a constant value to the final outcome , like in 10+ASCAN(a)
19
ASCAN post

Cell Formulas
Range	Formula
F2,P9,K9,F9,P2,K2	F2	=FORMULATEXT(F3)
F3:I6	F3	=ASCAN(A3:D6)
K3:N6	K3	=ASCAN(A3:D6,-1)
P3:S6	P3	=ASCAN(A3:D6,1)
A10:D15	A10	=SEQUENCE(6,4)
F10:I15	F10	=ASCAN(A10#)
K10:N15	K10	=ASCAN(A10#,-1)
P10:S15	P10	=ASCAN(A10#,1)
Dynamic array formulas.

 

[Xlambda]

LAMBDA 1.1.4.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	AB	AC
1	Task: scan largest n values of each row of the sample array
2									extract largest 4 values							=INDEX(AFUSBYROW(A5:G14,,,-1),SEQUENCE(10),SEQUENCE(,C3))
3	largest n=		4						row by row							↓	alternative function					result to get:
4	sample 10 rows								=LARGE(A5:G5,SEQUENCE(,$C$3))							↓	single cell					=SCAN(0,I5:L14,LAMBDA(v,a,v+a))
5	8	19	9	10	6	8	9		19	10	9	9				19	10	9	9			19	29	38	47
6	7	7	16	18	4	17	4		18	17	16	7				18	17	16	7			65	82	98	105
7	3	7	8	2	5	10	18		18	10	8	7				18	10	8	7			123	133	141	148
8	10	14	7	11	15	20	1		20	15	14	11				20	15	14	11			168	183	197	208
9	2	9	1	12	8	11	12		12	12	11	9				12	12	11	9			220	232	243	252
10	5	2	11	2	11	13	20		20	13	11	11				20	13	11	11			272	285	296	307
11	1	8	1	14	16	7	11		16	14	11	8				16	14	11	8			323	337	348	356
12	10	9	10	2	12	5	7		12	10	10	9				12	10	10	9			368	378	388	397
13	6	19	11	9	5	19	14		19	19	14	11				19	19	14	11			416	435	449	460
14	11	20	11	14	2	20	15		20	20	15	14				20	20	15	14			480	500	515	529
15
16									1st 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))
18									19	29	38	47
19		other function							65	82	98	105
20		AFUSBYROW							123	133	141	148
21									168	183	197	208
22									220	232	243	252
23									272	285	296	307
24									323	337	348	356
25									368	378	388	397
26									416	435	449	460
27									480	500	515	529
28
29									2nd way, 1 lambda helper, SCAN
30									=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))))
31									19	29	38	47
32									65	82	98	105
33									123	133	141	148
34									168	183	197	208
35									220	232	243	252
36									272	285	296	307
37									323	337	348	356
38									368	378	388	397
39									416	435	449	460
40									480	500	515	529
41
ASCAN post 11

Cell Formulas
Range	Formula
P2	P2	=FORMULATEXT(P5)
I4,I30,I17,V4	I4	=FORMULATEXT(I5)
P5:S14	P5	=INDEX(AFUSBYROW(A5:G14,,,-1),SEQUENCE(10),SEQUENCE(,C3))
V5:Y14	V5	=SCAN(0,I5:L14,LAMBDA(v,a,v+a))
I5:L14	I5	=LARGE(A5:G5,SEQUENCE(,$C$3))
I18:L27	I18	=SCAN(0,MAKEARRAY(10,C3,LAMBDA(r,c,LET(x,INDEX(A5:G14,r,),LARGE(x,c)))),LAMBDA(v,a,v+a))
I31:L40	I31	=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]

LAMBDA 1.1.4.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		largest n=		4							Task: scan whole array by each product's largest n values
2	sample 10 rows			3	products						results to get step by step
3
4	unique prod.
5	=TRANSPOSE(UNIQUE(A8:A17))										filter prod A
6	A	B	C								=--(A8:A17=INDEX(A6#,1))										1A
7	prod.										↓		=K8*LARGE(B8:H8,SEQUENCE(,$D$1))					=K8#*ASCAN(M8:P17)
8	A	8	19	9	10	6	8	9			1		19	10	9	9		19	29	38	47
9	B	7	7	16	18	4	17	4			0		0	0	0	0		0	0	0	0
10	A	3	7	8	2	5	10	18			1		18	10	8	7		65	75	83	90
11	C	10	14	7	11	15	20	1			0		0	0	0	0		0	0	0	0
12	C	2	9	1	12	8	11	12			0		0	0	0	0		0	0	0	0
13	A	5	2	11	2	11	13	20			1		20	13	11	11		110	123	134	145
14	B	1	8	1	14	16	7	11			0		0	0	0	0		0	0	0	0
15	A	10	9	10	2	12	5	7			1		12	10	10	9		157	167	177	186
16	C	6	19	11	9	5	19	14			0		0	0	0	0		0	0	0	0
17	B	11	20	11	14	2	20	15			0		0	0	0	0		0	0	0	0
18
19											filter prod B
20		result we should obtain									=--(A8:A17=INDEX(A6#,2))										2B
21		1A+2B+3C									↓		=K22*LARGE(B8:H8,SEQUENCE(,$D$1))					=K22#*ASCAN(M22:P31)
22		=R8#+R22#+R36#									0		0	0	0	0		0	0	0	0
23		19	29	38	47						1		18	17	16	7		18	35	51	58
24		18	35	51	58						0		0	0	0	0		0	0	0	0
25		65	75	83	90						0		0	0	0	0		0	0	0	0
26		20	35	49	60						0		0	0	0	0		0	0	0	0
27		72	84	95	104						0		0	0	0	0		0	0	0	0
28		110	123	134	145						1		16	14	11	8		74	88	99	107
29		74	88	99	107						0		0	0	0	0		0	0	0	0
30		157	167	177	186						0		0	0	0	0		0	0	0	0
31		123	142	156	167						1		20	20	15	14		127	147	162	176
32		127	147	162	176
33											filter prod C
34											=--(A8:A17=INDEX(A6#,3))										3C
35											↓		=K36*LARGE(B8:H8,SEQUENCE(,$D$1))					=K36#*ASCAN(M36:P45)
36											0		0	0	0	0		0	0	0	0
37											0		0	0	0	0		0	0	0	0
38											0		0	0	0	0		0	0	0	0
39											1		20	15	14	11		20	35	49	60
40											1		12	12	11	9		72	84	95	104
41											0		0	0	0	0		0	0	0	0
42											0		0	0	0	0		0	0	0	0
43											0		0	0	0	0		0	0	0	0
44											1		19	19	14	11		123	142	156	167
45											0		0	0	0	0		0	0	0	0
46
ASCAN post 12

Cell Formulas
Range	Formula
A5,M35,R35,B22,M21,R21,M7,R7	A5	=FORMULATEXT(A6)
A6:C6	A6	=TRANSPOSE(UNIQUE(A8:A17))
K6,K34,K20	K6	=FORMULATEXT(K8)
K8:K17	K8	=--(A8:A17=INDEX(A6#,1))
R8:U17,R36:U45,R22:U31	R8	=K8#*ASCAN(M8:P17)
M8:P17	M8	=K8*LARGE(B8:H8,SEQUENCE(,$D$1))
K22:K31	K22	=--(A8:A17=INDEX(A6#,2))
B23:E32	B23	=R8#+R22#+R36#
M22:P31	M22	=K22*LARGE(B8:H8,SEQUENCE(,$D$1))
K36:K45	K36	=--(A8:A17=INDEX(A6#,3))
M36:P45	M36	=K36*LARGE(B8:H8,SEQUENCE(,$D$1))
Dynamic array formulas.

 

[Xlambda]

LAMBDA 1.1.4.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Task: scan whole array by each product's largest n values
2	single cell formulas 2 ways
3
4		largest n=		4
5	sample 10 rows			3	products					unique prod.
6	prod.									=UNIQUE(A7:A16)
7	A	8	19	9	10	6	8	9		A
8	B	7	7	16	18	4	17	4		B
9	A	3	7	8	2	5	10	18		C
10	C	10	14	7	11	15	20	1
11	C	2	9	1	12	8	11	12
12	A	5	2	11	2	11	13	20
13	B	1	8	1	14	16	7	11
14	A	10	9	10	2	12	5	7
15	C	6	19	11	9	5	19	14
16	B	11	20	11	14	2	20	15
17
18		1st 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)))))
20		19	29	38	47
21		18	35	51	58
22		65	75	83	90
23		20	35	49	60
24		72	84	95	104
25		110	123	134	145
26		74	88	99	107
27		157	167	177	186
28		123	142	156	167
29		127	147	162	176
30
31		2nd 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)))))
33		19	29	38	47
34		18	35	51	58
35		65	75	83	90
36		20	35	49	60
37		72	84	95	104
38		110	123	134	145
39		74	88	99	107
40		157	167	177	186
41		123	142	156	167
42		127	147	162	176
43
ASCAN post 13

Cell Formulas
Range	Formula
J6	J6	=FORMULATEXT(J7)
J7:J9	J7	=UNIQUE(A7:A16)
B20:E29	B20	=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:E42	B33	=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]

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.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Task: scan whole array by each product's largest n values
2	single cell (lambda function)
3
4	sample
5	prod.
6	A	8	19	9	10	6	8	9
7	B	7	7	16	18	4	17	4
8	A	3	7	8	2	5	10	18
9	C	10	14	7	11	15	20	1
10	C	2	9	1	12	8	11	12
11	A	5	2	11	2	11	13	20
12	B	1	8	1	14	16	7	11
13	A	10	9	10	2	12	5	7
14	C	6	19	11	9	5	19	14
15	B	11	20	11	14	2	20	15
16
17		a,B6:H15,p,A6:A15,l,4
18		=LARGESCAN(B6:H15,A6:A15,4)
19		19	29	38	47
20		18	35	51	58
21		65	75	83	90
22		20	35	49	60
23		72	84	95	104
24		110	123	134	145
25		74	88	99	107
26		157	167	177	186
27		123	142	156	167
28		127	147	162	176
29
ASCAN post 13.1

Cell Formulas
Range	Formula
B18	B18	=FORMULATEXT(B19)
B19:E28	B19	=LARGESCAN(B6:H15,A6:A15,4)
Dynamic array formulas.

 

[Xlambda]

Examples with REDUCE as main lambda helper function.

LAMBDA 1.1.4.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	AB	AC	AD	AE	AF	AG
1	Task: Filter only the rows of an array that hold unique values, alternative functions
2	dups											1.1. AFUSBYROW (f , ignored,u,1,s,omitted)											1.2. AHCLEAN(AFUSBYROW)
3	sample											=AFUSBYROW(A4:J23,,1)											=AHCLEAN(L4#)
4	25	2	25	19	20	20	3	4	9	28		25	2	19	20	3	4	9	28				4	11	10	3	28	7	14	13	16	20
5	6	12	23	13	29	19	7	4	29	8		6	12	23	13	29	19	7	4	8			19	11	4	28	26	8	29	12	20	10
6	18	27	18	25	2	8	26	25	26	22		18	27	25	2	8	26	22					19	26	30	27	8	22	1	2	23	12
7	19	18	15	19	25	19	4	1	19	23		19	18	15	25	4	1	23					18	7	3	8	25	20	9	4	14	26
8	4	11	10	3	28	7	14	13	16	20		4	11	10	3	28	7	14	13	16	20
9	15	16	18	3	21	15	17	18	8	12		15	16	18	3	21	17	8	12
10	23	23	13	22	7	17	19	2	5	2		23	13	22	7	17	19	2	5				2. recursive method seen in ARRANGEMENTS thread
11	12	4	6	27	7	19	17	27	26	4		12	4	6	27	7	19	17	26				=T_P(A4:J23)
12	7	4	17	8	8	23	6	16	28	28		7	4	17	8	23	6	16	28				4	11	10	3	28	7	14	13	16	20
13	21	21	26	8	2	16	3	11	3	9		21	26	8	2	16	3	11	9				19	11	4	28	26	8	29	12	20	10
14	5	5	2	3	20	12	3	8	13	1		5	2	3	20	12	8	13	1				19	26	30	27	8	22	1	2	23	12
15	19	11	4	28	26	8	29	12	20	10		19	11	4	28	26	8	29	12	20	10		18	7	3	8	25	20	9	4	14	26
16	29	20	17	12	3	16	12	12	28	18		29	20	17	12	3	16	28	18
17	5	14	29	5	28	16	27	24	30	5		5	14	29	28	16	27	24	30
18	8	30	7	25	30	9	19	20	19	5		8	30	7	25	9	19	20	5				other functions
19	19	26	30	27	8	22	1	2	23	12		19	26	30	27	8	22	1	2	23	12		AFUSBYROW
20	29	13	5	3	9	23	10	27	9	21		29	13	5	3	9	23	10	27	21			AHCLEAN
21	18	7	3	8	25	20	9	4	14	26		18	7	3	8	25	20	9	4	14	26		T_P
22	20	20	29	27	4	3	14	10	21	22		20	29	27	4	3	14	10	21	22
23	16	8	21	25	21	15	15	19	17	15		16	8	21	25	15	19	17
24
ASCAN post 14

Cell Formulas
Range	Formula
L3,W11,W3	L3	=FORMULATEXT(L4)
L4:U23	L4	=AFUSBYROW(A4:J23,,1)
W4:AF7	W4	=AHCLEAN(L4#)
W12:AF15	W12	=T_P(A4:J23)
Dynamic array formulas.

 

[Xlambda]

LAMBDA 1.1.4.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	AB	AC	AD	AE	AF	AG	AH	AI
1	Task: Filter only the rows of an array that holds unique values, REDUCE
2
3												step 1 clm1=a + clm2=a +….+ clm10=a
4	duplicates						rows unique values					=REDUCE(0,SEQUENCE(D5),LAMBDA(v,i,v+(INDEX(A8:J27,,i)=A8:J27)))
5	columns			10								⬇
6												⬇											step 2 sum BYROW(a')
7	sample									a		⬇									a'		=BYROW(L8#,LAMBDA(a,SUM(a)))
8	25	2	25	19	20	20	3	4	9	28		2	1	2	1	2	2	1	1	1	1		14	a''
9	6	12	23	13	29	19	7	4	29	8		1	1	1	1	2	1	1	1	2	1		12		step 3 FILTER(a,a''=clms(a))
10	18	27	18	25	2	8	26	25	26	22		2	1	2	2	1	1	2	2	2	1		16		=FILTER(A8:J27,W8#=D5)
11	19	18	15	19	25	19	4	1	19	23		4	1	1	4	1	4	1	1	4	1		22		4	11	10	3	28	7	14	13	16	20
12	4	11	10	3	28	7	14	13	16	20		1	1	1	1	1	1	1	1	1	1		10		19	11	4	28	26	8	29	12	20	10
13	15	16	18	3	21	15	17	18	8	12		2	1	2	1	1	2	1	2	1	1		14		19	26	30	27	8	22	1	2	23	12
14	23	23	13	22	7	17	19	2	5	2		2	2	1	1	1	1	1	2	1	2		14		18	7	3	8	25	20	9	4	14	26
15	12	4	6	27	7	19	17	27	26	4		1	2	1	2	1	1	1	2	1	2		14
16	7	4	17	8	8	23	6	16	28	28		1	1	1	2	2	1	1	1	2	2		14
17	21	21	26	8	2	16	3	11	3	9		2	2	1	1	1	1	2	1	2	1		14
18	5	5	2	3	20	12	3	8	13	1		2	2	1	2	1	1	2	1	1	1		14
19	19	11	4	28	26	8	29	12	20	10		1	1	1	1	1	1	1	1	1	1		10
20	29	20	17	12	3	16	12	12	28	18		1	1	1	3	1	1	3	3	1	1		16
21	5	14	29	5	28	16	27	24	30	5		3	1	1	3	1	1	1	1	1	3		16
22	8	30	7	25	30	9	19	20	19	5		1	2	1	1	2	1	2	1	2	1		14
23	19	26	30	27	8	22	1	2	23	12		1	1	1	1	1	1	1	1	1	1		10		single cell
24	29	13	5	3	9	23	10	27	9	21		1	1	1	1	2	1	1	1	2	1		12		4	11	10	3	28	7	14	13	16	20
25	18	7	3	8	25	20	9	4	14	26		1	1	1	1	1	1	1	1	1	1		10		19	11	4	28	26	8	29	12	20	10
26	20	20	29	27	4	3	14	10	21	22		2	2	1	1	1	1	1	1	1	1		12		19	26	30	27	8	22	1	2	23	12
27	16	8	21	25	21	15	15	19	17	15		1	1	2	1	2	3	3	1	1	3		18		18	7	3	8	25	20	9	4	14	26
28
29								Y24:	=FILTER(A8:J27,BYROW(REDUCE(0,SEQUENCE(D5),LAMBDA(v,i,v+(INDEX(A8:J27,,i)=A8:J27))),LAMBDA(x,SUM(x)=D5)))
30
ASCAN post 15

Cell Formulas
Range	Formula
L4	L4	=FORMULATEXT(L8)
W7,Y10	W7	=FORMULATEXT(W8)
L8:U27	L8	=REDUCE(0,SEQUENCE(D5),LAMBDA(v,i,v+(INDEX(A8:J27,,i)=A8:J27)))
W8:W27	W8	=BYROW(L8#,LAMBDA(a,SUM(a)))
Y11:AH14	Y11	=FILTER(A8:J27,W8#=D5)
Y24:AH27	Y24	=FILTER(A8:J27,BYROW(REDUCE(0,SEQUENCE(D5),LAMBDA(v,i,v+(INDEX(A8:J27,,i)=A8:J27))),LAMBDA(x,SUM(x)=D5)))
I29	I29	=FORMULATEXT(Y24)
Dynamic array formulas.

 

[Xlambda]

LAMBDA 1.1.4.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	Task: filter only rows of an array that are in ascending order, alternative functions
2
3		asc.order						1.1. all rows in asc. order						1.2. compare a=a'						1.3. whole rows comparing
4	sample				a			=AFUSBYROW(A5:E12,,,1)						=A5:E12=H5#						=BYROW(N5#,LAMBDA(a,AND(a)))
5	a	b	c	d	e		a'	a	b	c	d	e		TRUE	TRUE	TRUE	TRUE	TRUE		TRUE
6	a	e	b	d	e			a	b	d	e	e		TRUE	FALSE	FALSE	FALSE	TRUE		FALSE		1.4. step 4,filtering
7	1	2	3	4	5			1	2	3	4	5		TRUE	TRUE	TRUE	TRUE	TRUE		TRUE		=FILTER(A5:E12,T5#)
8	1	3	0	4	5			0	1	3	4	5		FALSE	FALSE	FALSE	TRUE	TRUE		FALSE		a	b	c	d	e
9	a	a	b	c	d			a	a	b	c	d		TRUE	TRUE	TRUE	TRUE	TRUE		TRUE		1	2	3	4	5
10	1	2	2	3	4			1	2	2	3	4		TRUE	TRUE	TRUE	TRUE	TRUE		TRUE		a	a	b	c	d
11	a	b	1	2	3			1	2	3	a	b		FALSE	FALSE	FALSE	FALSE	FALSE		FALSE		1	2	2	3	4
12	1	2	3	a	b			1	2	3	a	b		TRUE	TRUE	TRUE	TRUE	TRUE		TRUE		1	2	3	a	b
13
14								single cell
15								=FILTER(A5:E12,BYROW(A5:E12=AFUSBYROW(A5:E12,,,1),LAMBDA(a,AND(a))))
16	other function							a	b	c	d	e
17	AFUSBYROW							1	2	3	4	5
18	T_CA							a	a	b	c	d
19								1	2	2	3	4
20								1	2	3	a	b
21
22								2. recursive method seen in ARRANGEMENTS thread
23								=T_CA(A5:E12)
24								a	b	c	d	e
25								1	2	3	4	5
26								a	a	b	c	d
27								1	2	2	3	4
28								1	2	3	a	b
29
ASCAN post 16

Cell Formulas
Range	Formula
H4,N4,H23,H15,V7,T4	H4	=FORMULATEXT(H5)
H5:L12	H5	=AFUSBYROW(A5:E12,,,1)
N5:R12	N5	=A5:E12=H5#
T5:T12	T5	=BYROW(N5#,LAMBDA(a,AND(a)))
V8:Z12	V8	=FILTER(A5:E12,T5#)
H16:L20	H16	=FILTER(A5:E12,BYROW(A5:E12=AFUSBYROW(A5:E12,,,1),LAMBDA(a,AND(a))))
H24:L28	H24	=T_CA(A5:E12)
Dynamic array formulas.

 

[Xlambda]

LAMBDA 1.1.4.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Task: filter only rows of an array that are in ascending order, REDUCE
2	5	columns					step 1 <=> for i=1 to clms(a)-1, 1*(clm i<=clm i+1)
3		asc.order					=REDUCE(1,SEQUENCE(A2-1),LAMBDA(v,i,v*(INDEX(A5:E12,,i)<=INDEX(A5:E12,,i+1))))
4	sample				a		a'
5	a	b	c	d	e		1
6	a	e	b	d	e		0		step 2, FILTER(a,a')
7	1	2	3	4	5		1		=FILTER(A5:E12,G5#)
8	1	3	0	4	5		0		a	b	c	d	e
9	a	a	b	c	d		1		1	2	3	4	5
10	1	2	2	3	4		1		a	a	b	c	d
11	a	b	1	2	3		0		1	2	2	3	4
12	1	2	3	a	b		1		1	2	3	a	b
13
14
15			step 3, single cell
16			=FILTER(A5:E12,REDUCE(1,SEQUENCE(A2-1),LAMBDA(v,i,v*(INDEX(A5:E12,,i)<=INDEX(A5:E12,,i+1)))))
17			a	b	c	d	e
18			1	2	3	4	5
19			a	a	b	c	d
20			1	2	2	3	4
21			1	2	3	a	b
22
ASCAN post 17

Cell Formulas
Range	Formula
G3	G3	=FORMULATEXT(G5)
G5:G12	G5	=REDUCE(1,SEQUENCE(A2-1),LAMBDA(v,i,v*(INDEX(A5:E12,,i)<=INDEX(A5:E12,,i+1))))
I7,C16	I7	=FORMULATEXT(I8)
I8:M12	I8	=FILTER(A5:E12,G5#)
C17:G21	C17	=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]

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.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Leila'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)))
3	table sample : Sales			↓↓↓		my solution (longer, unfortunatily, but avoids offset, hope that still can be considered a lambda "formula" ?)
4	Month	Sales		↓↓↓		=SCAN(0,SEQUENCE(ROWS(Sales)),LAMBDA(v,i,(MONTH(INDEX(Sales[Month],i))<>1)*v+INDEX(Sales[Sales],i)))
5	Jan-19	1		1		1
6	Feb-19	2		3		3		to reset the "acumulator" on July , we change only <>1 to <>7 on both formulas
7	Mar-19	3		6		6		=SCAN(0,Sales[Sales],LAMBDA(a,b,IF(MONTH(OFFSET(b,,-1))<>7,a+b,0*a+b)))
8	Apr-19	4		10		10		↓↓↓
9	May-19	5		15		15		↓↓↓		=SCAN(0,SEQUENCE(ROWS(Sales)),LAMBDA(v,i,(MONTH(INDEX(Sales[Month],i))<>7)*v+INDEX(Sales[Sales],i)))
10	Jun-19	6		21		21		1		1
11	Jul-19	7		28		28		3		3
12	Aug-19	8		36		36		6		6
13	Sep-19	9		45		45		10		10
14	Oct-19	10		55		55		15		15
15	Nov-19	11		66		66		21		21
16	Dec-19	12		78		78		7		7
17	Jan-20	1		1		1		15		15
18	Feb-20	2		3		3		24		24
19	Mar-20	3		6		6		34		34
20	Apr-20	4		10		10		45		45
21	May-20	5		15		15		57		57
22	Jun-20	6		21		21		58		58
23	Jul-20	7		28		28		60		60
24	Aug-20	8		36		36		63		63
25	Sep-20	9		45		45		67		67
26	Oct-20	10		55		55		72		72
27	Nov-20	11		66		66		78		78
28	Dec-20	12		78		78		7		7
29	Jan-21	1		1		1		15		15
30	Feb-21	2		3		3		24		24
31	Mar-21	3		6		6		34		34
32	Apr-21	4		10		10		45		45
33	May-21	5		15		15		57		57
34	Jun-21	6		21		21		58		58
35	Jul-21	7		28		28		60		60
36	Aug-21	8		36		36		63		63
37	Sep-21	9		45		45		67		67
38	Oct-21	10		55		55		72		72
39	Nov-21	11		66		66		78		78
40	Dec-21	12		78		78		7		7
41	Jan-22	1		1		1		15		15
42	Feb-22	2		3		3		24		24
43	Mar-22	3		6		6		34		34
44	Apr-22	4		10		10		45		45
45	May-22	5		15		15		57		57
46	Jun-22	6		21		21		58		58
47	Jul-22	7		28		28		60		60
48	Aug-22	8		36		36		63		63
49	Sep-22	9		45		45		67		67
50	Oct-22	10		55		55		72		72
51	Nov-22	11		66		66		78		78
52	Dec-22	12		78		78		7		7
53								15		15
54								24		24
55								34		34
56								45		45
57								57		57
58
Sheet2

Cell Formulas
Range	Formula
D2,H7	D2	=FORMULATEXT(D5)
F4,J9	F4	=FORMULATEXT(F5)
D5:D52	D5	=SCAN(0,Sales[Sales],LAMBDA(a,b,IF(MONTH(OFFSET(b,,-1))<>1,a+b,0*a+b)))
F5:F52	F5	=SCAN(0,SEQUENCE(ROWS(Sales)),LAMBDA(v,i,(MONTH(INDEX(Sales[Month],i))<>1)*v+INDEX(Sales[Sales],i)))
H10:H57	H10	=SCAN(0,Sales[Sales],LAMBDA(a,b,IF(MONTH(OFFSET(b,,-1))<>7,a+b,0*a+b)))
J10:J57	J10	=SCAN(0,SEQUENCE(ROWS(Sales)),LAMBDA(v,i,(MONTH(INDEX(Sales[Month],i))<>7)*v+INDEX(Sales[Sales],i)))
Dynamic array formulas.

 

[Xlambda]

leila SCAN.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Bonus 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)
2	There are more approaches, chose this one because is with SCAN embeded in REDUCE
3	Month	Sales		=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))))))
4	Jan-19	1		1
5	Feb-19	2		2
6	Mar-19	3		3
7	Apr-19	4		4
8	May-19	5		5
9	Jun-19	6		6
10	Jul-19	7		7
11	Aug-19	8		8
12	Sep-19	9		9
13	Oct-19	10		10
14	Nov-19	11		11
15	Dec-19	12		12
16	Jan-20	1		2
17	Feb-20	2		4
18	Mar-20	3		6
19	Apr-20	4		8
20	May-20	5		10
21	Jun-20	6		12
22	Jul-20	7		14
23	Aug-20	8		16
24	Sep-20	9		18
25	Oct-20	10		20
26	Nov-20	11		22
27	Dec-20	12		24
28	Jan-21	1		3
29	Feb-21	2		6
30	Mar-21	3		9
31	Apr-21	4		12
32	May-21	5		15
33	Jun-21	6		18
34	Jul-21	7		21
35	Aug-21	8		24
36	Sep-21	9		27
37	Oct-21	10		30
38	Nov-21	11		33
39	Dec-21	12		36
40	Jan-22	1		4
41	Feb-22	2		8
42	Mar-22	3		12
43	Apr-22	4		16
44	May-22	5		20
45	Jun-22	6		24
46	Jul-22	7		28
47	Aug-22	8		32
48	Sep-22	9		36
49	Oct-22	10		40
50	Nov-22	11		44
51	Dec-22	12		48
52
Sheet3

Cell Formulas
Range	Formula
D3	D3	=FORMULATEXT(D4)
D4:D51	D4	=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.