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]

leila SCAN.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	Existing alternative functions : We already have functions shorter than a formula bar row that can handle these tasks.
2
3	Month	Sales			SCAN by month																single cell year SCAN
4	Jan-19	1			=ARESIZE(Sales[Sales],,12)												=ARESIZE(E11#,,1)				=ARESIZE(ASCAN(ARESIZE(Sales[Sales],,12),1),,1)
5	Feb-19	2		1.	1	2	3	4	5	6	7	8	9	10	11	12		1	3.		1
6	Mar-19	3			1	2	3	4	5	6	7	8	9	10	11	12		2			2
7	Apr-19	4			1	2	3	4	5	6	7	8	9	10	11	12		3			3
8	May-19	5			1	2	3	4	5	6	7	8	9	10	11	12		4			4
9	Jun-19	6																5			5
10	Jul-19	7			=ASCAN(E5#,1)													6			6
11	Aug-19	8		2.	1	2	3	4	5	6	7	8	9	10	11	12		7			7
12	Sep-19	9			2	4	6	8	10	12	14	16	18	20	22	24		8			8
13	Oct-19	10			3	6	9	12	15	18	21	24	27	30	33	36		9			9
14	Nov-19	11			4	8	12	16	20	24	28	32	36	40	44	48		10			10
15	Dec-19	12																11			11
16	Jan-20	1			SCAN by year													12			12
17	Feb-20	2			1.					2.					3.			2			2		single cell month SCAN
18	Mar-20	3		=AUNSTACK(Sales[Sales],12)						=ASCAN(E19#,1)				=ASTACK(J19#,1)				4			4		=ASTACK(ASCAN(AUNSTACK(Sales[Sales],12),1),1)
19	Apr-20	4			1	1	1	1		1	1	1	1		1			6			6		1
20	May-20	5			2	2	2	2		3	3	3	3		3			8			8		3
21	Jun-20	6			3	3	3	3		6	6	6	6		6			10			10		6
22	Jul-20	7			4	4	4	4		10	10	10	10		10			12			12		10
23	Aug-20	8			5	5	5	5		15	15	15	15		15			14			14		15
24	Sep-20	9			6	6	6	6		21	21	21	21		21			16			16		21
25	Oct-20	10			7	7	7	7		28	28	28	28		28			18			18		28
26	Nov-20	11			8	8	8	8		36	36	36	36		36			20			20		36
27	Dec-20	12			9	9	9	9		45	45	45	45		45			22			22		45
28	Jan-21	1			10	10	10	10		55	55	55	55		55			24			24		55
29	Feb-21	2			11	11	11	11		66	66	66	66		66			3			3		66
30	Mar-21	3			12	12	12	12		78	78	78	78		78			6			6		78
31	Apr-21	4													1			9			9		1
32	May-21	5													3			12			12		3
33	Jun-21	6			other functions										6			15			15		6
34	Jul-21	7			ASTACK										10			18			18		10
35	Aug-21	8			AUNSTACK										15			21			21		15
36	Sep-21	9			ARESIZE										21			24			24		21
37	Oct-21	10													28			27			27		28
38	Nov-21	11													36			30			30		36
39	Dec-21	12													45			33			33		45
40	Jan-22	1													55			36			36		55
41	Feb-22	2													66			4			4		66
42	Mar-22	3													78			8			8		78
43	Apr-22	4													1			12			12		1
44	May-22	5													3			16			16		3
45	Jun-22	6													6			20			20		6
46	Jul-22	7													10			24			24		10
47	Aug-22	8													15			28			28		15
48	Sep-22	9													21			32			32		21
49	Oct-22	10													28			36			36		28
50	Nov-22	11													36			40			40		36
51	Dec-22	12													45			44			44		45
52															55			48			48		55
53															66								66
54															78								78
55															1								1
56															3								3
57															6								6
58															10								10
59															15								15
60															21								21
61															28								28
62															36								36
63															45								45
64															55								55
65															66								66
66															78								78
67
Sheet3

Cell Formulas
Range	Formula
E4,W18,J18,E10,U4	E4	=FORMULATEXT(E5)
Q4,D18,N18	Q4	=FORMULATEXT(R5)
E5:P8	E5	=ARESIZE(Sales[Sales],,12)
R5:R52	R5	=ARESIZE(E11#,,1)
U5:U52	U5	=ARESIZE(ASCAN(ARESIZE(Sales[Sales],,12),1),,1)
E11:P14	E11	=ASCAN(E5#,1)
E19:H30	E19	=AUNSTACK(Sales[Sales],12)
J19:M30	J19	=ASCAN(E19#,1)
O19:O66	O19	=ASTACK(J19#,1)
W19:W66	W19	=ASTACK(ASCAN(AUNSTACK(Sales[Sales],12),1),1)
Dynamic array formulas.

 

[Xlambda]

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.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	1. A1V(a,b)
2				rws a+rws b=2+1=3
3		a		=IF(SEQUENCE(3)=3,A7:B7,A4:B5)						=A1V(A4:B5,A7:B7)
4	a	b		a	b					a	b
5	c	d		c	d					c	d
6		b		e	f					e	f
7	e	f
8
9	2. lambda formula to replace recursion with REDUCE
10	If y(a) is the function to apply to every row
11	=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,))
13
14					lamda "formula" to sort in ascending order each row ( sort(a,,,1) 1 argument for sorting by clm)
15			a		=REDUCE("",SEQUENCE(3),LAMBDA(v,i,A1V(v,SORT(INDEX(A16:C18,i,),,,1))))
16	a	c	b																=A1V("",A7:B7)
17	1	2	3		a	b	c		the first row is blank because initial value is "" and first iteration is A1V=("",sort(first row of a)), like in
18	3	2	1		1	2	3												e	f
19					1	2	3
20
21					to amend the formula to "cut" first row
22					=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))))
23					a	b	c
24					1	2	3
25					1	2	3
26
27					sorting in descending order each row
28					=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))))
29					c	b	a
30					3	2	1
31					3	2	1		Instead of SORT we can write any function y=y(a)=LAMBDA(a,FUNCTION(a)) ( has to spill horizontally)
32
ASCAN np1

