AFUSBYROW

[Xlambda]

AFUSBYROW Array Filter Unique Sort By Rows. Calls AFLAT
Great NEWS!!! Yesterday, 28Jul21, smozgur was so kind to let me know about the new released functions BYROW, BYCOL ..etc. Saw MrExcel video about them and jumped to test their functionality. Amazing functions. Then I realized they can not return array results as I wished for, and I promised yesterday that I will write ones that does, filtering, sorting, unique extraction, separate and combined, for each row.
Here it is AFUSBYROW (non recursive) ?. Was easy, I have only used the functionality of previous other old functions like AFILTER , AUNQSRT , AUNIQUE, but this time, embedded in one function only.
I am positive that future Excel functions will cover this, but till then, is fun.
The formula is larger than I wanted to be, because it has some tweaks, (lot of calculations behind the scenes, for big data arrays):
- wrong input arguments checked before any other calculation is performed
- compact nested LET in LET structure, no variables calculated if not triggered by specific input arguments
- because of AFLAT, can handle arrays that have r*c<1 048 576 values (real estate rows limitations of a spreadsheet) , For an array of 100000 rows by 10 columns , calculation time less than 4s.

=LAMBDA(a,f,u,s,
    LET(d,SUM(MMULT({1,1,1},--(CHOOSE({1,2,3},f,u,s)={0,0,-1;-1,1,0;1,2,1}))),IF(d<>3,"check arguments",
      LET(n,ROWS(a),r,SEQUENCE(n),t,AFLAT(a),q,AFLAT(IF(a="","",r)),qf,CHOOSE({1,2},q,t),x,SWITCH(f,0,qf,-1,FILTER(qf,ISTEXT(t)),1,FILTER(qf,ISNUMBER(t))),
         y,IF(u,UNIQUE(x),x),z,SWITCH(s,0,y,1,SORT(y,{1,2}),-1,SORT(y,{1,2},{1,-1})),v,INDEX(z,,1),w,INDEX(z,,2),e,UNIQUE(w),IF(u=2,SWITCH(s,0,e,1,SORT(e),-1,SORT(e,,-1)),
           LET(x,INDEX(FREQUENCY(v,r),r),c,MAX(x),s,SEQUENCE(,c),y,SEQUENCE(n,c)/(x>=s),IFERROR(XLOOKUP(y,AFLAT(y),w,,,2),"")))))
   )
)

