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]

Latest MrExcel YT video (8Oct2022) triggered me to write this study about all aspects of UNIQUE function and how we can enhance its functionality.
Excel Unique Across Each Individual Row - 2519
Before getting to extracting unique values byrow/bycol/byarray, that can be done with general use lambda helper functions that can spill, in more than 3 different ways, will see how we can improve main UNIQUE function's versatility.
First about UNIQUE functionality and its arguments.

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	AE	AF	AG	AH	AI
1		=UNIQUE(array,[by_col],[exactly_once])
2
3		1. UNIQUE is case-insensitive
4		1.a. UNIQUE rows, "by_col" argument omitted																		1.b. UNIQUE columns, "by_col" argument = 1
5
6		ar		=UNIQUE(B7:B12)						ar				=UNIQUE(J7:L12)					ar	A	a	b	B					ar	a	A	b	B	c	c
7		A		A						a	a	A		a	a	A													a	A	B	b		c
8		a		b						A	A	a		b	B	0				=UNIQUE(T6:W6,1)									A	a
9		b		c						b	B			c	0	0				A	b
10		B		d						B	b			c	c	0													=UNIQUE(AC6:AH8,1)
11		c								c				blanks=> 0's															a	b	c	c
12		d								c	c																		a	B	0	c
13																													A	0	0	0
14																													blanks=> 0's
15
16		2. UNIQUE "exactly_once" argument=1 (but not twice, 3 times..etc)
17		1.a. UNIQUE rows, "by_col" argument omitted																		1.b. UNIQUE columns, "by_col" argument = 1
18
19		ar		=UNIQUE(B20:B25,,1)						ar				=UNIQUE(J20:L25,,1)					ar	A	a	b	B	c	d			ar	a	A	b	B	c	c
20		A		c						a	a	A		c	0	0													a	A	B	b		c
21		a		d						A	A	a		c	c	0				=UNIQUE(T19:Y19,1,1)									A	a
22		b								b	B			blanks=> 0's						c	d
23		B								B	b																		=UNIQUE(AC19:AH21,1,1)
24		c								c																			c	c
25		d								c	c																		0	c
26																													0	0
27																													blanks=> 0's
28
concept 1

Cell Formulas
Range	Formula
D6,AC23,T21,N19,D19,AC10,T8,N6	D6	=FORMULATEXT(D7)
D7:D10	D7	=UNIQUE(B7:B12)
N7:P10	N7	=UNIQUE(J7:L12)
T9:U9	T9	=UNIQUE(T6:W6,1)
AC11:AF13	AC11	=UNIQUE(AC6:AH8,1)
D20:D21	D20	=UNIQUE(B20:B25,,1)
N20:P21	N20	=UNIQUE(J20:L25,,1)
T22:U22	T22	=UNIQUE(T19:Y19,1,1)
AC24:AD26	AC24	=UNIQUE(AC19:AH21,1,1)
Dynamic array formulas.

 

[Xlambda]

Added functionality to the following 2 functions UNQROWS and UNQCOLS.
- occurrence argument "oc"
- case sensitive, "ex" (exact argument)
UNQROWS(ar,[oc],[ex]) Unique Rows
ar: array
[oc]: occurrence argument
- if oc=-1, occurrences distribution next to each unique row
- if oc omitted or 0, unique rows
- if oc>0 unique rows that correspond with occurrence value "oc", if not found=>NA()
[ex]: exact argument
- if ex omitted or 0, case-insensitive
- if ex=1 or <>0, case-sensitive (triggers EXACT function)
Fun fact: concept based only on counting occurrences/exact occurrences, not a single UNIQUE is used
Note: If initial array "ar" has errors we can use as input array IFERROR(ar,...) or anybody can alter the function as they wish. I took care of the blanks though.

=LAMBDA(ar, [oc], [ex],
    LET(
        a, IF(ar = "", "", ar),
        b, BYROW(a, LAMBDA(x, CONCAT(x))),
        s, SCAN(
            0,
            SEQUENCE(ROWS(a)),
            LAMBDA(v, i,
                LET(
                    x, INDEX(b, i),
                    y, INDEX(b, SEQUENCE(i)),
                    SUM(--IF(ex, EXACT(x, y), x = y))
                )
            )
        ),
        f, FILTER(b, s = 1),
        r, FILTER(a, s = 1),
        m, MAP(f, LAMBDA(x, SUM(--(IF(ex, EXACT(x, b), x = b))))),
        SWITCH(oc, -1, HSTACK(r, m), 0, r, FILTER(r, m = oc, NA()))
    )
)