Cell Formulas
Range	Formula
D3,J3,E28,E22,S16,E15	D3	=FORMULATEXT(D4)
D4:E6	D4	=IF(SEQUENCE(3)=3,A7:B7,A4:B5)
J4:K6	J4	=A1V(A4:B5,A7:B7)
E16:G19	E16	=REDUCE("",SEQUENCE(3),LAMBDA(v,i,A1V(v,SORT(INDEX(A16:C18,i,),,,1))))
S17:T18	S17	=A1V("",A7:B7)
E23:G25	E23	=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:G31	E29	=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]

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.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	a						sorting ascending						sorting descending
2	2	1	1	3			y,LAMBDA(a,SORT(a,,,-1))						y,LAMBDA(a,SORT(a,,,-1))
3	3	2	2	0		=AROW(A2:D7,LAMBDA(a,SORT(a,,,-1)))							=AROW(A2:D7,LAMBDA(a,SORT(a,,-1,-1)))
4	1	2	3	2			1	1	2	3			3	2	1	1
5	3	2	1	0			0	2	2	3			3	2	2	0
6	2	1	3	4			1	2	2	3			3	2	2	1
7	2	2	2	1			0	1	2	3			3	2	1	0
8							1	2	3	4			4	3	2	1
9							1	2	2	2			2	2	2	1
10
11							unique values						unique values ascending order
12							y,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)))
14							2	1	3				1	2	3
15							3	2	0				0	2	3
16							1	2	3				1	2	3
17							3	2	1	0			0	1	2	3
18							2	1	3	4			1	2	3	4
19							2	1					1	2
20
21							exclude blanks, align left						filter text,excluding blanks
22							y,LAMBDA(a,FILTER(a,a<>""))						y,LAMBDA(a,FILTER(a,ISTEXT(a)*(a<>"")))
23	a					=AROW(A24:E26,LAMBDA(a,FILTER(a,a<>"")))							=AROW(A24:E26,LAMBDA(a,FILTER(a,ISTEXT(a)*(a<>""))))
24	a		b	1	2		a	b	1	2			a	b
25	1	e	3	c	d		1	e	3	c	d		e	c	d
26	3	c	d		4		3	c	d	4			c	d
27
28							filter numbers
29							y,LAMBDA(a,FILTER(a,ISNUMBER(a)))
30							=AROW(A24:E26,LAMBDA(a,FILTER(a,ISNUMBER(a))))
31							1	2
32							1	3
33							3	4
34
ASCAN np 2

