AUNIQUE

[Xlambda]

AUNIQUE extracts unique values, horizontal (for each row) left aligned , vertical (for each column) top aligned , all (in a vertical array)
calls AFLATTEN , AUNQSRT

=LAMBDA(a,k,
    LET(x,OR(k={-1,0,1}),
       af,AFLATTEN(a),
       au,UNIQUE(FILTER(af,af<>"")),
       IF(x,SWITCH(k,0,au,1,AUNQSRT(a,),-1,TRANSPOSE(AUNQSRT(TRANSPOSE(a),))),"-1 vert., 0 all vert , 1 horiz.")
   )
)

LAMBDA 5.0.xlsx
	A	B	C	D	E	F	G	H
1	d	d	4		unq. H	↔	k=1
2	1	g	h		d	4
3	1	d	h		1	g	h
4	a	3	4		1	d	h
5	d	4	4		a	3	4
6	3		3		d	4
7	c	2	2		3
8					c	2
9
10	uniq.all	↕	k=0		unq. V	↕	k= -1
11	d				d	d	4
12	4				1	g	h
13	1				a	3	3
14	g				3	4	2
15	h				c	2
16	a
17	3
18	c
19	2
20
AUNIQUE post

Cell Formulas
Range	Formula
E2:G8	E2	=AUNIQUE(A1:C7,1)
A11:A19	A11	=AUNIQUE(A1:C7,)
E11:G15	E11	=AUNIQUE(A1:C7,-1)
Dynamic array formulas.

 

[Xlambda]

New YT link of same challenge: Power Query Split To Rows For 2 Similar Columns - 2520
Solution without using RBYROW, single cell lambda formula F3:

=VSTACK(
    B3:D3,
    DROP(
        REDUCE(
            0,
            SEQUENCE(ROWS(B4:D13)),
            LAMBDA(v, i, LET(x, INDEX(B4:D13, i, ), j, IFNA(TEXTSPLIT(TEXTJOIN("|", , x), ",", "|"), INDEX(x, 1)), VSTACK(v, TRANSPOSE(IFERROR(--j, j)))))
        ),
        1
    )
)

AUNIQUE.xlsx
	A	B	C	D	E	F	G	H	I
1						lambda formula without RBYROW:
2
3		Invoices	Products	Quantities		Invoices	Products	Quantities
4		1001	T,B,P,W,H,O	3,18,12,10,6,1		1001	T	3
5		1002	Q,H,O,B,S,V	2,11,4,16,7,1		1001	B	18
6		1003	G,Y,R	11,2,8		1001	P	12
7		1004	J,C,G,N	3,14,4,14		1001	W	10
8		1005	C,K,D,A	18,17,12,19		1001	H	6
9		1006	C,T,G,S,W	6,10,18,1,18		1001	O	1
10		1007	J,S,W,Q	13,15,5,8		1002	Q	2
11		1008	Z,X,S,T	5,12,12,12		1002	H	11
12		1009	G,F,P,J,X	17,8,9,2,14		1002	O	4
13		1010	F,J,T,Y,A	17,3,15,6,17		1002	B	16
14						1002	S	7
15						1002	V	1
16						1003	G	11
17						1003	Y	2
18						1003	R	8
19						1004	J	3
20						1004	C	14
21						1004	G	4
22						1004	N	14
23						1005	C	18
24						1005	K	17
25						1005	D	12
26						1005	A	19
27						1006	C	6
28						1006	T	10
29						1006	G	18
30						1006	S	1
31						1006	W	18
32						1007	J	13
33						1007	S	15
34						1007	W	5
35						1007	Q	8
36						1008	Z	5
37						1008	X	12
38						1008	S	12
39						1008	T	12
40						1009	G	17
41						1009	F	8
42						1009	P	9
43						1009	J	2
44						1009	X	14
45						1010	F	17
46						1010	J	3
47						1010	T	15
48						1010	Y	6
49						1010	A	17
50
Split to rows

Cell Formulas
Range	Formula
F3:H49	F3	=VSTACK(B3:D3,DROP(REDUCE(0,SEQUENCE(ROWS(B4:D13)),LAMBDA(v,i,LET(x,INDEX(B4:D13,i,),j,IFNA(TEXTSPLIT(TEXTJOIN("|",,x),",","|"),INDEX(x,1)),VSTACK(v,TRANSPOSE(IFERROR(--j,j)))))),1))
Dynamic array formulas.

 