LAMBDA 1.1.0.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
1	sample				Introduction: Very first glimpse on new!! BYROW function
2	1	3	2		like in MrExcel video
3	4	2	5		=BYROW(A2:C3,LAMBDA(a,MAX(a)))							=BYROW(A2:C3,LAMBDA(a,AGGREGATE(14,6,a,2)))
4					3							2		second largest value of each row,
5					5							4		also works because returns a single value
6					works fine							works fine
7	Seems that any function that returns a single result, applied to every row of an array , will produce an array of results accordingly
8	Note 1:		top 2 largest values of each rows does not work, because it does not return a single result k={1,2} is triggering an array
9		=BYROW(A2:C3,LAMBDA(a,LARGE(a,{1,2})))
10		#CALC!
11	Probably, to solve this, still will need a recursive approach. Will see the other functions potential asap.
12	Covered this already with 2 types of recursive functions, 2 different methods, regarding the "engine" that "stacks" the results iteration by iteration.
13	One method, found in T_A1GGH, (ARF post Task 10) uses extra arguments "ai" for "stacking" and "i" for "counting", but no other function needed ,
14	and the other method, found in AAGREGATE that does not need any of extra arguments, but calls another function for "stacking" (APPEND2V)
15		=T_A1GGH(A2:C3,14,6,{1,2},,)					=AAGGREGATE(A2:C3,12,{1,2})
16		3	2				3	2
17		5	4				5	4
18	Anyhow , the fact that for single results we don't need to use recursion anymore, it's a huge step. Lot of functions will be rewritten.
19	I will see how I can integrate BYROW when a single result is returned, and use recursion for arrays results only when we trigger them.
20
21	Note 2:		SORT each row of an array, independently, also does not work (array outcome), and AFUSBYROW here tries to address this kind of functionality.
22		=BYROW(A2:C3,LAMBDA(a,SORT(a)))
23		#CALC!
24																0,0,0 only blanks removed								0,2,0 all unique
25	f,u,s, arguments values							sample								=AFUSBYROW(H26:N31,,,)								=AFUSBYROW(H26:N31,,2,)
26	f		u		s			1	2	2	z	4	a	z		1	2	2	z	4	a	z		1
27	-1		0		-1			8	b	b	8	14	c	14		8	b	b	8	14	c	14		2		1,2,-1 nr.only,all unique,desc.
28	0		1		0			d		3	5	3	5	d		d	3	5	3	5	d			z		=AFUSBYROW(H26:N31,1,2,-1)
29	1		2		1			6	f	g	8		g	8		6	f	g	8	g	8			4		14
30	text		no unique		descending			9	9		5	9	5	5		9	9	5	9	5	5			a		9		text only,all unique,asc.
31	no blanks		unique		no sort			d	3	a		3	4	4		d	3	a	3	4	4			8		8		-1,2,1
32	numeric		all unique		ascending																			b		6		=AFUSBYROW(H26:N31,-1,2,1)
33						-1,1,-1 text,unq.,desc.					1,0,1 only nr. ascending							1,1,1 nr.,unq,asc.						14		5		a
34	-1,0,0 only text				=AFUSBYROW(H26:N31,-1,1,-1)						=AFUSBYROW(H26:N31,1,,1)							=AFUSBYROW(H26:N31,1,1,1)						c		4		b
35	z	a	z			z	a				1	2	2	4				1	2	4				d		3		c
36	b	b	c			c	b				8	8	14	14				8	14					3		2		d
37	d	d				d					3	3	5	5				3	5					5		1		f
38	f	g	g			g	f				6	8	8					6	8					6				g
39											5	5	5	9	9	9		5	9					f				z
40	d	a				d	a				3	3	4	4				3	4					g
41	=AFUSBYROW(H26:N31,-1,,)																							9
42
43	using ACOMBINE these are all possible combinations 3^3=27
44	f	u	s															=ISNUMBER(S40)
45	-1	0	-1			Obs: No need to say, numbers are extracted as numbers												TRUE
46	-1	0	0
47	-1	0	1			input errors, wron argument values
48	-1	1	-1			=AFUSBYROW(H26:N31,2,,)
49	-1	1	0			check arguments
50	-1	1	1
51	-1	2	-1			AFUSBYCOL will follow probably using double transpose this one
52	-1	2	0
53	-1	2	1			other functions on minisheet
54	0	0	-1			T_A1GGH (ARF post Task 10)
55	0	0	0			AAGREGATE
56	0	0	1
57	0	1	-1
58	0	1	0
59	0	1	1
60	0	2	-1
61	0	2	0
62	0	2	1
63	1	0	-1
64	1	0	0
65	1	0	1
66	1	1	-1
67	1	1	0
68	1	1	1
69	1	2	-1
70	1	2	0
71	1	2	1
72
AFUSBYROW