Cell Formulas
Range	Formula
F3,F23	F3	=FORMULATEXT(G4)
M3,G30,M23,M13	M3	=FORMULATEXT(M4)
G4:J9	G4	=AROW(A2:D7,LAMBDA(a,SORT(a,,,-1)))
M4:P9	M4	=AROW(A2:D7,LAMBDA(a,SORT(a,,-1,-1)))
E13	E13	=FORMULATEXT(G14)
G14:J19	G14	=AROW(A2:D7,LAMBDA(a,UNIQUE(a,1)))
M14:P19	M14	=AROW(A2:D7,LAMBDA(a,SORT(UNIQUE(a,1),,,-1)))
G24:K26	G24	=AROW(A24:E26,LAMBDA(a,FILTER(a,a<>"")))
M24:O26	M24	=AROW(A24:E26,LAMBDA(a,FILTER(a,ISTEXT(a)*(a<>""))))
G31:H33	G31	=AROW(A24:E26,LAMBDA(a,FILTER(a,ISNUMBER(a))))
Dynamic array formulas.

 

[Xlambda]

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

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Name	Amount Base	Bonus			Task: Lambda "formula" to sum array formatted with TEXT							other functions
2	Bill	1000	50										T_CHARS
3	Nick	2500	60			1st method, when we have a pattern (digits after ":")							AKEEP
4	Sara	3600	100			single cell for whole row							(last posts functions)
5	Jessica	100	20			=SUM(--REPLACE(B6:C6,1,SEARCH(":",B6:C6),""))
6		Base: 7,200	Bonus: 230	0		7430
7		↑	↑	↑
8		↑	↑	=SUM(B6:C6)		2nd method, works for any text format (REDUCE method)
9		↑	="Bonus: "&TEXT(SUM(C2:C5),"#,##0")			single cell for whole row
10		="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),"")))))
11						7430
12
13						=LET(a,B14:D14,REDUCE(0,a,LAMBDA(v,x,v+CONCAT(IFERROR(--MID(x,SEQUENCE(LEN(x)),1),"")))))
14		Base: $ 7,200	Bonus# 1,230$	Other/$2,740$/		11170
15
16
17		Base: $ 7,200	Other/$2,740$/			=LET(a,B17:C18,REDUCE(0,a,LAMBDA(v,x,v+CONCAT(IFERROR(--MID(x,SEQUENCE(LEN(x)),1),"")))))
18		Bonus# 1,230$	$200$HGFgh			11370
19
20						3rd method, dedicated functions, any text format
21						=SUM(AKEEP(B6:C6,T_CHARS(1)))
22		Big advantage of using dedicated function,						=SUM(AKEEP(B17:C18,T_CHARS(1)))				=SUM(AKEEP(B14:D14,T_CHARS(1)))
23		can handle decimal points with ease				7430		11370				11170
24		because of T_CHARS versatility
25						=AKEEP(B26:C27,T_CHARS(1,,"."))				=SUM(AKEEP(B26:C27,T_CHARS(1,,".")))
26		Base: $ 7,200.01	Other/$2,740.03$/			7200.01	2740.03			11370.1
27		Bonus# 1,230.02$	$200.04$HGFgh			1230.02	200.04
28
29						=SUM(F26#)
30						11370.1
31
Sheet1

Cell Formulas
Range	Formula
F5,F29,F25,J25,H22,L22,F17,F13,F10	F5	=FORMULATEXT(F6)
B6	B6	="Base: "&TEXT(SUM(B2:B5),"#,##0")
C6	C6	="Bonus: "&TEXT(SUM(C2:C5),"#,##0")
D6	D6	=SUM(B6:C6)
F6	F6	=SUM(--REPLACE(B6:C6,1,SEARCH(":",B6:C6),""))
D8	D8	=FORMULATEXT(D6)
C9	C9	=FORMULATEXT(C6)
B10	B10	=FORMULATEXT(B6)
F11	F11	=LET(a,B6:C6,REDUCE(0,a,LAMBDA(v,x,v+CONCAT(IFERROR(--MID(x,SEQUENCE(LEN(x)),1),"")))))
F14	F14	=LET(a,B14:D14,REDUCE(0,a,LAMBDA(v,x,v+CONCAT(IFERROR(--MID(x,SEQUENCE(LEN(x)),1),"")))))
F18	F18	=LET(a,B17:C18,REDUCE(0,a,LAMBDA(v,x,v+CONCAT(IFERROR(--MID(x,SEQUENCE(LEN(x)),1),"")))))
F21	F21	=FORMULATEXT(F23)
F23	F23	=SUM(AKEEP(B6:C6,T_CHARS(1)))
H23	H23	=SUM(AKEEP(B17:C18,T_CHARS(1)))
L23	L23	=SUM(AKEEP(B14:D14,T_CHARS(1)))
F26:G27	F26	=AKEEP(B26:C27,T_CHARS(1,,"."))
J26	J26	=SUM(AKEEP(B26:C27,T_CHARS(1,,".")))
F30	F30	=SUM(F26#)
Dynamic array formulas.

 

[Xlambda]

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.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1		Formula 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),"")))))
2		Data			=QUARTILE.INC(B3:B36,D4:D8)
3		2		Quart	Value	Mode/s
4		4		0	2
5		10		1	20	12		=TRANSPOSE(MODE.MULT(FILTER($B$3:$B$36,($B$3:$B$36>=E4)*($B$3:$B$36<E5))))
6		12		2	41	31		=TRANSPOSE(MODE.MULT(FILTER($B$3:$B$36,($B$3:$B$36>=E5)*($B$3:$B$36<E6))))
7		12		3	67.5	44		=TRANSPOSE(MODE.MULT(FILTER($B$3:$B$36,($B$3:$B$36>=E6)*($B$3:$B$36<E7))))
8		13		4	79	68	70	=TRANSPOSE(MODE.MULT(FILTER($B$3:$B$36,($B$3:$B$36>=E7)*($B$3:$B$36<E8))))
9		15
10		18		single cell formula, spills horizontally
11		19		12
12		23		31
13		24		44
14		30		68	70
15		31
16		31		first try was a shorter formula that had this problem:
17		31		=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))))
18		38		12	12
19		39		31	31			why is that? REDUCE appends with every iteration another row.
20		43		44	44			If the final result will "spill" max n clms, if one row spills a single value, will spread this single result to all clms.
21		44		68	70
22		44						Like in this example:				to append a single value "7" at the bottom of the array "a"
23		52								a		=IF(SEQUENCE(3)=3,7,H24:J25)
24		57						1	2	3		1	2	3
25		61						4	5	6		4	5	6
26		64										7	7	7
27		66
28		68						to overcome this behavior(that happens only for single values) we have to "force" "7" in a 2 elem array ({7,""})
29		68
30		70						=IF(SEQUENCE(3)=3,IF({1,0},7,""),H24:J25)				=IFNA(IF(SEQUENCE(3)=3,IF({1,0},7,""),H24:J25),"")
31		70						1	2	3		1	2	3
32		71						4	5	6		4	5	6
33		72						7		#N/A		7
34		73
35		77						This explain this part of final formula:
36		79						...y,IF(COLUMNS(x)=1,IF({1,0},x,""),x),IFNA(IF(SEQUENCE(i)=i,y,v),"")
37
38
ModeInQuartile1