[Xlambda]

What if the initial data array receives more data columns?
1st scenario: new data is consistent by row with nr.elements/cell, but has different delimiters and even empty strings (records)
Note: Formula gets even shorter, no need to stack the headers if we include them in the initial array.

AUNIQUE.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1		1st scenario, more columns, consistent elements
2								=RBYROW(B3:F13,LAMBDA(x,LET(j,IFNA(TEXTSPLIT(TEXTJOIN("|",,x),{",",";"},"|"),INDEX(x,1)),TRANSPOSE(IFERROR(--j,j)))))
3		Invoices	Products	Quantities	Revenues	Region		Invoices	Products	Quantities	Revenues	Region
4		1001	T,B,P,W,H,O	3,18,12,10,6,1	21,,59,14,30,25	v;;w;r;n;i		1001	T	3	21	v
5		1002	Q,H,O,B,S,V	2,11,4,16,,1	28,48,86,15,,84	j;y;u;y;;q		1001	B	18
6		1003	G,Y,R	11,2,8	12,27,56	x;b;e		1001	P	12	59	w
7		1004	J,C,G,N	3,14,4,14	53,94,38,60	j;u;g;o		1001	W	10	14	r
8		1005	C,K,D,A	18,17,12,19	48,85,12,18	f;j;q;l		1001	H	6	30	n
9		1006	C,T,G,S,W	6,10,18,1,18	39,28,83,89,67	b;t;k;h;t		1001	O	1	25	i
10		1007	J,S,W,Q	13,15,5,8	51,24,33,52	y;z;m;i		1002	Q	2	28	j
11		1008	Z,X,S,T	5,12,12,12	88,29,20,42	p;r;c;i		1002	H	11	48	y
12		1009	G,F,P,J,X	17,8,9,2,14	99,97,11,40,88	m;e;u;q;m		1002	O	4	86	u
13		1010	F,J,T,Y,A	17,3,15,6,17	90,13,79,57,37	u;y;w;g;f		1002	B	16	15	y
14								1002	S
15								1002	V	1	84	q
16								1003	G	11	12	x
17								1003	Y	2	27	b
18								1003	R	8	56	e
19								1004	J	3	53	j
20								1004	C	14	94	u
21								1004	G	4	38	g
22								1004	N	14	60	o
23								1005	C	18	48	f
24								1005	K	17	85	j
25								1005	D	12	12	q
26								1005	A	19	18	l
27								1006	C	6	39	b
28								1006	T	10	28	t
29								1006	G	18	83	k
30								1006	S	1	89	h
31								1006	W	18	67	t
32								1007	J	13	51	y
33								1007	S	15	24	z
34								1007	W	5	33	m
35								1007	Q	8	52	i
36								1008	Z	5	88	p
37								1008	X	12	29	r
38								1008	S	12	20	c
39								1008	T	12	42	i
40								1009	G	17	99	m
41								1009	F	8	97	e
42								1009	P	9	11	u
43								1009	J	2	40	q
44								1009	X	14	88	m
45								1010	F	17	90	u
46								1010	J	3	13	y
47								1010	T	15	79	w
48								1010	Y	6	57	g
49								1010	A	17	37	f
50
Split to rows 1

Cell Formulas
Range	Formula
H2	H2	=FORMULATEXT(H3)
H3:L49	H3	=RBYROW(B3:F13,LAMBDA(x,LET(j,IFNA(TEXTSPLIT(TEXTJOIN("|",,x),{",",";"},"|"),INDEX(x,1)),TRANSPOSE(IFERROR(--j,j)))))
Dynamic array formulas.

 

[Xlambda]

2nd scenario: added more columns, not consistent data (elements/cell/row not always the same), 3 different in cell delimiters between elements.
Fun fact: PT style report for inconsistent data, returned with the shortest single cell formula.
Needless to say, we can add also as many rows as we want.

