AUNIQUE

=AUNIQUE(a,k)

a
required. array
k
required -1 vertical, 0 all vertical, 1 horizontal

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

Xlambda

Well-known Member
Joined
Mar 8, 2021
Messages
837
Office Version
  1. 365
Platform
  1. Windows
AUNIQUE extracts unique values, horizontal (for each row) left aligned , vertical (for each column) top aligned , all (in a vertical array)
calls AFLATTEN , AUNQSRT
Excel Formula:
=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
ABCDEFGH
1dd4unq. Hk=1
21ghd4
31dh1gh
4a341dh
5d44a34
633d4
7c223
8c2
9
10uniq.allk=0unq. Vk= -1
11ddd4
1241gh
131a33
14g342
15hc2
16a
173
18c
192
20
AUNIQUE post
Cell Formulas
RangeFormula
E2:G8E2=AUNIQUE(A1:C7,1)
A11:A19A11=AUNIQUE(A1:C7,)
E11:G15E11=AUNIQUE(A1:C7,-1)
Dynamic array formulas.
 
Upvote 0
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. ✌️
 
Excel 365 Beta

Cell A11
=UNIQUE(A.COL(A1:C7,1))
 
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"
 
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
Excel Formula:
=LAMBDA(ar,ISERR(IFERROR(ar,"")&""))
UNQPIC(ar)
Excel Formula:
=LAMBDA(ar,LET(t,TOCOL(ar),SORT(UNIQUE(FILTER(t,ISPICTURE(t))))))
EMT1822.xlsx
ABCDEFGHIJKLMNO
1Upper Price20
2Lower Price30
3ProductPrice
4ProductPicturePriceCarlota26.95ProductPicturePrice
5Bellen27.95 Bellen27.95
6Bellen Wind Dancer43.95Carlota26.95
7Deuce32.95Sunset29.95
8Carlota26.95Pictures as lookup valuesPictures as criteria
9Majestic Beaut38.95=XLOOKUP(J5:J7,Pa[Picture],Pa[Product])=SUMIFS(Pa[Price],Pa[Picture],J5:J7)
10Sunshine50.95Bellen27.95
11Quad43.69Carlota26.95
12Sunset29.95Sunset29.95
13ISPICTURE functionUNQPIC function
14=ISPICTURE(I5#)=UNQPIC(F4:K7)
15FALSETRUEFALSE 
16FALSETRUEFALSE
17FALSETRUEFALSE
18
1822 (an)
Cell Formulas
RangeFormula
G4G4=XLOOKUP(F5,Pa[Picture],Pa[Price])
F5F5=XLOOKUP(F4,Pa[Product],Pa[Picture])
I5:K7I5=FILTER(Pa,(Pa[Price]>=J1)*(Pa[Price]<=J2))
F9,J14,F14,L9F9=FORMULATEXT(F10)
F10:F12F10=XLOOKUP(J5:J7,Pa[Picture],Pa[Product])
L10:L12L10=SUMIFS(Pa[Price],Pa[Picture],J5:J7)
F15:H17F15=ISPICTURE(I5#)
J15:J17J15=UNQPIC(F4:K7)
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
I5:K8,I10:K12,K9Celldoes not contain a blank value textNO
Cells with Data Validation
CellAllowCriteria
F4List=$B$5:$B$12
excel pics.png
 

Forum statistics

Threads
1,216,499
Messages
6,131,012
Members
449,613
Latest member
MedDash99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top