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
860
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
I guess the only need for me of a Unique replacement is to use unique for a certain column. I don't see that in this tutorial.
 
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)
Indeed, the new functions are beyond amazing. They are like a Bugatti hyper car, but, on a kart's racetrack. Extremely capable but cannot go "fast" enough. Nested iterations struggle, 1M rows are so tight now. We need at least data model's capabilities.
Anyhow, managed to "squeeze" min, max and all comparison operations under same argument, so no extra argument. New function.
ROWSUNQ(ar,[oc],[ex])
[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()
- oc as string :
"-" triggers min(oc)
"+" triggers max(oc)
"<n" , ">n" triggers filtering for oc<n , oc>n
"/n", "\n" triggers filtering for oc<=n, oc>=n
"#n" triggers filtering for oc<>n
[ex]: exact argument
- if ex omitted or 0, case-insensitive
- if ex=1 or <>0, case-sensitive (triggers EXACT function)
Note: This is the first draft, and like UNQROWS, does not use UNIQUE at all. I am positive that using UNIQUE can speed up the calculations when we do not need EXACT.
Also, I will do some speed tests to justify why I used CONCAT. CONCAT speeds up the calculations considerably but comes with the CONCAT string limitation of 32767 char long strings.
Since these functions concat only horizontally, it is unlikely to reach the limitations but we should be aware of it.
Excel Formula:
=LAMBDA(ar, [oc], [ex],
    LET(
        a, IF(ar = "", "", ar),
        t, TYPE(oc),
        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))))),
        l, LEFT(oc, 1),
        v, --RIGHT(oc, LEN(oc) - 1),
        w, SWITCH(l, "+", m = MAX(m), "-", m = MIN(m), "<", m < v, ">", m > v, "/", m <= v, "\", m >= v, "#", m <> v),
        SWITCH(oc, -1, HSTACK(r, m), 0, r, IF(t = 1, FILTER(r, m = oc, NA()), HSTACK(FILTER(r, w, NA()), FILTER(m, w, NA()))))
    )
)
AUNIQUE.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1Part 1. ROWSUNQ, ex, omitted
2spin ?noc,-1oc,"-" => min(oc)oc,"\5"=> oc>=5oc,">3"oc,"#3" => oc<>3
3=ROWSUNQ(D4#,-1)=ROWSUNQ(D4#,"-")=ROWSUNQ(D4#,"\5")=ROWSUNQ(D4#,">3")=ROWSUNQ(D4#,"#3")
4Aaaaa9ab2aa9aa9aa9
5BaAab2ac5bc4ab2
6Cabbc4oc,"+" => max(oc)cB6ac5bc4
7abcac5=ROWSUNQ(D4#,"+")bb5cB6ac5
8bbCcB6aa9bb5cB6
9cacbb5oc,"/3" => oc<=3bb5
10AACa3=ROWSUNQ(D4#,"/3")oc,"<5"
11Aacc3ab2=ROWSUNQ(D4#,"<5")oc,3
12cBBa3Ca3ab2=ROWSUNQ(D4#,3)
13cbcc3bc4Ca
14aAcheck (UNQROWS)Ba3Ca3cc
15bb=UNQROWS(D4#,-1)cc3Ba
16bcaa9Ba3
17bBab2
18bBbc4oc,7oc,"\10" => oc>=10oc,"%" wrong char
19AAac5=ROWSUNQ(D4#,7)=ROWSUNQ(D4#,"\10")=ROWSUNQ(D4#,"%")
20CacB6#N/A#N/A#N/A
21AAbb5
22AcCa3
23cccc3
24ACBa3circular reference
25aCsample formula to freeze RANDARRAY to recalculate
26BB=AND(G4#=G16#)D4: =IF(D2="y",INDEX(B4:B9,RANDARRAY(40,2,1,6,1)),D4#)
27abTRUE
28bB
29BC
30cb
31Ba
32Ca
33bA
34cC
35aA
36Cb
37Ba
38cc
39AC
40cB
41ca
42cB
43AA
44
ROWSUNQ 1
Cell Formulas
RangeFormula
G3,K3,O3,S3,W3,G26,O19,S19,W19,G15,W12,S11,O10,K7G3=FORMULATEXT(G4)
D4:E43D4=IF(D2="y",INDEX(B4:B9,RANDARRAY(40,2,1,6,1)),D4#)
G4:I12G4=ROWSUNQ(D4#,-1)
K4:M4K4=ROWSUNQ(D4#,"-")
O4:Q7O4=ROWSUNQ(D4#,"\5")
S4:U8S4=ROWSUNQ(D4#,">3")
W4:Y9W4=ROWSUNQ(D4#,"#3")
K8:M8K8=ROWSUNQ(D4#,"+")
O11:Q14O11=ROWSUNQ(D4#,"/3")
S12:U16S12=ROWSUNQ(D4#,"<5")
W13:X15W13=ROWSUNQ(D4#,3)
G16:I24G16=UNQROWS(D4#,-1)
O20O20=ROWSUNQ(D4#,7)
S20S20=ROWSUNQ(D4#,"\10")
W20W20=ROWSUNQ(D4#,"%")
G27G27=AND(G4#=G16#)
Dynamic array formulas.
 
AUNIQUE.xlsx
BCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAF
1Part 2. ROWSUNQ, ex,1
2spin ?ncheck (UNQROWS)oc,-1oc,"-" => min(oc)oc,"\3"=> oc>=3oc,">1"oc,"#1" => oc<>1
3=UNQROWS(D4#,-1,1)=ROWSUNQ(D4#,-1,1)=ROWSUNQ(D4#,"-",1)=ROWSUNQ(D4#,"\3",1)=ROWSUNQ(D4#,">1",1)=ROWSUNQ(D4#,"#1",1)
4Aaaaa1aa1aa1aA3aA3aA3
5BaAaA3aA3bC1AA4ab2ab2
6Cabab2ab2ac1cB3bc2bc2
7abcbc2bc2Aa1bB3AA4AA4
8bbCbC1bC1bb1cB3cB3
9cacac1ac1Ac1oc,"/2" => oc<=2cb2cb2
10AAAA4AA4aC1=ROWSUNQ(D4#,"/2",1)bB3bB3
11AaAa1Aa1BB1aa1Ca2Ca2
12cBcB3cB3BC1ab2cc2cc2
13cbcb2cb2bA1bc2AC2AC2
14aAbb1bb1cC1bC1Ba2Ba2
15bbbB3bB3Cb1ac1
16bcCa2Ca2ca1Aa1oc,"<4"oc,1
17bBAc1Ac1cb2=ROWSUNQ(D4#,"<4",1)=ROWSUNQ(D4#,1,1)
18bBcc2cc2oc,"+" => max(oc)bb1aa1aa
19AAAC2AC2=ROWSUNQ(D4#,"+",1)Ca2aA3bC
20CaaC1aC1AA4Ac1ab2ac
21AABB1BB1cc2bc2Aa
22AcBC1BC1AC2bC1bb
23ccBa2Ba2aC1ac1Ac
24ACbA1bA1BB1Aa1aC
25aCcC1cC1BC1cB3BB
26BBCb1Cb1Ba2cb2BC
27abca1ca1bA1bb1bA
28bBcC1bB3cC
29BCCb1Ca2Cb
30cbca1Ac1ca
31Bacc2
32CaAC2
33bAaC1
34cCBB1
35aABC1
36CbBa2
37BabA1
38cccC1
39ACCb1
40cBca1
41ca
42cB
43AA
44
ROWSUNQ 1
Cell Formulas
RangeFormula
G3,K3,O3,S3,W3,AB3,O19,W17,AB17,S10G3=FORMULATEXT(G4)
D4:E43D4=IF(D2="y",INDEX(B4:B9,RANDARRAY(40,2,1,6,1)),D4#)
G4:I27G4=UNQROWS(D4#,-1,1)
K4:M27K4=ROWSUNQ(D4#,-1,1)
O4:Q16O4=ROWSUNQ(D4#,"-",1)
S4:U7S4=ROWSUNQ(D4#,"\3",1)
W4:Y14W4=ROWSUNQ(D4#,">1",1)
AB4:AD14AB4=ROWSUNQ(D4#,"#1",1)
S11:U30S11=ROWSUNQ(D4#,"/2",1)
W18:Y40W18=ROWSUNQ(D4#,"<4",1)
AB18:AC30AB18=ROWSUNQ(D4#,1,1)
O20:Q20O20=ROWSUNQ(D4#,"+",1)
Dynamic array formulas.
 
I guess the only need for me of a Unique replacement is to use unique for a certain column. I don't see that in this tutorial.
Sorry @jaeiow , still a lot of posts to come. When this will be finished it will cover all possible scenarios of UNIQUE I can think of. Unfortunately, I can do this only in my spare time and editing the posts is 10 times more time consuming than designing a formula. If you can be more specific, at least I can tell you if it was already taken in consideration or not.
So far, I have done unique rows (case sensitive capable, total count unique rows capable, occurrence nr. extractions). and unique byrow or any other function that can spill rows (horizontally).
To come: unique columns, unique bycol , probably this Sunday. ✌️
PS: UNQROWS and ROWSUNQ (previous 2 posts) can handle column vectors, not only arrays. Check post #3
 
In the notes to MrExcel's video, he notes something about unique by certain columns, but does not talk in the video about that topic. Any of the lambdas' I don't see what I would think is a column index argument. This might be what you refer to as unique bycol. The only way I did acheive this was to use your AFLAT with the occurrences, (first choosecol on an array, apply UNQROWS) then sort the expanded array, and h-stack with the sorted original array minus the choose col from previous step. But seems like too much sorting and mismatch problems with case-insensitive applied.
 
In the notes to MrExcel's video, he notes something about unique by certain columns, but does not talk in the video about that topic. Any of the lambdas' I don't see what I would think is a column index argument. This might be what you refer to as unique bycol. The only way I did acheive this was to use your AFLAT with the occurrences, (first choosecol on an array, apply UNQROWS) then sort the expanded array, and h-stack with the sorted original array minus the choose col from previous step. But seems like too much sorting and mismatch problems with case-insensitive applied.
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)
Excel Formula:
=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)
    )
)
AUNIQUE.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQAR
1CBYCOL works with any function , not only UNIQUE
2=RANDARRAY(7,20,1,6,1)=CBYCOL(SEQUENCE(,7),LAMBDA(x,SEQUENCE(x)))
3146222132515525436151111111
452632232161234326416222222
53526525126511355453533333
6353125126614531242114444
736543315636452261251555
86626241356221452161166
9455516415642661135317
10
11Unique bycolTop 3 largest, exclude ties
12=CBYCOL(B3#,LAMBDA(x,UNIQUE(x)))=CBYCOL(B3#,LAMBDA(x,LARGE(UNIQUE(x),SEQUENCE(3))))
131462221325155254361566665655666566566656
145223553216523432643655553543555454354535
153536335163611315455144342432234233243411
1666511445524662612
1744641
185
19Unique with CHOOSECOLS
20Sort Unique bycol=CBYCOL(CHOOSECOLS(B3#,{1,3,5,7,9,11}),LAMBDA(x,UNIQUE(x)))
21=CBYCOL(B3#,LAMBDA(x,SORT(UNIQUE(x))))162121
2212211211131112111211525315
2334322332252233223435333566
2445533443564454344556651452
255664555565566556644
2665666
276
28
29Sort Unique Filter bycol
30=CBYCOL(B3#,LAMBDA(x,SORT(UNIQUE(FILTER(x,x>=3)))))
3134333333534433343435
3245545445655554554556
335665556666666
34666
35
CBYCOL
Cell Formulas
RangeFormula
B2,B30,B21,X20,X12,B12,X2B2=FORMULATEXT(B3)
B3:U9B3=RANDARRAY(7,20,1,6,1)
X3:AD9X3=CBYCOL(SEQUENCE(,7),LAMBDA(x,SEQUENCE(x)))
B13:U18B13=CBYCOL(B3#,LAMBDA(x,UNIQUE(x)))
X13:AQ15X13=CBYCOL(B3#,LAMBDA(x,LARGE(UNIQUE(x),SEQUENCE(3))))
X21:AC25X21=CBYCOL(CHOOSECOLS(B3#,{1,3,5,7,9,11}),LAMBDA(x,UNIQUE(x)))
B22:U27B22=CBYCOL(B3#,LAMBDA(x,SORT(UNIQUE(x))))
B31:U34B31=CBYCOL(B3#,LAMBDA(x,SORT(UNIQUE(FILTER(x,x>=3)))))
Dynamic array formulas.
 
Thanks for the bycol formula, but my primary aim is for a "sort by rows for certain column", not whole row, not by col. The idea is to move files with VBA, or something else, where files are getting renamed with a counter (2,3, etc.), because the same file name exists more than once. I take your ROWSUNQ with -1,1 args. But only on the filename. There are other columns that describe the file. So after, we have to sort the expanded rows (according to occurrences generated by ROWSUNQ: the 2nd occurrence will get " (2)" appended to the filename, and so on), and match them back to the descriptor fields. This is an HSTACK with both in ascending order...

Full formula:

Excel Formula:
=LET(de, SUBSTITUTE('File Plan'!B3#,"    ",""),bc, ROWSUNQ(CHOOSECOLS(ATEXTSPLIT(de,"~",1),-1),-1,1),
    ATEXTJOIN(HSTACK(SORT(DROP(IFDATE(ATEXTSPLIT(de,"~",1), TEXT(ATEXTSPLIT(de,"~",1),"MM/dd/yyyy")),,-1),2,1), SUBSTITUTE(SORT(REDUCE(SEQUENCE(SUM(CHOOSECOLS(bc,2))),
        SEQUENCE(ROWS(CHOOSECOLS(bc,2))),
        LET(a,CHOOSECOLS(bc,2),
            b, CHOOSECOLS(bc,1),LAMBDA(v,i,IF(v<=SUM(INDEX(a,SEQUENCE(i))),
LET(c, MOD(v,INDEX(a,i)), d, IF(c=0,IF(INDEX(a,i)=1,"",INDEX(a,i)),IF(c=1,"",c)),IF(d<>"",
TEXTBEFORE(INDEX(b,i),".",-1)&"abc"&"("&d&")."&TEXTAFTER(INDEX(b,i),".",-1),INDEX(b,i))),v)))),,1),"abc"&"("," (")),"~",1))
 
Thanks for the bycol formula, but my primary aim is for a "sort by rows for certain column", not whole row, not by col. The idea is to move files with VBA, or something else, where files are getting renamed with a counter (2,3, etc.), because the same file name exists more than once. I take your ROWSUNQ with -1,1 args. But only on the filename. There are other columns that describe the file. So after, we have to sort the expanded rows (according to occurrences generated by ROWSUNQ: the 2nd occurrence will get " (2)" appended to the filename, and so on), and match them back to the descriptor fields. This is an HSTACK with both in ascending order...
You're welcome, !!Wow!! Super cool !! ✌️
 
Another challenge from @MrExcel , check this cool PQ solution: Split To Rows For Multiple Columns
This is how it can be solved using RBYROW :
AUNIQUE.xlsx
ABCDEFGHIJKLMNOPQRS
1Split To Rows For Multiple Columnslambda formula:
2=VSTACK(B3:D3,RBYROW(B4:D13,LAMBDA(x,LET(j,IFNA(TEXTSPLIT(TEXTJOIN("|",,x),",","|"),INDEX(x,1)),TRANSPOSE(IFERROR(--j,j))))))
3InvoicesProductsQuantitiesInvoicesProductsQuantities
41001T,B,P,W,H,O3,18,12,10,6,11001T3steps for 1 row
51002Q,H,O,B,S,V2,11,4,16,7,11001B18=TEXTJOIN("|",,B4:D4)
61003G,Y,R11,2,81001P121001|T,B,P,W,H,O|3,18,12,10,6,1
71004J,C,G,N3,14,4,141001W10
81005C,K,D,A18,17,12,191001H6=IFNA(TEXTSPLIT(J6,",","|"),INDEX(B4:D4,1))
91006C,T,G,S,W6,10,18,1,181001O1100110011001100110011001
101007J,S,W,Q13,15,5,81002Q2TBPWHO
111008Z,X,S,T5,12,12,121002H11318121061
121009G,F,P,J,X17,8,9,2,141002O4
131010F,J,T,Y,A17,3,15,6,171002B16=TRANSPOSE(IFERROR(--J9#,J9#))
141002S71001T3
151002V11001B18
161003G111001P12
171003Y21001W10
181003R81001H6
191004J31001O1
201004C14
211004G4
221004N14
231005C18
241005K17
251005D12
261005A19
271006C6
281006T10
291006G18
301006S1
311006W18
321007J13
331007S15
341007W5
351007Q8
361008Z5
371008X12
381008S12
391008T12
401009G17
411009F8
421009P9
431009J2
441009X14
451010F17
461010J3
471010T15
481010Y6
491010A17
50
Split to rows
Cell Formulas
RangeFormula
F2,J13,J8,J5F2=FORMULATEXT(F3)
F3:H49F3=VSTACK(B3:D3,RBYROW(B4:D13,LAMBDA(x,LET(j,IFNA(TEXTSPLIT(TEXTJOIN("|",,x),",","|"),INDEX(x,1)),TRANSPOSE(IFERROR(--j,j))))))
J6J6=TEXTJOIN("|",,B4:D4)
J9:O11J9=IFNA(TEXTSPLIT(J6,",","|"),INDEX(B4:D4,1))
J14:L19J14=TRANSPOSE(IFERROR(--J9#,J9#))
Dynamic array formulas.
 
Great application of how to do this without any M-Code, on Invoices, Products, Quantities!
 

Forum statistics

Threads
1,223,897
Messages
6,175,270
Members
452,628
Latest member
dd2

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