AUNIQUE.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1		1st scenario, more columns, consistent elements
2										=RBYROW(B3:H13,LAMBDA(x,LET(j,TEXTSPLIT(TEXTJOIN("|",,x),{",",";","/"},"|"),TRANSPOSE(IFERROR(--j,j)))))
3		Invoices	Agent	Branch	Products	Quantities	Revenues	Region		Invoices	Agent	Branch	Products	Quantities	Revenues	Region
4		1001	Ax By	LA/NY	T,B,P,W,H,O	3,18,12,10,6	21,,59,14,30,25	v;;w;r;n;i		1001	Ax By	LA	T	3	21	v
5		1002	Cx Ty	IL/KY/ME	Q,H,O,B,S,V	2,11,4,16,7,1	28,,86,15	j;y;u;y;;q				NY	B	18
6		1003	Kx Wy	CT/DE	G,Y,R	11,2,8	12,27,56	x;b;e					P	12	59	w
7		1004	Px My/Ex Dy	FL/KS/ID/MI	J,C,G,N	3,14,4,14	53,94,38	j;u;g;o					W	10	14	r
8		1005	Dx Zy	MN/MI	C,K,D,A	18,17,12,19	48,85,12,18	f;j;q;l					H	6	30	n
9		1006	Fx Ry	FL	C,T,,S,W	6,,18,1,18	39,28,,89,67	b;t;k;h;t					O		25	i
10		1007	Jx Gy/Tx Sy	NY	J,S,W,Q	13,15,5,8	51,24,33,52	y;z;m;i		1002	Cx Ty	IL	Q	2	28	j
11		1008	Ox Cy	UT/TX	Z,X,S,T	5,12,12,12	88,29,20,42	p;r;c;i				KY	H	11		y
12		1009	Ux Qy	TN	G,F,P,J,X	17,8,9,2,14	99,97,,40	m;e;u;q;m				ME	O	4	86	u
13		1010	Ex Wy	OR/OK/OH	F,J,T,Y,A	17,3,15,6,17	90,13,79,57	u;y;w;g;f					B	16	15	y
14													S	7
15													V	1		q
16										1003	Kx Wy	CT	G	11	12	x
17												DE	Y	2	27	b
18													R	8	56	e
19										1004	Px My	FL	J	3	53	j
20											Ex Dy	KS	C	14	94	u
21												ID	G	4	38	g
22												MI	N	14		o
23										1005	Dx Zy	MN	C	18	48	f
24												MI	K	17	85	j
25													D	12	12	q
26													A	19	18	l
27										1006	Fx Ry	FL	C	6	39	b
28													T		28	t
29														18		k
30													S	1	89	h
31													W	18	67	t
32										1007	Jx Gy	NY	J	13	51	y
33											Tx Sy		S	15	24	z
34													W	5	33	m
35													Q	8	52	i
36										1008	Ox Cy	UT	Z	5	88	p
37												TX	X	12	29	r
38													S	12	20	c
39													T	12	42	i
40										1009	Ux Qy	TN	G	17	99	m
41													F	8	97	e
42													P	9		u
43													J	2	40	q
44													X	14		m
45										1010	Ex Wy	OR	F	17	90	u
46												OK	J	3	13	y
47												OH	T	15	79	w
48													Y	6	57	g
49													A	17		f
50
Split to rows 2

Cell Formulas
Range	Formula
J2	J2	=FORMULATEXT(J3)
J3:P49	J3	=RBYROW(B3:H13,LAMBDA(x,LET(j,TEXTSPLIT(TEXTJOIN("|",,x),{",",";","/"},"|"),TRANSPOSE(IFERROR(--j,j)))))
Dynamic array formulas.

 

[jaeiow]

When we design functions, it is not only about what the function can achieve or how often we are going to use them. Since all these new functions environment is new to all of us, we have to take any challenge and find ways to solve them. More for the sake of testing and learning "programing" techniques and concepts. Once these techniques will be mastered and understood, anybody will design their own functions very well fitted for their particular needs and never use any of them here.
My work here will be worth something not when my functions will be used 1 on 1, but when will help others develop their own ones.
And another important thing. No function is "final". Any function can be improved, updated or transformed, and any function, if we want to find ways to break it, is so easy.

Ok, let's see if this fits your needs better. (sorry for the rough presentation, did not have anything prepared yet, few examples for you only)
CBYCOL(ar,fn) Colum BYCOL. Can spill any function bycol. (including UNIQUE)