AUNIQUE.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1				1. ex, omitted						2. ex,1
2				oc,omitted			oc,1			oc,omitted			oc,1
3		ar		=UNQROWS(B4:B20)			=UNQROWS(B4:B20,1)			=UNQROWS(B4:B20,,1)			=UNQROWS(B4:B20,1,1)
4		A		A			e			A			C
5		A		b			F			a			d
6		a		c						b			D
7		a		d			=UNIQUE(B4:B20,,1)			B			e
8		a		e			e			c			F
9		b		F			F			C
10		b								d			oc,3
11		B		=UNIQUE(B4:B20)			oc,5			D			=UNQROWS(B4:B20,3,1)
12		B		A			=UNQROWS(B4:B20,5)			e			a
13		B		b			A			F			B
14		c		c			b
15		c		d						oc,-1			oc,2
16		C		e			oc,2			=UNQROWS(B4:B20,-1,1)			=UNQROWS(B4:B20,2,1)
17		d		F			=UNQROWS(B4:B20,2)			A	2		A
18		D					d			a	3		b
19		e		oc,-1						b	2		c
20		F		=UNQROWS(B4:B20,-1)			oc,3			B	3
21				A	5		=UNQROWS(B4:B20,3)			c	2		oc,4
22				b	5		c			C	1		=UNQROWS(B4:B20,4,1)
23				c	3					d	1		#N/A
24				d	2		oc,4			D	1
25				e	1		=UNQROWS(B4:B20,4)			e	1
26				F	1		#N/A			F	1
27
UNQROWS 1

Cell Formulas
Range	Formula
D3,G3,G25,M22,G21,D20,G17,J16,M16,G12,M11,D11,G7,J3,M3	D3	=FORMULATEXT(D4)
D4:D9	D4	=UNQROWS(B4:B20)
G4:G5	G4	=UNQROWS(B4:B20,1)
J4:J13	J4	=UNQROWS(B4:B20,,1)
M4:M8	M4	=UNQROWS(B4:B20,1,1)
G8:G9	G8	=UNIQUE(B4:B20,,1)
D12:D17	D12	=UNIQUE(B4:B20)
M12:M13	M12	=UNQROWS(B4:B20,3,1)
G13:G14	G13	=UNQROWS(B4:B20,5)
J17:K26	J17	=UNQROWS(B4:B20,-1,1)
M17:M19	M17	=UNQROWS(B4:B20,2,1)
G18	G18	=UNQROWS(B4:B20,2)
D21:E26	D21	=UNQROWS(B4:B20,-1)
G22	G22	=UNQROWS(B4:B20,3)
M23	M23	=UNQROWS(B4:B20,4,1)
G26	G26	=UNQROWS(B4:B20,4)
Dynamic array formulas.

 

[Xlambda]

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
1						1. ex, omitted											2. ex,1
2
3		ar				=UNQROWS(B4:D20)					=UNQROWS(B4:D20,-1)						=UNQROWS(B4:D20,,1)					=UNQROWS(B4:D20,1,1)
4		a	a	A		a	a	A			a	a	A	7			a	a	A			a	a	a
5		A	A	a		b	b				b	b		7			A	A	a			A	A	A
6		a	a	a		c					c			1			a	a	a			b	b
7		A	A	A		c	c				c	c		1			A	A	A			B	B
8		a	a	A		c	c	c			c	c	c	1			b	b				c
9		a	a	A													B	B				c	c
10		A	A	a		=UNIQUE(B4:D20)					=UNQROWS(B4:D20,1)						b	B				c	c	c
11		b	b			a	a	A			c						B	b
12		B	B			b	b	0			c	c					c					=UNQROWS(B4:D20,2,1)
13		b	B			c	0	0			c	c	c				c	c				A	A	a
14		B	b			c	c	0									c	c	c			B	b
15		b	B			c	c	c			=UNIQUE(B4:D20,,1)
16		b	B								c	0	0				=UNQROWS(B4:D20,-1,1)					=UNQROWS(B4:D20,3,1)
17		B	b								c	c	0				a	a	A	3		a	a	A
18		c									c	c	c				A	A	a	2		b	B
19		c	c														a	a	a	1
20		c	c	c							=UNQROWS(B4:D20,7)						A	A	A	1
21											a	a	A				b	b		1
22											b	b					B	B		1
23																	b	B		3
24											=UNQROWS(B4:D20,3)						B	b		2
25											#N/A						c			1
26																	c	c		1
27																	c	c	c	1
28
UNQROWS 2

