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]

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.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	lambda "formula", Moving Average, using SCAN
2
3		What if you need the "moving average", like average of last 7 days or last 12 months?
4		Moving averages "smooth out" numbers to give you a better over view of the trend.						(all numeric data, same format)
5								single cell (input array, entire column, formula knows where to stop)
6		Month End	Sales	6-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))))
7		31-07-22	614,212	494,631	=AVERAGE(C7:C12)			494,631
8		30-06-22	551,250	456,528	=AVERAGE(C8:C13)			456,528
9		31-05-22	352,220	410,297	=AVERAGE(C9:C14)			410,297
10		30-04-22	607,028	405,090	=AVERAGE(C10:C15)			405,090
11		31-03-22	494,401	342,149	=AVERAGE(C11:C16)			342,149
12		28-02-22	348,672	283,648	=AVERAGE(C12:C17)			283,648
13		31-01-22	385,596	273,681	=AVERAGE(C13:C18)			273,681
14		31-12-21	273,864	279,941	=AVERAGE(C14:C19)			279,941
15		30-11-21	320,980	282,871	=AVERAGE(C15:C20)			282,871
16		31-10-21	229,383	328,734	=AVERAGE(C16:C21)			328,734
17		30-09-21	143,395	337,529	=AVERAGE(C17:C22)			337,529
18		31-08-21	288,865	394,946	=AVERAGE(C18:C23)			394,946
19		31-07-21	423,159	393,964	=AVERAGE(C19:C24)			393,964
20		30-06-21	291,445	385,404	=AVERAGE(C20:C25)			385,404
21		31-05-21	596,158	366,708	=AVERAGE(C21:C26)			366,708
22		30-04-21	282,149	314,443	=AVERAGE(C22:C27)			314,443
23		31-03-21	487,897	358,810	=AVERAGE(C23:C28)			358,810
24		28-02-21	282,977	315,192	=AVERAGE(C24:C29)			315,192
25		31-01-21	371,800	307,968	=AVERAGE(C25:C30)			307,968
26		31-12-20	179,265	289,454	=AVERAGE(C26:C31)			289,454
27		30-11-20	282,570	282,478	=AVERAGE(C27:C32)			282,478
28		31-10-20	548,348	268,041	=AVERAGE(C28:C33)			268,041
29		30-09-20	226,192	219,677	=AVERAGE(C29:C34)			219,677
30		31-08-20	239,634	260,147	=AVERAGE(C30:C35)			260,147
31		31-07-20	260,717	295,960	=AVERAGE(C31:C36)			295,960
32		30-06-20	137,409	278,240	=AVERAGE(C32:C37)			278,240
33		31-05-20	195,947			drag and drop			formula knows
34		30-04-20	258,165			has to stop here			where to stop
35		31-03-20	469,009
36		29-02-20	454,513
37		31-01-20	154,396
38
moving average

Cell Formulas
Range	Formula
F6	F6	=FORMULATEXT(H7)
D7:D32	D7	=AVERAGE(C7:C12)
E7:E32	E7	=FORMULATEXT(D7)
H7:H32	H7	=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]

ABYROW.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Moving Average , different approach, unpivoting, using BYROW and ABYROW
2
3					=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))))
4		Month End	Sales		↓		=LET(a,C5:C35,nm,6,ABYROW(SEQUENCE(ROWS(a)-nm+1),LAMBDA(i,AVERAGE(INDEX(a,SEQUENCE(,6)+i-1)))))
5		31-07-22	614,212		494,631		494,631
6		30-06-22	551,250		456,528		456,528		To prove that ABYROW is "natively" capable (BYROW is not)
7		31-05-22	352,220		410,297		410,297		to "spread" (spill) a horizontal pattern from a vertical array (single values/row).
8		30-04-22	607,028		405,090		405,090		ABYROW is can embed a pattern created from a column.
9		31-03-22	494,401		342,149		342,149		=ABYROW(SEQUENCE(26),LAMBDA(i,SEQUENCE(,6)+i-1))
10		28-02-22	348,672		283,648		283,648		1	2	3	4	5	6
11		31-01-22	385,596		273,681		273,681		2	3	4	5	6	7
12		31-12-21	273,864		279,941		279,941		3	4	5	6	7	8
13		30-11-21	320,980		282,871		282,871		4	5	6	7	8	9
14		31-10-21	229,383		328,734		328,734		5	6	7	8	9	10
15		30-09-21	143,395		337,529		337,529		6	7	8	9	10	11
16		31-08-21	288,865		394,946		394,946		7	8	9	10	11	12
17		31-07-21	423,159		393,964		393,964		8	9	10	11	12	13
18		30-06-21	291,445		385,404		385,404		9	10	11	12	13	14
19		31-05-21	596,158		366,708		366,708		10	11	12	13	14	15
20		30-04-21	282,149		314,443		314,443		11	12	13	14	15	16
21		31-03-21	487,897		358,810		358,810		12	13	14	15	16	17
22		28-02-21	282,977		315,192		315,192		13	14	15	16	17	18
23		31-01-21	371,800		307,968		307,968		14	15	16	17	18	19
24		31-12-20	179,265		289,454		289,454		15	16	17	18	19	20
25		30-11-20	282,570		282,478		282,478		16	17	18	19	20	21
26		31-10-20	548,348		268,041		268,041		17	18	19	20	21	22
27		30-09-20	226,192		219,677		219,677		18	19	20	21	22	23
28		31-08-20	239,634		260,147		260,147		19	20	21	22	23	24
29		31-07-20	260,717		295,960		295,960		20	21	22	23	24	25
30		30-06-20	137,409		278,240		278,240		21	22	23	24	25	26
31		31-05-20	195,947						22	23	24	25	26	27
32		30-04-20	258,165						23	24	25	26	27	28
33		31-03-20	469,009						24	25	26	27	28	29
34		29-02-20	454,513						25	26	27	28	29	30
35		31-01-20	154,396						26	27	28	29	30	31
36
mov avrg 2