=LAMBDA(ar, fn,
    LET(
        a, IF(ar = "", "", ar),
        w, --(ROWS(a) = 1),
        r, REDUCE(0, SEQUENCE(COLUMNS(a)), LAMBDA(v, i, HSTACK(v, fn(INDEX(a, w, i))))),
        DROP(IFNA(r, ""), , 1)
    )
)

To check if any data exists in the col, do we use index/match, filter, or something else? I know Filter works, but I'm willing to bet there is a faster solution, including Find the First Non-blank Value in a Row, but is there anything shorter?

Edit, it seems COUNTA is capable by itself, but not on number columns containing quasi blanks

 

[Xlambda]

To check if any data exists in the col, do we use index/match, filter, or something else? I know Filter works, but I'm willing to bet there is a faster solution, including Find the First Non-blank Value in a Row, but is there anything shorter?

Edit, it seems COUNTA is capable by itself, but not on number columns containing quasi blanks

To check if any data "exists" we can use =AND(array=""). If true, no data (empty strings only)

 

[jaeiow]

To check if any data "exists" we can use =AND(array=""). If true, no data (empty strings only)

/thank you.

(works in bycol and cbycol).

 

[Xlambda]

/thank you.

(works in bycol and cbycol).

You're welcome! Yes, CBYCOL should be used only if the results spill bycol, for sigle results/col, BYCOL does the job.
Anyhow, for this type of scenarios had designed a while ago AHCLEAN and AVCLEAN. Check if it helps.
This is the concept behind 2 of the most common cases.