Cell Formulas
Range	Formula
F3,K24,K20,Q16,V16,K15,V12,K10,F10,Q3,V3,K3	F3	=FORMULATEXT(F4)
F4:H8	F4	=UNQROWS(B4:D20)
K4:N8	K4	=UNQROWS(B4:D20,-1)
Q4:S14	Q4	=UNQROWS(B4:D20,,1)
V4:X10	V4	=UNQROWS(B4:D20,1,1)
F11:H15	F11	=UNIQUE(B4:D20)
K11:M13	K11	=UNQROWS(B4:D20,1)
V13:X14	V13	=UNQROWS(B4:D20,2,1)
K16:M18	K16	=UNIQUE(B4:D20,,1)
Q17:T27	Q17	=UNQROWS(B4:D20,-1,1)
V17:X18	V17	=UNQROWS(B4:D20,3,1)
K21:M22	K21	=UNQROWS(B4:D20,7)
K25	K25	=UNQROWS(B4:D20,3)
Dynamic array formulas.

 

[Xlambda]

One of the lambda helper functions that can spill results, Row Byrow : RBYROW
Similar to SPILLBYROWS only without the extensive error management like filtering out errors or ignoring blanks. This can be done through lambda helper argument like we do with all the other built in lambda helper functions. Minimalistic approach.
RBYROW(ar,fn) Row BYROW lambda helper function
ar: array
fn: lambda helper argument

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

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		Some RBYROW examples. (pattern's paradise)
2														The only lambda helper function that can expand rows
3		=RBYROW(SEQUENCE(5),LAMBDA(x,SEQUENCE(,x)))
4		1												rpt rows				=RBYROW(SEQUENCE(3),LAMBDA(x,SEQUENCE(INDEX(P5:P7,x))^0*x))
5		1	2											A	x	3		1
6		1	2	3										B	y	4		1		=INDEX(N5:O7,R5#,{1,2})
7		1	2	3	4									C	z	2		1		A	x
8		1	2	3	4	5												2		A	x
9																		2		A	x
10		=RBYROW(SEQUENCE(5,,5,-1),LAMBDA(x,SEQUENCE(,x)))																2		B	y
11		1	2	3	4	5												2		B	y
12		1	2	3	4													3		B	y
13		1	2	3														3		B	y
14		1	2																	C	z
15		1																		C	z
16
17		=RBYROW(SEQUENCE(4,2),LAMBDA(x,SEQUENCE(,MAX(x))))
18		1	2
19		1	2	3	4									=RBYROW(SEQUENCE(5),LAMBDA(x,SEQUENCE(3,x)))
20		1	2	3	4	5	6							1
21		1	2	3	4	5	6	7	8					2							=RBYROW(SEQUENCE(5),LAMBDA(x,SEQUENCE(x,x)))
22														3							1
23														1	2						1	2
24														3	4						3	4
25														5	6						1	2	3
26														1	2	3					4	5	6
27														4	5	6					7	8	9
28														7	8	9					1	2	3	4
29														1	2	3	4				5	6	7	8
30														5	6	7	8				9	10	11	12
31														9	10	11	12				13	14	15	16
32														1	2	3	4	5			1	2	3	4	5
33														6	7	8	9	10			6	7	8	9	10
34														11	12	13	14	15			11	12	13	14	15
35																					16	17	18	19	20
36																					21	22	23	24	25
37
RBYROW 1

Cell Formulas
Range	Formula
B3,U21,N19,B17,B10,T6,R4	B3	=FORMULATEXT(B4)
B4:F8	B4	=RBYROW(SEQUENCE(5),LAMBDA(x,SEQUENCE(,x)))
R5:R13	R5	=RBYROW(SEQUENCE(3),LAMBDA(x,SEQUENCE(INDEX(P5:P7,x))^0*x))
T7:U15	T7	=INDEX(N5:O7,R5#,{1,2})
B11:F15	B11	=RBYROW(SEQUENCE(5,,5,-1),LAMBDA(x,SEQUENCE(,x)))
B18:I21	B18	=RBYROW(SEQUENCE(4,2),LAMBDA(x,SEQUENCE(,MAX(x))))
N20:R34	N20	=RBYROW(SEQUENCE(5),LAMBDA(x,SEQUENCE(3,x)))
U22:Y36	U22	=RBYROW(SEQUENCE(5),LAMBDA(x,SEQUENCE(x,x)))
Dynamic array formulas.

 

[Xlambda]

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		RBYROW with UNIQUE																				Task: extract unique positive nr., ascending order
2
3		ar											=RBYROW(B4:K23,LAMBDA(x,UNIQUE(x,1)))									=RBYROW(B4:K23,LAMBDA(x,SORT(UNIQUE(FILTER(x,x>0),1),,,1)))
4		0	-3	2	2	1	3	-2	-2	3	2			0	-3	2	1	3	-2			1	2	3
5		2	3	-1	-2	3	2	-2	0	1	-2			2	3	-1	-2	0	1			1	2	3
6		-3	3	-1	2	-3	-1	2	-1	1	-2			-3	3	-1	2	1	-2			1	2	3
7		1	-2	2	1	0	1	3	-1	3	-2			1	-2	2	0	3	-1			1	2	3
8		0	1	1	-3	3	1	2	-2	2	-1			0	1	-3	3	2	-2	-1		1	2	3
9		0	-3	1	0	-2	-1	-3	-2	0	0			0	-3	1	-2	-1				1
10		2	3	0	3	-2	-2	-1	-3	3	0			2	3	0	-2	-1	-3			2	3
11		1	-2	1	0	-1	-3	-1	-1	-1	0			1	-2	0	-1	-3				1
12		-3	1	-1	-2	-2	2	-3	-1	-3	-2			-3	1	-1	-2	2				1	2
13		-3	-1	3	0	0	3	-1	1	-1	-3			-3	-1	3	0	1				1	3
14		1	-2	1	-2	0	2	0	-3	1	-1			1	-2	0	2	-3	-1			1	2
15		-1	-3	1	0	-2	-2	3	3	-2	0			-1	-3	1	0	-2	3			1	3
16		-1	2	-1	1	-1	2	0	-1	0	1			-1	2	1	0					1	2
17		1	2	0	0	1	-2	0	0	0	0			1	2	0	-2					1	2
18		-3	-3	-3	-2	3	1	-2	0	-2	-3			-3	-2	3	1	0				1	3
19		3	-1	0	-2	-1	-1	-2	-1	-1	3			3	-1	0	-2					3
20		2	-2	-3	2	0	2	3	-3	3	0			2	-2	-3	0	3				2	3
21		0	3	-2	2	-3	-3	-1	2	3	0			0	3	-2	2	-3	-1			2	3
22		1	-3	1	1	-2	0	2	-1	-1	-1			1	-3	-2	0	2	-1			1	2
23		0	3	-3	1	0	-2	1	-3	0	1			0	3	-3	1	-2				1	3
24
RBYROW 2

Cell Formulas
Range	Formula
M3	M3	=FORMULATEXT(N4)
V3	V3	=FORMULATEXT(V4)
N4:T23	N4	=RBYROW(B4:K23,LAMBDA(x,UNIQUE(x,1)))
V4:X23	V4	=RBYROW(B4:K23,LAMBDA(x,SORT(UNIQUE(FILTER(x,x>0),1),,,1)))
Dynamic array formulas.

 

[Xlambda]

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	AE	AF	AG	AH	AI
1		RBYROW with UNIQUE, various
2														Unique								Unique numbers, vertical array (expanding rows), ascending order byrow
3		ar												=RBYROW(B4:L7,LAMBDA(x,UNIQUE(x,1)))								=RBYROW(B4:L7,LAMBDA(x,SORT(TOCOL(UNIQUE(FILTER(x,ISNUMBER(x)),1)))))
4		b	b	2	2	a	a	a	1	3	3	c		b	2	a	1	3	c			1
5		d	a	c	c	c	d	d	c	2	3	3		d	a	c	2	3				2
6		7	8	9	9	8	8	7	7	5	6	6		7	8	9	5	6				3
7		z	y	y	x	x	4	4	3	3	5	5		z	y	x	4	3	5			2
8																						3
9														Unique text only								5
10										=RBYROW(B4:L7,LAMBDA(x,UNIQUE(FILTER(x,ISTEXT(x),""),1)))												6
11														b	a	c						7
12														d	a	c						8
13																						9
14														z	y	x						3
15																						4
16														Unique numbers, ascending order								5
17								=RBYROW(B4:L7,LAMBDA(x,SORT(UNIQUE(FILTER(x,ISNUMBER(x),""),1),,,1)))
18														1	2	3
19														2	3
20														5	6	7	8	9
21														3	4	5
22
RBYROW 3

Cell Formulas
Range	Formula
N3,V3	N3	=FORMULATEXT(N4)
N4:S7	N4	=RBYROW(B4:L7,LAMBDA(x,UNIQUE(x,1)))
V4:V16	V4	=RBYROW(B4:L7,LAMBDA(x,SORT(TOCOL(UNIQUE(FILTER(x,ISNUMBER(x)),1)))))
J10	J10	=FORMULATEXT(N11)
N11:P14	N11	=RBYROW(B4:L7,LAMBDA(x,UNIQUE(FILTER(x,ISTEXT(x),""),1)))
H17	H17	=FORMULATEXT(N18)
N18:R21	N18	=RBYROW(B4:L7,LAMBDA(x,SORT(UNIQUE(FILTER(x,ISNUMBER(x),""),1),,,1)))
Dynamic array formulas.

 

[smozgur]

I like the UNQROWS function, @Xlambda.

Pretending to be the user who wants to use this function. I would expect [oc] is the minimum occurrence number instead of finding the exact number of occurrence. Perhaps another flag parameter to define if I want to find the exact occurrence or otherwise.

Nice function. Thank you!

 

[Xlambda]

I like the UNQROWS function, @Xlambda.

Pretending to be the user who wants to use this function. I would expect [oc] is the minimum occurrence number instead of finding the exact number of occurrence. Perhaps another flag parameter to define if I want to find the exact occurrence or otherwise.

Nice function. Thank you!

Thank you Suat!!
The name Excel uses for third argument,"exactly_once", made me extend the logic to "exactly_twice", "exactly_3 times", and so on.
If we have total count, we can go anywhere we want with FILTER.
So, which one you think is more relevant, min(oc), max(oc) or for a given oc, <oc, <=oc, >oc, >=oc, <>oc ?
Or could it be intervals v1<oc<=v2 ?
Let me know and all this can be done easily with an extra argument.

 

[smozgur]

Thank you Suat!!
The name Excel uses for third argument,"exactly_once", made me extend the logic to "exactly_twice", "exactly_3 times", and so on.
If we have total count, we can go anywhere we want with FILTER.
So, which one you think is more relevant, min(oc), max(oc) or for a given oc, <oc, <=oc, >oc, >=oc, <>oc ?
Or could it be intervals v1<oc<=v2 ?
Let me know and all this can be done easily with an extra argument.

Actually, I am amazed to see how possibilities are almost endless by using lambda functions! I remember the first time I met array formulas in Excel when I was trying to solve a 600x600 matrix problem, it was for my final thesis more than two decades ago! Glad Excel was not improved like today because if it was, I wouldn't have needed to learn to program in other languages back then!! I really can't decide if the new functions look make things easy just because I am a programmer or if they are just easy to use for everyone! However, one thing is for sure, they are amazing.

I don't like using string parameters, otherwise, I could say something like =COUNTIF(A1:A5,"<=3") where the string parameter can define the criteria. Instead of that, I would use one extra enum parameter that will represent three conditions to make this function more powerful:
-1 : min(oc)
0 : exact(oc)
1: max(oc)