Cell Formulas
Range	Formula
E3	E3	=FORMULATEXT(E5)
G4,I9	G4	=FORMULATEXT(G5)
E5:E30	E5	=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:G30	G5	=LET(a,C5:C35,nm,6,ABYROW(SEQUENCE(ROWS(a)-nm+1),LAMBDA(i,AVERAGE(INDEX(a,SEQUENCE(,6)+i-1)))))
I10:N35	I10	=ABYROW(SEQUENCE(26),LAMBDA(i,SEQUENCE(,6)+i-1))
Dynamic array formulas.

 

[Xlambda]

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.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	ABYROW. 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 something
4			a	b		#N/A
5			c	d
6
7	1.filter only numeric values byrow
8
9						manually (drag and drop)
10						F6:F12 =FILTER(A11:D11,ISNUMBER(A11:D11))				=ABYROW(A11:D17,LAMBDA(a,FILTER(a,ISNUMBER(a))))
11	1	2	a	b		1	2			1	2
12	c	d	3	e		3				3
13	f	g	h	i		#CALC!
14	j	4	5	6		4	5	6		4	5	6
15	7	8	9	x		7	8	9		7	8	9
16	k	l	m	n		#CALC!
17	o	10	p	11		10	11			10	11
18
19	2. count the rows that have no numeric values									3. remove empty rows
20
21						=SUM(--(J11:J17=""))				=FILTER(J11#,J11:J17<>"")
22						2				1	2
23										3
24	4. average of numbers, if empty numeric rows are considered 0's									4	5	6
25										7	8	9
26						=IF(J11:J17="",0,J11#)				10	11
27						1	2
28						3
29						0	0	0
30						4	5	6
31						7	8	9
32						0	0	0
33						10	11
34
35						=AVERAGE(F27#)
36						3.8824
37
38						compared to
39
40						=AVERAGE(A11:D17)
41						6
42
ABYROW peek 4

Cell Formulas
Range	Formula
F3,F40,F35,F26,J21,F21,J10	F3	=FORMULATEXT(F4)
F4	F4	=ABYROW(C4:D5,LAMBDA(a,FILTER(a,ISNUMBER(a))))
F10	F10	="F6:F12"&" "&FORMULATEXT(F11)
J11:L17	J11	=ABYROW(A11:D17,LAMBDA(a,FILTER(a,ISNUMBER(a))))
F11:G11,F17:G17,F14:H15,F12:F13,F16	F11	=FILTER(A11:D11,ISNUMBER(A11:D11))
F22	F22	=SUM(--(J11:J17=""))
J22:L26	J22	=FILTER(J11#,J11:J17<>"")
F27:H33	F27	=IF(J11:J17="",0,J11#)
F36	F36	=AVERAGE(F27#)
F41	F41	=AVERAGE(A11:D17)
Dynamic array formulas.

 

[Xlambda]

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.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
1	array		Set up array, randomly created						Function to be tested							Functions to test:
2	elements		fixed 10 clms wide						LAMBDA(a,UNIQUE(a,1))
3			nr. rows		200000				(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 ) ) ) )
4	A			spin	n										tboulden's
5	B		=IF(E4="n",C6#,LET(a,INDEX(A4:A49,RANDARRAY(E3,10,1,46,1)),IFERROR(--a,a)))												thunks
6	C		7	C	4	3	2	E	X	S	C	L
7	D		K	1	Q	9	A	K	O	P	Q	W
8	E		O	N	2	3	H	X	W	Z	S	U
9	F		J	C	M	3	C	9	3	O	V	E
10	G		U	R	K	F	M	G	W	4	4	3
11	H		B	E	4	T	7	H	D	8	5	M
12	I		I	4	5	7	J	4	4	2	1	7
13	J		3	3	S	3	W	0	6	N	2	6
14	K		2	Q	V	L	1	Y	D	4	D	9
15	L		V	5	G	C	T	L	1	7	2	1
16	M		R	U	J	3	G	M	7	A	9	8
17	N		0	1	0	B	J	3	S	B	G	W
18	O		6	2	G	Y	9	Q	2	N	T	F
19	P		1	5	M	E	1	2	E	R	T	W
20	Q		3	6	6	P	H	R	6	4	2	T
21	R		Z	E	3	9	6	4	K	B	8	B
22	S		S	I	Z	V	C	4	9	6	5	B			2.	=ABYROW(C6#,LAMBDA(a,UNIQUE(a,1)))
23	T		4	U	Y	T	S	H	3	7	3	P			3.	=AROW(C6#,LAMBDA(a,UNIQUE(a,1)))				calls ABYCOL
24	U		8	X	M	H	9	1	7	L	8	E			4.	=AFUSBYROW(C6#:L15,,1)				calls AFLAT
25	V		Z	3	2	G	H	8	J	1	3	C				(4. classic, no lambda helper functions used)
26	W		X	H	1	N	W	S	J	2	Q	A
27	X		G	U	6	V	1	R	Z	D	5	L				Note: 2,3 and 4 functions, perform lot more intestine calculations
28	Y		8	J	0	Q	F	U	G	C	4	6				since are designed as "functional" functions.
29	Z		7	4	J	Y	T	S	8	R	9	J				(can handle , errors, null strings, wrong arguments)
30	0		8	1	1	O	G	Y	T	P	D	3
31	1		6	3	O	9	F	O	H	9	U	J
32	2		2	E	2	R	H	Z	C	S	1	4				links to other functions:
33	3		T	0	8	6	8	N	J	L	L	V				(use latest posts/variants on respective threads)
34	4		2	F	6	E	E	Y	O	2	9	X				ABYROW		this thread
35	5		K	5	T	P	S	1	J	H	Q	0				ABYCOL		this thread
36	6		7	W	8	2	7	J	G	X	4	0				AROW		this thread
37	7		I	8	9	B	X	P	5	3	F	W				AFUSBYROW
38	8		I	S	9	K	Q	R	E	S	3	K				AFLAT
39	9		0	0	G	X	1	6	6	O	F	9
40	0		9	B	1	8	J	8	Y	A	K	N
41	1		T	9	M	P	K	3	B	E	Z	D
42	2		4	C	J	X	1	5	7	A	Y	I			Results	thunks	ABYROW	AROW	AFUSBYROW
43	3		6	8	9	C	C	8	5	V	K	2			array rows	1. calc time	2. calc time	3. calc time	4. calc time
44	4		J	H	4	W	G	J	3	0	1	D			1,000	10.03 s	1.12 s	instant	instant
45	5		1	8	V	J	0	U	Q	E	V	P			2,000	41.35 s	5.43 s	instant	instant
46	6		W	A	S	R	N	Q	Z	9	Z	5			3,000	94.44 s	12.31 s	instant	instant
47	7		S	2	0	X	K	4	A	2	V	X			4,000	192.29 s	23.88 s	instant	instant
48	8		P	6	W	0	G	V	J	X	O	4			5,000	298.22 s	37.28 s	0.23 s	instant
49	9		3	S	X	X	W	2	C	L	J	Q			10,000	?	?	1.35 s	instant
50			3	W	I	Z	2	8	H	2	P	O			100,000	?	?	12.27 s	4.6 s
51			I	1	0	N	P	U	I	B	E	3			200,000	?	?	22.37 s !!!	error *
52			0	7	3	A	4	D	3	Z	F	I				timing errors aprox. +0.5s		*	using AFLAT, 200,000 rows * 10 clms=
53			D	J	E	6	B	1	6	A	6	D			!!! Quite a lot, but so far,				= 2M rows (excedes 1.5M rows spreadsheet real estate
54			5	G	I	Q	1	3	V	2	8	0			I do not have a better solution, yet!				(horiz/vert size do not affect calc. time, only r*c value)
55			4	X	F	F	P	F	8	E	8	V
56			3	5	8	0	B	8	2	1	0	3			Important note:
57			J	9	1	O	1	N	6	R	O	6			If anybody has other functions, other designs, other results, please feel free to share it with us here.
58			3	E	K	U	4	I	B	C	4	2
59			K	E	O	G	T	2	W	F	G	G			Glimpse of results returned by AROW				check	200000	=ROWS(O61#)
60			9	N	4	W	P	C	C	0	M	K			=AROW(C6#,LAMBDA(a,UNIQUE(a,1)))
61			2	S	4	G	8	8	7	X	N	2			7	C	4	3	2	E	X	S	L
62			A	A	W	6	3	P	5	I	Z	0			K	1	Q	9	A	O	P	W
63			2	7	N	V	9	F	2	7	I	9			O	N	2	3	H	X	W	Z	S	U
64			2	F	8	H	D	N	3	Q	E	Q			J	C	M	3	9	O	V	E
65			6	0	F	E	U	0	Y	S	D	0			U	R	K	F	M	G	W	4	3
66			0	S	7	Y	E	Z	P	H	G	E			B	E	4	T	7	H	D	8	5	M
67			B	B	T	0	3	2	5	H	P	5			I	4	5	7	J	2	1
68			8	P	V	7	4	1	Y	O	V	U			3	S	W	0	6	N	2
69			4	M	L	D	I	U	1	3	8	8			2	Q	V	L	1	Y	D	4	9
70			I	6	A	Y	Y	4	F	A	C	Q			V	5	G	C	T	L	1	7	2
71			A	F	Q	M	K	8	T	C	3	F			R	U	J	3	G	M	7	A	9	8
72			W	X	W	7	6	N	2	N	8	L			0	1	B	J	3	S	G	W
73			R	5	5	Y	N	4	3	7	0	7			6	2	G	Y	9	Q	N	T	F
74			N	1	F	A	V	I	1	H	0	J			1	5	M	E	2	R	T	W
75			0	0	T	5	8	X	K	J	X	4			3	6	P	H	R	4	2	T
76			4	F	W	T	1	2	9	9	6	3			Z	E	3	9	6	4	K	B	8
77			9	1	O	P	8	Q	Z	0	8	0			S	I	Z	V	C	4	9	6	5	B
78			6	1	2	0	1	G	G	S	R	6			4	U	Y	T	S	H	3	7	P
79			4	2	Z	S	S	7	N	K	L	Y			8	X	M	H	9	1	7	L	E
ABYCOL peek 1

Cell Formulas
Range	Formula
C5,O60	C5	=FORMULATEXT(C6)
C6:L200005	C6	=IF(E4="n",C6#,LET(a,INDEX(A4:A49,RANDARRAY(E3,10,1,46,1)),IFERROR(--a,a)))
T59	T59	=ROWS(O61#)
U59	U59	=FORMULATEXT(T59)
O61:X200060	O61	=AROW(C6#,LAMBDA(a,UNIQUE(a,1)))
Dynamic array formulas.

 

[Xlambda]

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.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
1	array		Set up array, randomly created						Function to be tested							Functions to test:
2	elements		fixed 10 clms wide						LAMBDA(a,UNIQUE(a,1))
3			nr. rows		300000				(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 ) ) ) )
4	A			spin	n										tboulden's
5	B		=IF(E4="n",C6#,LET(a,INDEX(A4:A49,RANDARRAY(E3,10,1,46,1)),IFERROR(--a,a)))												thunks
6	C		4	H	7	E	7	H	E	8	D	N
7	D		K	8	Z	5	W	8	N	3	9	Y
8	E		0	4	6	4	D	5	Q	B	7	Z
9	F		0	J	J	D	0	8	Q	K	A	4
10	G		7	K	3	6	B	4	N	9	N	9
11	H		Q	A	6	6	O	N	J	R	C	7
12	I		4	2	0	L	K	P	L	0	F	C
13	J		5	B	U	9	A	4	2	0	5	4
14	K		5	X	P	6	S	1	Q	J	Q	Z
15	L		S	O	P	4	2	M	1	0	U	O
16	M		2	4	T	1	C	H	G	7	N	4
17	N		D	Y	8	B	P	2	E	8	2	7
18	O		C	5	5	C	P	T	G	5	F	Y
19	P		U	Q	L	7	3	X	A	6	K	2
20	Q		E	Z	8	7	F	U	8	O	4	Z
21	R		8	D	A	V	L	0	K	3	8	2
22	S		4	X	0	P	Y	J	A	U	2	T			2.	=ABYROW(C6#,LAMBDA(a,UNIQUE(a,1)))
23	T		F	9	9	R	W	5	6	0	M	D			3.	=AROW(C6#,LAMBDA(a,UNIQUE(a,1))) (old one)				calls ABYCOL
24	U		P	5	X	Z	2	0	3	S	H	5			4.	=AFUSBYROW(C6#:L15,,1)				calls AFLAT
25	V		H	0	0	2	E	Q	P	7	1	J			5.	=AROW(C6#,LAMBDA(a,UNIQUE(a,1))) (new one)
26	W		1	9	F	M	7	J	9	0	Z	2				(4. classic, no lambda helper functions used)
27	X		L	9	7	D	3	U	R	8	I	6
28	Y		0	L	G	5	K	5	R	9	3	7				Note: 2,3 and 4 functions, perform lot more intestine calculations
29	Z		L	4	P	K	Y	M	D	W	4	9				since are designed as "functional" functions.
30	0		8	U	1	K	B	3	3	W	H	7				(can handle , errors, null strings, wrong arguments)
31	1		0	5	N	S	H	L	K	Y	3	F
32	2		G	J	E	J	4	G	H	5	2	L
33	3		N	K	8	W	S	6	Q	0	3	R				links to other functions:
34	4		M	A	O	H	V	1	U	7	D	8				(use latest posts/variants on respective threads)
35	5		G	1	R	Z	7	4	N	1	C	8				ABYROW		this thread
36	6		G	7	1	0	5	W	U	U	2	B				ABYCOL		this thread
37	7		9	3	1	4	F	X	6	Q	T	1				AROW (old,new))		this thread
38	8		4	V	P	C	D	V	U	7	D	2				AFUSBYROW
39	9		0	1	D	6	N	B	E	7	6	F				AFLAT
40	0		B	5	0	4	B	K	Y	0	0	0
41	1		C	1	9	X	0	W	9	S	Z	8
42	2		L	V	1	0	C	5	C	G	5	N							NEW !!
43	3		F	T	Q	0	0	3	9	7	3	6			Results	thunks	ABYROW	AROW(old)	AROW(new)	AFUSBYROW
44	4		0	V	8	9	A	3	D	1	U	6			array rows	1. calc time	2. calc time	3. calc time	5. calc time	4. calc time
45	5		G	Y	1	6	3	O	0	C	9	R			1,000	10.03 s	1.12 s	instant	instant	instant
46	6		0	8	4	A	D	S	1	F	6	0			2,000	41.35 s	5.43 s	instant	instant	instant
47	7		N	7	8	T	9	G	5	Y	9	1			3,000	94.44 s	12.31 s	instant	instant	instant
48	8		R	S	C	D	V	W	C	O	Z	O			4,000	192.29 s	23.88 s	instant	instant	instant
49	9		2	I	K	P	M	6	M	R	E	6			5,000	298.22 s	37.28 s	0.23 s	instant	instant
50			4	2	3	2	4	I	4	1	4	4			10,000	?	?	1.35 s	1.13 s	instant
51			O	J	W	5	H	E	V	Y	5	P			100,000	?	?	12.27 s	10.31 s	4.6 s
52			0	D	1	5	L	E	9	5	Y	7			200,000	?	?	22.37 s	18.91 !!!	error *
53			7	E	9	U	9	1	0	C	7	0			300,000	?	?	34.64 s	29.11 !!!	error *
54			L	5	5	J	I	I	O	B	Z	9				timing errors aprox. +0.5s			*	using AFLAT, 200,000 rows * 10 clms=
55			9	H	B	S	E	U	L	C	A	6			!!! Quite a lot, but so far,					= 2M rows (excedes 1.5M rows spreadsheet real estate
56			3	8	I	1	A	8	H	I	G	2			I do not have a better solution, yet !					(horiz/vert size do not affect calc. time, only r*c value)
57			G	6	T	7	4	9	Y	F	S	W
58			8	N	X	Z	F	7	K	9	U	V			Important note:
59			M	O	2	O	8	N	A	P	5	F			If anybody has other functions, other designs, other results, please feel free to share it with us here.
60			3	I	W	H	V	G	9	5	8	9
61			H	Y	X	3	X	4	5	T	C	L			Glimpse of results returned by AROW				check	300000	=ROWS(O63#)
62			B	M	K	R	7	D	5	Y	P	6			=AROW(C6#,LAMBDA(a,UNIQUE(a,1)))
63			8	J	8	P	E	J	7	G	7	5			4	H	7	E	8	D	N
64			O	E	W	C	3	5	0	A	3	6			K	8	Z	5	W	N	3	9	Y
65			Q	S	Y	2	Z	J	1	5	C	X			0	4	6	D	5	Q	B	7	Z
66			6	2	N	X	7	Q	4	U	5	W			0	J	D	8	Q	K	A	4
67			3	H	V	Z	E	H	H	A	9	P			7	K	3	6	B	4	N	9
68			3	B	5	1	P	9	6	7	P	U			Q	A	6	O	N	J	R	C	7
69			H	8	P	0	4	0	1	8	5	9			4	2	0	L	K	P	F	C
70			9	7	3	F	C	6	R	H	3	2			5	B	U	9	A	4	2	0
71			W	5	E	H	O	0	Z	H	0	3			5	X	P	6	S	1	Q	J	Z
72			K	8	8	5	C	M	8	A	4	9			S	O	P	4	2	M	1	0	U
73			8	3	M	B	4	L	D	3	L	5			2	4	T	1	C	H	G	7	N
74			M	5	0	8	7	D	1	0	2	S			D	Y	8	B	P	2	E	7
75			8	C	Q	3	1	A	5	O	Q	6			C	5	P	T	G	F	Y
76			0	D	9	O	1	O	1	V	V	G			U	Q	L	7	3	X	A	6	K	2
77			G	O	7	5	J	2	2	M	2	0			E	Z	8	7	F	U	O	4
78			I	6	K	E	R	0	V	M	2	R			8	D	A	V	L	0	K	3	2
79			E	Y	C	5	K	9	7	8	E	2			4	X	0	P	Y	J	A	U	2	T
80			8	W	Y	Z	Z	8	0	2	G	8			F	9	R	W	5	6	0	M	D
81			2	N	0	5	7	E	J	E	3	D			P	5	X	Z	2	0	3	S	H
82			B	4	8	7	7	W	8	Q	L	G			H	0	2	E	Q	P	7	1	J
83			3	1	F	4	L	X	Z	Y	J	8			1	9	F	M	7	J	0	Z	2
84			B	7	N	5	P	I	V	H	4	0			L	9	7	D	3	U	R	8	I	6
ABYCOL peek 1

Cell Formulas
Range	Formula
C5,O62	C5	=FORMULATEXT(C6)
C6:L300005	C6	=IF(E4="n",C6#,LET(a,INDEX(A4:A49,RANDARRAY(E3,10,1,46,1)),IFERROR(--a,a)))
T61	T61	=ROWS(O63#)
U61	U61	=FORMULATEXT(T61)
O63:X300062	O63	=AROW(C6#,LAMBDA(a,UNIQUE(a,1)))
Dynamic array formulas.

 

[Xlambda]

1,000,000 (1 milion) rows tested, AROW took 209.95 seconds. During this time thunks method can calculate 4,000 something rows.

ABYROW.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
1	array		Set up array, randomly created						Function to be tested							Functions to test:
2	elements		fixed 10 clms wide						LAMBDA(a,UNIQUE(a,1))
3			nr. rows		1000000				(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 ) ) ) )
4	A			spin	y										tboulden's
5	B		=IF(E4="n",C6#,LET(a,INDEX(A4:A49,RANDARRAY(E3,10,1,46,1)),IFERROR(--a,a)))												thunks
6	C		K	H	P	Z	8	5	O	P	J	N
7	D		6	4	G	Y	5	K	9	C	J	T
8	E		0	R	F	9	P	0	I	A	1	6
9	F		D	1	M	C	C	8	3	M	S	L
10	G		8	L	G	6	R	E	0	3	4	X
11	H		S	7	F	8	7	8	8	Y	C	8
12	I		N	Q	5	1	N	W	3	2	8	X
13	J		2	5	P	8	8	R	9	1	5	7
14	K		4	9	U	M	4	K	0	I	F	R
15	L		G	B	H	6	Z	6	2	4	M	B
16	M		V	1	J	F	E	Q	2	Q	U	G
17	N		S	I	4	3	6	9	F	W	R	0
18	O		J	U	9	8	A	3	7	4	4	B
19	P		4	6	1	0	U	I	V	H	4	5
20	Q		Z	C	6	B	1	D	2	5	G	V
21	R		Q	4	1	I	B	9	I	6	7	T
22	S		D	1	0	F	K	N	V	8	O	G			2.	=ABYROW(C6#,LAMBDA(a,UNIQUE(a,1)))
23	T		C	H	B	E	C	H	L	M	2	D			3.	=AROW(C6#,LAMBDA(a,UNIQUE(a,1))) (old one)				calls ABYCOL
24	U		2	L	W	I	F	T	F	N	Y	J			4.	=AFUSBYROW(C6#:L15,,1)				calls AFLAT
25	V		O	X	0	R	T	Z	R	K	0	O			5.	=AROW(C6#,LAMBDA(a,UNIQUE(a,1))) (new one)
26	W		5	L	7	B	4	N	O	6	7	9				(4. classic, no lambda helper functions used)
27	X		U	0	5	E	Y	9	9	0	C	1
28	Y		1	H	C	N	H	I	C	I	7	8				Note: 2,3 and 4 functions, perform lot more intestine calculations
29	Z		9	3	5	K	I	5	N	Z	I	8				since are designed as "functional" functions.
30	0		M	8	W	W	0	P	8	O	6	C				(can handle , errors, null strings, wrong arguments)
31	1		3	C	F	N	4	7	0	6	0	9
32	2		1	5	T	S	7	6	T	W	M	5
33	3		L	X	0	2	V	4	D	0	R	5				links to other functions:
34	4		Y	0	7	9	S	L	A	Q	E	E				(use latest posts/variants on respective threads)
35	5		C	C	P	S	1	9	X	R	5	I				ABYROW		this thread
36	6		I	T	L	9	X	T	A	Q	7	P				ABYCOL		this thread
37	7		B	C	F	4	G	G	M	4	8	R				AROW (old,new))		this thread
38	8		I	1	X	3	W	L	D	H	R	B				AFUSBYROW
39	9		2	T	3	R	9	U	Q	S	9	J				AFLAT
40	0		7	0	2	8	5	G	M	C	X	2
41	1		W	E	D	L	A	9	F	P	5	5
42	2		4	B	I	H	G	2	D	U	7	8							NEW !!
43	3		R	7	F	4	B	P	X	E	1	P			Results	thunks	ABYROW	AROW(old)	AROW(new)	AFUSBYROW
44	4		7	2	0	S	S	H	G	E	B	Q			array rows	1. calc time	2. calc time	3. calc time	5. calc time	4. calc time
45	5		H	4	F	F	Y	I	8	9	W	4			1,000	10.03 s	1.12 s	instant	instant	instant
46	6		9	6	Q	5	2	5	M	U	J	4			2,000	41.35 s	5.43 s	instant	instant	instant
47	7		0	3	8	2	U	6	Y	8	Q	T			3,000	94.44 s	12.31 s	instant	instant	instant
48	8		M	C	0	X	9	Z	0	X	1	P			4,000	192.29 s	23.88 s	instant	instant	instant
49	9		G	6	9	D	0	R	V	W	R	D			5,000	298.22 s	37.28 s	0.23 s	instant	instant
50			7	U	I	9	3	8	G	5	0	7			10,000	?	?	1.35 s	1.13 s	instant
51			C	2	S	G	5	Q	7	K	6	0			100,000	?	?	12.27 s	10.31 s	4.6 s
52			O	4	9	8	T	7	5	U	4	Y			200,000	?	?	22.37 s	18.91 !!!	error *
53			P	5	T	0	4	3	5	R	C	0			300,000	?	?	34.64 s	29.11 !!!	error *
54			3	B	8	T	1	J	M	N	C	4				timing errors aprox. +0.5s			*	using AFLAT, 200,000 rows * 10 clms=
55			V	8	W	1	B	B	F	4	9	T			!!! Quite a lot, but so far,					= 2M rows (excedes 1.5M rows spreadsheet real estate
56			5	C	I	6	2	L	Z	I	7	F			I do not have a better solution, yet !					(horiz/vert size do not affect calc. time, only r*c value)
57			8	P	Q	E	Q	2	2	5	6	B
58			N	4	Q	R	3	D	9	C	P	J			Important note:
59			6	1	O	8	J	9	J	H	Y	J			If anybody has other functions, other designs, other results, please feel free to share it with us here.
60			6	V	Q	0	8	H	G	1	E	O
61			1	2	C	9	1	1	5	I	M	0			Glimpse of results returned by AROW				check	1000000	=ROWS(O63#)
62			Q	3	0	O	Z	6	U	7	8	Z			=AROW(C6#,LAMBDA(a,UNIQUE(a,1)))
63			2	I	F	T	H	F	T	X	B	6			K	H	P	Z	8	5	O	J	N
64			B	G	E	F	B	X	J	C	C	U			6	4	G	Y	5	K	9	C	J	T
65			0	N	Q	1	5	M	3	0	4	W			0	R	F	9	P	I	A	1	6
66			D	3	N	6	W	D	A	6	4	J			D	1	M	C	8	3	S	L
67			L	8	9	S	P	R	1	1	1	B			8	L	G	6	R	E	0	3	4	X
68			8	Z	I	M	Q	8	J	4	O	E			S	7	F	8	Y	C
69			7	9	0	4	T	P	D	7	W	4			N	Q	5	1	W	3	2	8	X
70			7	T	D	J	S	M	5	F	8	O			2	5	P	8	R	9	1	7
71			8	6	0	Q	Q	0	G	C	7	9			4	9	U	M	K	0	I	F	R
72			S	Z	4	0	1	4	J	9	X	I			G	B	H	6	Z	2	4	M
73			H	T	8	2	H	W	0	3	1	7			V	1	J	F	E	Q	2	U	G
74			9	Z	S	1	E	R	6	N	7	C			S	I	4	3	6	9	F	W	R	0
75			4	7	E	B	7	Z	1	S	L	2			J	U	9	8	A	3	7	4	B
76			6	7	M	V	Y	H	V	J	A	I			4	6	1	0	U	I	V	H	5
77			Y	H	7	U	2	5	1	8	O	4			Z	C	6	B	1	D	2	5	G	V
78			R	Y	7	0	4	9	H	0	2	C			Q	4	1	I	B	9	6	7	T
79			E	0	Z	O	P	K	B	F	5	7			D	1	0	F	K	N	V	8	O	G
80			7	8	Z	1	O	H	K	C	2	F			C	H	B	E	L	M	2	D
81			2	M	F	4	F	3	I	G	Y	X			2	L	W	I	F	T	N	Y	J
82			5	U	5	3	G	T	E	B	5	8			O	X	0	R	T	Z	K
83			V	U	3	D	2	V	4	7	B	W			5	L	7	B	4	N	O	6	9
84			K	9	0	3	Q	G	R	0	9	W			U	0	5	E	Y	9	C	1
85			K	W	4	1	P	N	1	M	1	3			1	H	C	N	I	7	8
ABYCOL peek 1

Cell Formulas
Range	Formula
C5,O62	C5	=FORMULATEXT(C6)
C6:L1000005	C6	=IF(E4="n",C6#,LET(a,INDEX(A4:A49,RANDARRAY(E3,10,1,46,1)),IFERROR(--a,a)))
T61	T61	=ROWS(O63#)
U61	U61	=FORMULATEXT(T61)
O63:X1000062	O63	=AROW(C6#,LAMBDA(a,UNIQUE(a,1)))
Dynamic array formulas.

 

[Xlambda]

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.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
1						cpaTbl		formula to unpivot table
2		CPA Test 1	CPA Test 2	CPA Test 3	CPA Test 4	CPA 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)))
3		22	28	43	35	55		CPA Test 1	22
4		49	14	32	45	53		CPA Test 2	28		1. if input data table format (cpaTbl)
5		70	5	52	61	30		CPA Test 3	43		Note: Sample table has less rows than original, to fit real-estate of mini-sheet
6		13	65	5	37	68		CPA Test 4	35		lambda argument quartile.inc(a,sequence(5)-1) spills vertically
7		52	80	51	52	58		CPA Test 5	55
8		44	67	62	55	31		CPA Test 1	49		lambda formula
9		18	81	56	45	61		CPA Test 2	14		=REDUCE(0,SEQUENCE(5),LAMBDA(v,i,IF(SEQUENCE(i)=i,BYCOL(cpaTbl,LAMBDA(a,QUARTILE.INC(a,i-1))),v)))
10		52	47	31	7	57		CPA Test 3	32		13	5	5	4	13
11		49	51	47	44	87		CPA Test 4	45		44	49	34	37	53
12		37	67	12	57	60		CPA Test 5	53		51	58	49	44	59
13		78	66	64	58	66		CPA Test 1	70		70	66	62	53	66
14		75	81	61	50	66		CPA Test 2	5		78	81	89	61	87
15		70	63	49	53	64		CPA Test 3	52
16		51	15	67	39	32		CPA Test 4	61		=ACOL(cpaTbl,LAMBDA(a,QUARTILE.INC(a,SEQUENCE(5)-1)))
17		50	54	34	34	62		CPA Test 5	30		13	5	5	4	13
18		70	50	89	60	17		CPA Test 1	13		44	49	34	37	53
19		71	54	40	40	13		CPA Test 2	65		51	58	49	44	59
20		72	49	73	4	59		CPA Test 3	5		70	66	62	53	66
21		17	62	47	43	77		CPA Test 4	37		78	81	89	61	87
22		45	58	69	43	56		CPA Test 5	68
23		62	64	31	36	74		CPA Test 1	52		2. if input data as 2 column array
24								CPA Test 2	80		=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))))
25								CPA Test 3	51		13	5	5	4	13
26								CPA Test 4	52		44	49	34	37	53
27								CPA Test 5	58		51	58	49	44	59
28								CPA Test 1	44		70	66	62	53	66
29								CPA Test 2	67		78	81	89	61	87
30								CPA Test 3	62
31								CPA Test 4	55
32								CPA Test 5	31
33								CPA Test 1	18
34								CPA Test 2	81
35								CPA Test 3	56
36								CPA Test 4	45
37								CPA Test 5	61
38								CPA Test 1	52
39								CPA Test 2	47
40								CPA Test 3	31
41								CPA Test 4	7
42								CPA Test 5	57
43								CPA Test 1	49
44								CPA Test 2	51
45								CPA Test 3	47
46								CPA Test 4	44
47								CPA Test 5	87
48								CPA Test 1	37
49								CPA Test 2	67
50								CPA Test 3	12
51								CPA Test 4	57
52								CPA Test 5	60
53								CPA Test 1	78
54								CPA Test 2	66
55								CPA Test 3	64
56								CPA Test 4	58
57								CPA Test 5	66
58								CPA Test 1	75
59								CPA Test 2	81
60								CPA Test 3	61
61								CPA Test 4	50
62								CPA Test 5	66
63								CPA Test 1	70
64								CPA Test 2	63
65								CPA Test 3	49
66								CPA Test 4	53
67								CPA Test 5	64
68								CPA Test 1	51
69								CPA Test 2	15
70								CPA Test 3	67
71								CPA Test 4	39
72								CPA Test 5	32
73								CPA Test 1	50
74								CPA Test 2	54
75								CPA Test 3	34
76								CPA Test 4	34
77								CPA Test 5	62
78								CPA Test 1	70
79								CPA Test 2	50
80								CPA Test 3	89
81								CPA Test 4	60
82								CPA Test 5	17
83								CPA Test 1	71
84								CPA Test 2	54
85								CPA Test 3	40
86								CPA Test 4	40
87								CPA Test 5	13
88								CPA Test 1	72
89								CPA Test 2	49
90								CPA Test 3	73
91								CPA Test 4	4
92								CPA Test 5	59
93								CPA Test 1	17
94								CPA Test 2	62
95								CPA Test 3	47
96								CPA Test 4	43
97								CPA Test 5	77
98								CPA Test 1	45
99								CPA Test 2	58
100								CPA Test 3	69
101								CPA Test 4	43
102								CPA Test 5	56
103								CPA Test 1	62
104								CPA Test 2	64
105								CPA Test 3	31
106								CPA Test 4	36
107								CPA Test 5	74
108
ACOL

Cell Formulas
Range	Formula
H2,K24,K16,K9	H2	=FORMULATEXT(H3)
H3:I107	H3	=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:O14	K10	=REDUCE(0,SEQUENCE(5),LAMBDA(v,i,IF(SEQUENCE(i)=i,BYCOL(cpaTbl,LAMBDA(a,QUARTILE.INC(a,i-1))),v)))
K17:O21	K17	=ACOL(cpaTbl,LAMBDA(a,QUARTILE.INC(a,SEQUENCE(5)-1)))
K25:O29	K25	=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 Ranges
Name	Refers To	Cells
ExternalData_1	=ACOL!$B$2:$F$23	H3

 

[Xlambda]

ABYROW.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
1				both orientations, when data 2clms array
2	CPA Test 1	22
3	CPA Test 2	28		=UNIQUE(A2:A106)
4	CPA Test 3	43		↓↓	=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))))
5	CPA Test 4	35		CPA Test 1	13	44	51	70	78
6	CPA Test 5	55		CPA Test 2	5	49	58	66	81
7	CPA Test 1	49		CPA Test 3	5	34	49	62	89
8	CPA Test 2	14		CPA Test 4	4	37	44	53	61
9	CPA Test 3	32		CPA Test 5	13	53	59	66	87
10	CPA Test 4	45
11	CPA Test 5	53			=TRANSPOSE(UNIQUE(A2:A106))
12	CPA Test 1	70			CPA Test 1	CPA Test 2	CPA Test 3	CPA Test 4	CPA Test 5
13	CPA Test 2	5			13	5	5	4	13
14	CPA Test 3	52			44	49	34	37	53
15	CPA Test 4	61			51	58	49	44	59
16	CPA Test 5	30			70	66	62	53	66
17	CPA Test 1	13			78	81	89	61	87
18	CPA Test 2	65		E13	=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))))
19	CPA Test 3	5
20	CPA Test 4	37
21	CPA Test 5	68
22	CPA Test 1	52
23	CPA Test 2	80
24	CPA Test 3	51
25	CPA Test 4	52
26	CPA Test 5	58
27	CPA Test 1	44
28	CPA Test 2	67
29	CPA Test 3	62
30	CPA Test 4	55
31	CPA Test 5	31
32	CPA Test 1	18
33	CPA Test 2	81
34	CPA Test 3	56
35	CPA Test 4	45
36	CPA Test 5	61
37	CPA Test 1	52
38	CPA Test 2	47
39	CPA Test 3	31
40	CPA Test 4	7
41	CPA Test 5	57
42	CPA Test 1	49
43	CPA Test 2	51
44	CPA Test 3	47
45	CPA Test 4	44
46	CPA Test 5	87
47	CPA Test 1	37
48	CPA Test 2	67
49	CPA Test 3	12
50	CPA Test 4	57
51	CPA Test 5	60
52	CPA Test 1	78
53	CPA Test 2	66
54	CPA Test 3	64
55	CPA Test 4	58
56	CPA Test 5	66
57	CPA Test 1	75
58	CPA Test 2	81
59	CPA Test 3	61
60	CPA Test 4	50
61	CPA Test 5	66
62	CPA Test 1	70
63	CPA Test 2	63
64	CPA Test 3	49
65	CPA Test 4	53
66	CPA Test 5	64
67	CPA Test 1	51
68	CPA Test 2	15
69	CPA Test 3	67
70	CPA Test 4	39
71	CPA Test 5	32
72	CPA Test 1	50
73	CPA Test 2	54
74	CPA Test 3	34
75	CPA Test 4	34
76	CPA Test 5	62
77	CPA Test 1	70
78	CPA Test 2	50
79	CPA Test 3	89
80	CPA Test 4	60
81	CPA Test 5	17
82	CPA Test 1	71
83	CPA Test 2	54
84	CPA Test 3	40
85	CPA Test 4	40
86	CPA Test 5	13
87	CPA Test 1	72
88	CPA Test 2	49
89	CPA Test 3	73
90	CPA Test 4	4
91	CPA Test 5	59
92	CPA Test 1	17
93	CPA Test 2	62
94	CPA Test 3	47
95	CPA Test 4	43
96	CPA Test 5	77
97	CPA Test 1	45
98	CPA Test 2	58
99	CPA Test 3	69
100	CPA Test 4	43
101	CPA Test 5	56
102	CPA Test 1	62
103	CPA Test 2	64
104	CPA Test 3	31
105	CPA Test 4	36
106	CPA Test 5	74
107
ACOL 2

Cell Formulas
Range	Formula
D3	D3	=FORMULATEXT(D5)
E4,E11	E4	=FORMULATEXT(E5)
D5:D9	D5	=UNIQUE(A2:A106)
E5:I9	E5	=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:I12	E12	=TRANSPOSE(UNIQUE(A2:A106))
E13:I17	E13	=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))))
E18	E18	=FORMULATEXT(E13)
Dynamic array formulas.

 

[Xlambda]

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]

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.