AUNIQUE.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
1									1.
2									exclude empty clms
3		sample							=BYCOL(B4:G9,LAMBDA(x,AND(x="")))							=FILTER(B4:G9,NOT(I4#))					=LET(a,B4:G9,b,IF(a="","",a),FILTER(b,NOT(I4#)))
4		a	4		11		e		FALSE	FALSE	TRUE	FALSE	TRUE	FALSE		a	4	11	e		a	4	11	e
5		b	5				f									b	5	0	f		b	5		f
6		c	6		12		20									c	6	12	20		c	6	12	20
7		1	7				g									1	7	0	g		1	7		g
8		2			10		30									2	0	10	30		2		10	30
9		3	d		x		h									3	d	x	h		3	d	x	h
10
11		=AVCLEAN(B4:G9,ROWS(B4:G9)-1)																			=FILTER(IF(B4:G9="","",B4:G9),NOT(BYCOL(B4:G9,LAMBDA(x,AND(x="")))))
12		a	4	11	e																a	4	11	e
13		b	5		f																b	5		f
14		c	6	12	20																c	6	12	20
15		1	7		g																1	7		g
16		2		10	30																2		10	30
17		3	d	x	h																3	d	x	h
18
19									2.
20									keep only "full" clms
21									=BYCOL(B4:G9,LAMBDA(x,OR(x="")))												=FILTER(B4:G9,NOT(I22#))
22									FALSE	TRUE	TRUE	TRUE	TRUE	FALSE							a	e
23																					b	f
24																					c	20
25																					1	g
26																					2	30
27																					3	h
28
29		=AVCLEAN(B4:G9)																			=FILTER(B4:G9,NOT(BYCOL(B4:G9,LAMBDA(x,OR(x="")))))
30		a	e																		a	e
31		b	f																		b	f
32		c	20																		c	20
33		1	g																		1	g
34		2	30																		2	30
35		3	h																		3	h
36
Sheet4

Cell Formulas
Range	Formula
I3,U29,B29,U21,I21,U11,B11,P3,U3	I3	=FORMULATEXT(I4)
I4:N4	I4	=BYCOL(B4:G9,LAMBDA(x,AND(x="")))
P4:S9	P4	=FILTER(B4:G9,NOT(I4#))
U4:X9	U4	=LET(a,B4:G9,b,IF(a="","",a),FILTER(b,NOT(I4#)))
B12:E17	B12	=AVCLEAN(B4:G9,ROWS(B4:G9)-1)
U12:X17	U12	=FILTER(IF(B4:G9="","",B4:G9),NOT(BYCOL(B4:G9,LAMBDA(x,AND(x="")))))
I22:N22	I22	=BYCOL(B4:G9,LAMBDA(x,OR(x="")))
U22:V27	U22	=FILTER(B4:G9,NOT(I22#))
B30:C35	B30	=AVCLEAN(B4:G9)
U30:V35	U30	=FILTER(B4:G9,NOT(BYCOL(B4:G9,LAMBDA(x,OR(x="")))))
Dynamic array formulas.

 

[jaeiow]

Thank you for setting the record with these functions made over a year ago. The lambda forum always surprises me. Filtering on the result bycol/row is pretty and easy to teach to new users when it makes a nice True and False

 

[Xlambda]

Thank you for setting the record with these functions made over a year ago. The lambda forum always surprises me. Filtering on the result bycol/row is pretty and easy to teach to new users when it makes a nice True and False

You're welcome!! Here is a similar scenario when CBYCOL really comes handy. Filtering only for numeric values.
For fun, let's define 2 basic super simple functions: Filter Numbers FLTNR(a) and simple variant of AVCLEAN, Vertical Clean, VCLN(a) (where a: array)
Example of how simple modular lambda routines, properly assembled, can make a complicated task, a walk in a park.
FLTNR(a)

=LAMBDA(a, FILTER(a,ISNUMBER(a),""))

VCLN(a)

=LAMBDA(a,FILTER(a,NOT(BYCOL(a,LAMBDA(x,AND(x=""))))))

PS: Great news, Advanced Formula Environment add-in got a big update. Looking forward to Bill's YT on the matter. From the changes I have seen, Excel team has big plans for helping lambda "programmers".

AUNIQUE.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1
2		sample								=CBYCOL(B3:H9,FLTNR)								=VCLN(J3#)
3		4	6	1	a	9	G	x		4	6	1		9		6		4	6	1	9	6
4		3	a	2	b	10	H	y		3	2	2		10		2		3	2	2	10	2
5		1	a	3	c	20	J	6		1	6	3		20		9		1	6	3	20	9
6		2	2	4	d	30	R	2		2		4		30		2		2		4	30	2
7		b	6	5	e	23	Y	9				5		23		7				5	23	7
8		c	b	6	f	43	U	2				6		43						6	43
9		a	b	7	g	56	W	7				7		56						7	56
10
11		single cell
12		=VCLN(CBYCOL(B3:H9,FLTNR))
13		4	6	1	9	6
14		3	2	2	10	2
15		1	6	3	20	9
16		2		4	30	2
17				5	23	7
18				6	43
19				7	56
20
Sheet6

Cell Formulas
Range	Formula
J2,B12,R2	J2	=FORMULATEXT(J3)
J3:P9	J3	=CBYCOL(B3:H9,FLTNR)
R3:V9	R3	=VCLN(J3#)
B13:F19	B13	=VCLN(CBYCOL(B3:H9,FLTNR))
Dynamic array formulas.

 

[jaeiow]

This is one of the formulas I rewrote today with some of the additions from this thread. , It used to be spread over named ranges, but it is easier to read now, at least, since learning how to excel "program" and relying on the advanced formula environment. Uses elements from various lambdas posted here, but can be used on it's own. specific use case though, TOFILE: concatenate headers with cells and use the same delimiter to complete a one-column flat file for a table. Now, I have TOFILE, a utility to make generating flat files easier for a program where I work. (particularly for digest by RPA), this file can be useful, because we don't always have the headers, and need a subsection of rows. As an aside, I don't know if concatenation (the "&") is faster that if this was done inside the Lambda. I suspect that the lambda needs to run through each row, so limiting the arguments is going to be better.

=LAMBDA(a,header,delimiter,
    LET(
        column, IF(ISERROR(a), "", IF(a = "", "", a)),
        isvis, BYCOL(header, LAMBDA(l, LARGE(CELL("width", l), 1))),
        onlyvisible, FILTER(column, isvis <> 0),
        isempty, NOT(BYCOL(onlyvisible, LAMBDA(x, AND(x = "")))),
        cleaned, FILTER(onlyvisible, isempty),
        headers, FILTER(header, isvis <> 0),
        heading, FILTER(headers, isempty),
        everything, IF(ISBLANK(DROP(heading, -1)), "", heading & delimiter & cleaned),
        BYROW(everything, LAMBDA(a, TEXTJOIN(delimiter, , a)))
    )
)