Cell Formulas
Range	Formula
E2	E2	=FORMULATEXT(E4)
E4:E8	E4	=QUARTILE.INC(B3:B36,D4:D8)
F5:F7,F8:G8	F5	=TRANSPOSE(MODE.MULT(FILTER($B$3:$B$36,($B$3:$B$36>=E4)*($B$3:$B$36<E5))))
H5:H8	H5	=FORMULATEXT(F5)
D11:E14	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),"")))))
D17,H30,L30,L23	D17	=FORMULATEXT(D18)
D18:E21	D18	=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:N26	L24	=IF(SEQUENCE(3)=3,7,H24:J25)
H31:J33	H31	=IF(SEQUENCE(3)=3,IF({1,0},7,""),H24:J25)
L31:N33	L31	=IFNA(IF(SEQUENCE(3)=3,IF({1,0},7,""),H24:J25),"")
Dynamic array formulas.

 

[Xlambda]

AROW example ETM1771-Ch03-ESA.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	We 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)
3
4			=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)
5	Data		↓		=XMATCH(A6:A39,C6#,-1)			=IF(E6#=5,4,E6#)
6	2		2		1			1
7	4		20		1			1		Amended formula:
8	10		41		1			1		=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),"")))))
9	12		67.5		1			1
10	12		79		1			1
11	13				1			1		12
12	15				1			1		31
13	18				1			1		44
14	19				1			1		68	70	79
15	23				2			2
16	24				2			2
17	30				2			2
18	31				2			2
19	31				2			2
20	31				2			2
21	38				2			2
22	39				2			2
23	43				3			3
24	44				3			3
25	44				3			3
26	52				3			3
27	57				3			3
28	61				3			3
29	64				3			3
30	66				3			3
31	68				4			4
32	68				4			4
33	70				4			4
34	70				4			4
35	71				4			4
36	72				4			4
37	73				4			4
38	79				5			4
39	79				5			4
40
Sheet1

