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]

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

Wow, looks cool, definitely this deserves a new thread with examples and everything. Consider doing it.

 

[ISY]

Excel 365 Beta

Cell A11
=UNIQUE(A.COL(A1:C7,1))

 

[jaeiow]

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".

What a great utility, comes with problems, AFE to Name Manager and forum do not play nice together. "after 2084 characters"

 

[Xlambda]

Working with pictures in Excel, Extracting only the unique pictures of an array. ISPICTURE and UNQPIC custom lambdas.
Check this out Mike's ExcelIsFun latest YT:

Note: The mini-sheet cannot show the pictures, but you can download the file. I will also attach screen captures.
Download Excel File and zipped folder of pictures: https://excelisfun.net/files/EMT1822.zip
Concept: If we append an empty string to any value type that is not an error will get a text value. For a picture will get an #VALUE! error.
Fun fact: If an array has pics TOCOL(ar,3) will exclude the pics => to keep them => TOCOL(ar)
ISPICTURE(ar)
ar: any array

=LAMBDA(ar,ISERR(IFERROR(ar,"")&""))

UNQPIC(ar)

=LAMBDA(ar,LET(t,TOCOL(ar),SORT(UNIQUE(FILTER(t,ISPICTURE(t))))))

EMT1822.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1									Upper Price	20
2									Lower Price	30
3						Product	Price
4		Product	Picture	Price		Carlota	26.95		Product	Picture	Price
5		Bellen		27.95					Bellen		27.95
6		Bellen Wind Dancer		43.95					Carlota		26.95
7		Deuce		32.95					Sunset		29.95
8		Carlota		26.95		Pictures as lookup values						Pictures as criteria
9		Majestic Beaut		38.95		=XLOOKUP(J5:J7,Pa[Picture],Pa[Product])						=SUMIFS(Pa[Price],Pa[Picture],J5:J7)
10		Sunshine		50.95		Bellen						27.95
11		Quad		43.69		Carlota						26.95
12		Sunset		29.95		Sunset						29.95
13						ISPICTURE function				UNQPIC function
14						=ISPICTURE(I5#)				=UNQPIC(F4:K7)
15						FALSE	TRUE	FALSE
16						FALSE	TRUE	FALSE
17						FALSE	TRUE	FALSE
18
1822 (an)

Cell Formulas
Range	Formula
G4	G4	=XLOOKUP(F5,Pa[Picture],Pa[Price])
F5	F5	=XLOOKUP(F4,Pa[Product],Pa[Picture])
I5:K7	I5	=FILTER(Pa,(Pa[Price]>=J1)*(Pa[Price]<=J2))
F9,J14,F14,L9	F9	=FORMULATEXT(F10)
F10:F12	F10	=XLOOKUP(J5:J7,Pa[Picture],Pa[Product])
L10:L12	L10	=SUMIFS(Pa[Price],Pa[Picture],J5:J7)
F15:H17	F15	=ISPICTURE(I5#)
J15:J17	J15	=UNQPIC(F4:K7)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I5:K8,I10:K12,K9	Cell	does not contain a blank value	text	NO

Cells with Data Validation
Cell	Allow	Criteria
F4	List	=$B$5:$B$12