Cell Formulas
Range	Formula
E3,L3,F48,R44,R34,K34,AB32,Z28,X25,P25,B22,B15,G15,B9	E3	=FORMULATEXT(E4)
E4:E5	E4	=BYROW(A2:C3,LAMBDA(a,MAX(a)))
L4:L5	L4	=BYROW(A2:C3,LAMBDA(a,AGGREGATE(14,6,a,2)))
B10	B10	=BYROW(A2:C3,LAMBDA(a,LARGE(a,{1,2})))
B16:C17	B16	=T_A1GGH(A2:C3,14,6,{1,2},,)
G16:H17	G16	=AAGGREGATE(A2:C3,12,{1,2})
B23	B23	=BYROW(A2:C3,LAMBDA(a,SORT(a)))
P26:V31	P26	=AFUSBYROW(H26:N31,,,)
X26:X41	X26	=AFUSBYROW(H26:N31,,2,)
Z29:Z37	Z29	=AFUSBYROW(H26:N31,1,2,-1)
AB33:AB39	AB33	=AFUSBYROW(H26:N31,-1,2,1)
E34	E34	=FORMULATEXT(F35)
A35:C40	A35	=AFUSBYROW(H26:N31,-1,,)
F35:G40	F35	=AFUSBYROW(H26:N31,-1,1,-1)
K35:P40	K35	=AFUSBYROW(H26:N31,1,,1)
R35:T40	R35	=AFUSBYROW(H26:N31,1,1,1)
A41	A41	=FORMULATEXT(A35)
R45	R45	=ISNUMBER(S40)
F49	F49	=AFUSBYROW(H26:N31,2,,)
Dynamic array formulas.

 

[Xlambda]