Cell Formulas
Range	Formula
C4	C4	=FORMULATEXT(C6)
E5,H5	E5	=FORMULATEXT(E6)
C6:C10	C6	=QUARTILE.INC(A6:A39,SEQUENCE(5)-1)
E6:E39	E6	=XMATCH(A6:A39,C6#,-1)
H6:H39	H6	=IF(E6#=5,4,E6#)
J11:L14	J11	=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]

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.xlsm
	A	B	C	D	E	F	G	H
1	Modified "Data" to check if formula spills correctly.
2
3	Data
4	2		4	12	19
5	4		31
6	4		44	66
7	12		68	70	79
8	12
9	13
10	15
11	19
12	19
13	23
14	24
15	30
16	31
17	31
18	31
19	38
20	39
21	43
22	44
23	44
24	52
25	57
26	61
27	66
28	66
29	68
30	68
31	70
32	70
33	71
34	72
35	73
36	79
37	79
38
Sheet2

Cell Formulas
Range	Formula
C4:E7	C4	=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]

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.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Using ABYROW to solve previous task. Checking that ABYROW spills horizontally.
2
3	Data
4	2		=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))))))
5	4		4	12	19
6	4		31
7	12		44	66
8	12		68	70	79
9	13
10	15
11	19
12	19
13	23
14	24
15	30
16	31
17	31
18	31
19	38
20	39
21	43
22	44
23	44
24	52
25	57
26	61
27	66
28	66
29	68
30	68
31	70
32	70
33	71
34	72
35	73
36	79
37	79
38
ABYROW peek 1