AFUSBYCOL Array Filter Unique Sort By Columns. Calls AFUSBYROW (above, same arguments) . This is what I call "double transpose", when we need to change the "orientation" of an "oriented" function.
If Fr(a) is row oriented, to get to a function column oriented we use: Fc(a)=transpose(Fr(transpose(a))
Important note: AFUSBYROW returns its results left aligned, AFUSBYCOL returns its results top aligned. On both functions, all unique values return a vertical 1D array.
AFUSBYCOL(a,f,u,s)=LAMBDA(a,f,u,s,LET(t,TRANSPOSE(IF(a="","",a)),x,AFUSBYROW(t,f,u,s),IF(u<>2,TRANSPOSE(IF(x="","",x)),x)))

=LAMBDA(a,f,u,s,LET(t,TRANSPOSE(IF(a="","",a)),x,AFUSBYROW(t,f,u,s),IF(u<>2,TRANSPOSE(IF(x="","",x)),x)))

LAMBDA 1.1.0.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
1								0,0,0							-1,0,0							1,0,0
2	sample							=AFUSBYCOL($A$3:$F$9,,,)							=AFUSBYCOL($A$3:$F$9,-1,,)							=AFUSBYCOL($A$3:$F$9,1,,)								all combinations
3	1	8	d	6	9	d		1	8	d	6	9	d		z	b	d	f		d		1	8	3	6	9	3			f	u	s	Description
4	2	b		f	9	3		2	b	3	f	9	3		a	b	d	g		a		2	8	5	8	9	3		1	-1	0	-1	text descending
5	2	b	3	g		a		2	b	5	g	5	a		z	c		g				2	14	3	8	5	4		2	-1	0	0	text
6	z	8	5	8	5			z	8	3	8	9	3							2		4	14	5		9	4		3	-1	0	1	text ascending
7	4	14	3		9	3		4	14	5	g	5	4		-1,0,1											5			4	-1	1	-1	unique text descending
8	a	c	5	g	5	4		a	c	d	8	5	4		=AFUSBYCOL($A$3:$F$9,-1,,1)											5			5	-1	1	0	unique text
9	z	14	d	8	5	4		z	14						a	b	d	f		a							20		6	-1	1	1	unique text
10													11		z	b	d	g		d		1,0,1							7	-1	2	-1	all unique text descending
11	0,2,0							0.1.0							z	c		g				=AFUSBYCOL($A$3:$F$9,1,,1)							8	-1	2	0	all unique text
12	=AFUSBYCOL(A3:F9,,2,)							=AFUSBYCOL($A$3:$F$9,,1,)												3		1	8	3	6	5	3		9	-1	2	1	all unique text ascending
13	1							1	8	d	6	9	d		-1,0,-1							2	8	3	8	5	3		10	0	0	-1	descending
14	2		0,2,1					2	b	3	f	5	3		=AFUSBYCOL($A$3:$F$9,-1,,-1)							2	14	5	8	5	4		11	0	0	0	no blanks
15	z		=AFUSBYCOL(A3:F9,,2,1)					z	14	5	g		a		z	c	d	g		d		4	14	5		9	4		12	0	0	1	ascending
16	4		1					4	c		8		4		z	b	d	g		a						9			13	0	1	-1	numbers descending
17	a		2					a							a	b		f								9			14	0	1	0	numbers
18	8		3		-1,2,1								14							1							21		15	0	1	1	numbers ascending
19	b		4		=AFUSBYCOL(A3:F9,-1,2,1)										-1,1,0							1,0,-1							16	0	2	-1	all unique descending
20	14		5		a										=AFUSBYCOL($A$3:$F$9,-1,1,)							=AFUSBYCOL($A$3:$F$9,1,,-1)							17	0	2	0	all unique
21	c		6		b			0,1,1							z	b	d	f		d		4	14	5	8	9	4		18	0	2	1	all unique ascending
22	d		8		c			=AFUSBYCOL($A$3:$F$9,,1,1)							a	c		g		a		2	14	5	8	9	4		19	1	0	-1	numbers descending
23	3		9		d			1	8	3	6	5	3							5		2	8	3	6	9	3		20	1	0	0	numbers
24	5		14		f			2	14	5	8	9	4		-1,1,1							1	8	3		5	3		21	1	0	1	numbers ascending
25	6		a		g			4	b	d	f		a		=AFUSBYCOL($A$3:$F$9,-1,1,1)											5			22	1	1	-1	unique numbers descending
26	f		b		z			a	c		g		d		a	b	d	f		a						5			23	1	1	0	unique numbers
27	g		c		9			z							z	c		g		d							19		24	1	1	1	unique numbers ascending
28	9		d										15							6		1,1,0							25	1	2	-1	all unique numbers descending
29	17		f		1,2,-1										-1,1,-1							=AFUSBYCOL($A$3:$F$9,1,1,)							26	1	2	0	all unique numbers
30			g		=AFUSBYCOL(A3:F9,1,2,-1)										=AFUSBYCOL($A$3:$F$9,-1,1,-1)							1	8	3	6	9	3		27	1	2	1	all unique numbers ascending
31			z		14										z	c	d	g		d		2	14	5	8	5	4
32			18		9			0,1,-1							a	b		f		a		4
33					8			=AFUSBYCOL(A3:F9,,1,-1)												4							23
34					6			z	c	d	g	9	d									1,1,1
35					5			a	b	5	f	5	a									=AFUSBYCOL($A$3:$F$9,1,1,1)
36					4			4	14	3	8		4									1	8	3	6	5	3
37					3			2	8		6		3									2	14	5	8	9	4
38					2			1														4
39					1								13														24
40					25																	1,1,-1
41																						=AFUSBYCOL($A$3:$F$9,1,1,-1)
42																						4	14	5	8	9	4
43																						2	8	3	6	5	3
44																						1
45																											22
46
AFUSBYCOL

Cell Formulas
Range	Formula
H2,V41,V35,H33,O30,E30,V29,O25,H22,O20,V20,E19,C15,O14,H12,A12,V11,O8,V2,O2	H2	=FORMULATEXT(H3)
H3:M9	H3	=AFUSBYCOL($A$3:$F$9,,,)
O3:T5	O3	=AFUSBYCOL($A$3:$F$9,-1,,)
V3:AA8	V3	=AFUSBYCOL($A$3:$F$9,1,,)
AC4:AC30	AC4	=SEQUENCE(27)
O9:T11	O9	=AFUSBYCOL($A$3:$F$9,-1,,1)
V12:AA17	V12	=AFUSBYCOL($A$3:$F$9,1,,1)
A13:A28	A13	=AFUSBYCOL(A3:F9,,2,)
H13:M17	H13	=AFUSBYCOL($A$3:$F$9,,1,)
O15:T17	O15	=AFUSBYCOL($A$3:$F$9,-1,,-1)
C16:C31	C16	=AFUSBYCOL(A3:F9,,2,1)
E20:E26	E20	=AFUSBYCOL(A3:F9,-1,2,1)
O21:T22	O21	=AFUSBYCOL($A$3:$F$9,-1,1,)
V21:AA26	V21	=AFUSBYCOL($A$3:$F$9,1,,-1)
H23:M27	H23	=AFUSBYCOL($A$3:$F$9,,1,1)
O26:T27	O26	=AFUSBYCOL($A$3:$F$9,-1,1,1)
V30:AA32	V30	=AFUSBYCOL($A$3:$F$9,1,1,)
E31:E39	E31	=AFUSBYCOL(A3:F9,1,2,-1)
O31:T32	O31	=AFUSBYCOL($A$3:$F$9,-1,1,-1)
H34:M38	H34	=AFUSBYCOL(A3:F9,,1,-1)
V36:AA38	V36	=AFUSBYCOL($A$3:$F$9,1,1,1)
V42:AA44	V42	=AFUSBYCOL($A$3:$F$9,1,1,-1)
Dynamic array formulas.

 

[Xlambda]

Since the functions were created same day when lambda helper functions emerged, was not familiar with omitted arguments.
Here are the functions with f,u,s arguments that can be omitted. These are the only modifications, same functionality kept.
AFUSBYROW

=LAMBDA(a,[f],[u],[s],
    LET(d,SUM(MMULT({1,1,1},--(CHOOSE({1,2,3},f,u,s)={0,0,-1;-1,1,0;1,2,1}))),IF(d<>3,"check arguments",
      LET(n,ROWS(a),r,SEQUENCE(n),t,AFLAT(a),q,AFLAT(IF(a="","",r)),qf,CHOOSE({1,2},q,t),x,SWITCH(f,0,qf,-1,FILTER(qf,ISTEXT(t)),1,FILTER(qf,ISNUMBER(t))),
         y,IF(u,UNIQUE(x),x),z,SWITCH(s,0,y,1,SORT(y,{1,2}),-1,SORT(y,{1,2},{1,-1})),v,INDEX(z,,1),w,INDEX(z,,2),e,UNIQUE(w),IF(u=2,SWITCH(s,0,e,1,SORT(e),-1,SORT(e,,-1)),
           LET(x,INDEX(FREQUENCY(v,r),r),c,MAX(x),s,SEQUENCE(,c),y,SEQUENCE(n,c)/(x>=s),IFERROR(XLOOKUP(y,AFLAT(y),w,,,2),"")))))
   )
)

AFUSBYCOL

=LAMBDA(a,[f],[u],[s],LET(t,TRANSPOSE(IF(a="","",a)),x,AFUSBYROW(t,f,u,s),IF(u<>2,TRANSPOSE(IF(x="","",x)),x)))[/COLOR]

LAMBDA 1.1.2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	sample
2	a		2	a		3
3		x		a	x	3
4
5	f,u,s, all omitted - filters only blanks								f,ignored,u,1,s,omitted
6	=AFUSBYROW(A2:F3)								=AFUSBYROW(A2:F3,,1)
7	a	2	a	3					a	2	3
8	x	a	x	3					x	a	3
9
10	=AFUSBYCOL(A2:F3)								=AFUSBYCOL(A2:F3,,1)
11	a	x	2	a	x	3			a	x	2	a	x	3
12				a		3
13
Sheet1

Cell Formulas
Range	Formula
A6,I10,A10,I6	A6	=FORMULATEXT(A7)
A7:D8	A7	=AFUSBYROW(A2:F3)
I7:K8	I7	=AFUSBYROW(A2:F3,,1)
A11:F12	A11	=AFUSBYCOL(A2:F3)
I11:N11	I11	=AFUSBYCOL(A2:F3,,1)
Dynamic array formulas.