Cell Formulas
Range	Formula
C4	C4	=FORMULATEXT(C5)
C5:E8	C5	=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]

ABYROW.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Task: Extract the quartile sections of "Data" as separate rows. Unpivoting a vertical array in uneven rows, based on row pattern only.
2
3			=QUARTILE.INC(A5:A38,SEQUENCE(5)-1)
4	Data		↓		=LET(x,XMATCH(A5:A38,C5#,-1),IF(x=5,4,x))
5	2		2		1
6	4		20		1		=ABYROW(SEQUENCE(4),LAMBDA(i,TRANSPOSE(FILTER(A5:A38,E5#=i))))
7	4		41		1		2	4	4	12	12	13	15	19	19
8	12		67.5		1		23	24	30	31	31	31	38	39
9	12		79		1		43	44	44	52	57	61	66	66
10	13				1		68	68	70	70	71	72	73	79	79
11	15				1
12	19				1		How easy is now to get MODE.MULT with "spilling" ABYROW
13	19				1
14	23				2		=ABYROW(G7#,LAMBDA(a,TRANSPOSE(MODE.MULT(a))))
15	24				2		4	12	19
16	30				2		31
17	31				2		44	66
18	31				2		68	70	79
19	31				2
20	38				2
21	39				2
22	43				3
23	44				3
24	44				3
25	52				3
26	57				3
27	61				3
28	66				3
29	66				3
30	68				4
31	68				4
32	70				4
33	70				4
34	71				4
35	72				4
36	73				4
37	79				4
38	79				4
39
ABYROW peek 2

Cell Formulas
Range	Formula
C3	C3	=FORMULATEXT(C5)
E4,G14,G6	E4	=FORMULATEXT(E5)
C5:C9	C5	=QUARTILE.INC(A5:A38,SEQUENCE(5)-1)
E5:E38	E5	=LET(x,XMATCH(A5:A38,C5#,-1),IF(x=5,4,x))
G7:O10	G7	=ABYROW(SEQUENCE(4),LAMBDA(i,TRANSPOSE(FILTER(A5:A38,E5#=i))))
G15:I18	G15	=ABYROW(G7#,LAMBDA(a,TRANSPOSE(MODE.MULT(a))))
Dynamic array formulas.

 

[Xlambda]

ABYROW.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Task. Unflatten an array for a certain pattern.
2	Unflatten a vertical array into 3 rows. 1st row has 6 elements, 2nd 4, and 3rd 5 elements.
3
4					step1.				step2. row pattern
5			elements/row		=SCAN(0,C6:C8,LAMBDA(v,a,v+a))				=XMATCH(SEQUENCE(MAX(E6#)),E6#,1)
6	a		6		6				1
7	b		4		10				1		step 3. ABYROW
8	c		5		15				1		=ABYROW(SEQUENCE(ROWS(C6:C8)),LAMBDA(i,TRANSPOSE(FILTER(A6:A20,I6#=i))))
9	d								1		a	b	c	d	e	f
10	e				or				1		A	B	C	D
11	f								1		1	2	3	4	5
12	A				=ASCAN(C6:C8)				2
13	B				6				2
14	C				10				2
15	D				15				2
16	1								3
17	2								3
18	3								3
19	4								3
20	5								3
21
ABYROW peek 3

Cell Formulas
Range	Formula
E5,I5,E12,K8	E5	=FORMULATEXT(E6)
E6:E8	E6	=SCAN(0,C6:C8,LAMBDA(v,a,v+a))
I6:I20	I6	=XMATCH(SEQUENCE(MAX(E6#)),E6#,1)
K9:P11	K9	=ABYROW(SEQUENCE(ROWS(C6:C8)),LAMBDA(i,TRANSPOSE(FILTER(A6:A20,I6#=i))))
E13:E15	E13	=ASCAN(C6:C8)
Dynamic array formulas.