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
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:
Excel Formula:
=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
ABCDEFGHI
1lambda formula without RBYROW:
2
3InvoicesProductsQuantitiesInvoicesProductsQuantities
41001T,B,P,W,H,O3,18,12,10,6,11001T3
51002Q,H,O,B,S,V2,11,4,16,7,11001B18
61003G,Y,R11,2,81001P12
71004J,C,G,N3,14,4,141001W10
81005C,K,D,A18,17,12,191001H6
91006C,T,G,S,W6,10,18,1,181001O1
101007J,S,W,Q13,15,5,81002Q2
111008Z,X,S,T5,12,12,121002H11
121009G,F,P,J,X17,8,9,2,141002O4
131010F,J,T,Y,A17,3,15,6,171002B16
141002S7
151002V1
161003G11
171003Y2
181003R8
191004J3
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
F3:H49F3=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.
 
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
ABCDEFGHIJKLMNOPQRS
11st scenario, more columns, consistent elements
2=RBYROW(B3:F13,LAMBDA(x,LET(j,IFNA(TEXTSPLIT(TEXTJOIN("|",,x),{",",";"},"|"),INDEX(x,1)),TRANSPOSE(IFERROR(--j,j)))))
3InvoicesProductsQuantitiesRevenuesRegionInvoicesProductsQuantitiesRevenuesRegion
41001T,B,P,W,H,O3,18,12,10,6,121,,59,14,30,25v;;w;r;n;i1001T321v
51002Q,H,O,B,S,V2,11,4,16,,128,48,86,15,,84j;y;u;y;;q1001B18
61003G,Y,R11,2,812,27,56x;b;e1001P1259w
71004J,C,G,N3,14,4,1453,94,38,60j;u;g;o1001W1014r
81005C,K,D,A18,17,12,1948,85,12,18f;j;q;l1001H630n
91006C,T,G,S,W6,10,18,1,1839,28,83,89,67b;t;k;h;t1001O125i
101007J,S,W,Q13,15,5,851,24,33,52y;z;m;i1002Q228j
111008Z,X,S,T5,12,12,1288,29,20,42p;r;c;i1002H1148y
121009G,F,P,J,X17,8,9,2,1499,97,11,40,88m;e;u;q;m1002O486u
131010F,J,T,Y,A17,3,15,6,1790,13,79,57,37u;y;w;g;f1002B1615y
141002S
151002V184q
161003G1112x
171003Y227b
181003R856e
191004J353j
201004C1494u
211004G438g
221004N1460o
231005C1848f
241005K1785j
251005D1212q
261005A1918l
271006C639b
281006T1028t
291006G1883k
301006S189h
311006W1867t
321007J1351y
331007S1524z
341007W533m
351007Q852i
361008Z588p
371008X1229r
381008S1220c
391008T1242i
401009G1799m
411009F897e
421009P911u
431009J240q
441009X1488m
451010F1790u
461010J313y
471010T1579w
481010Y657g
491010A1737f
50
Split to rows 1
Cell Formulas
RangeFormula
H2H2=FORMULATEXT(H3)
H3:L49H3=RBYROW(B3:F13,LAMBDA(x,LET(j,IFNA(TEXTSPLIT(TEXTJOIN("|",,x),{",",";"},"|"),INDEX(x,1)),TRANSPOSE(IFERROR(--j,j)))))
Dynamic array formulas.
 
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
ABCDEFGHIJKLMNOPQRSTU
11st scenario, more columns, consistent elements
2=RBYROW(B3:H13,LAMBDA(x,LET(j,TEXTSPLIT(TEXTJOIN("|",,x),{",",";","/"},"|"),TRANSPOSE(IFERROR(--j,j)))))
3InvoicesAgentBranchProductsQuantitiesRevenuesRegionInvoicesAgentBranchProductsQuantitiesRevenuesRegion
41001Ax ByLA/NYT,B,P,W,H,O3,18,12,10,621,,59,14,30,25v;;w;r;n;i1001Ax ByLAT321v
51002Cx TyIL/KY/MEQ,H,O,B,S,V2,11,4,16,7,128,,86,15j;y;u;y;;qNYB18
61003Kx WyCT/DEG,Y,R11,2,812,27,56x;b;eP1259w
71004Px My/Ex DyFL/KS/ID/MIJ,C,G,N3,14,4,1453,94,38j;u;g;oW1014r
81005Dx ZyMN/MIC,K,D,A18,17,12,1948,85,12,18f;j;q;lH630n
91006Fx RyFLC,T,,S,W6,,18,1,1839,28,,89,67b;t;k;h;tO25i
101007Jx Gy/Tx SyNYJ,S,W,Q13,15,5,851,24,33,52y;z;m;i1002Cx TyILQ228j
111008Ox CyUT/TXZ,X,S,T5,12,12,1288,29,20,42p;r;c;iKYH11y
121009Ux QyTNG,F,P,J,X17,8,9,2,1499,97,,40m;e;u;q;mMEO486u
131010Ex WyOR/OK/OHF,J,T,Y,A17,3,15,6,1790,13,79,57u;y;w;g;fB1615y
14S7
15V1q
161003Kx WyCTG1112x
17DEY227b
18R856e
191004Px MyFLJ353j
20Ex DyKSC1494u
21IDG438g
22MIN14o
231005Dx ZyMNC1848f
24MIK1785j
25D1212q
26A1918l
271006Fx RyFLC639b
28T28t
2918k
30S189h
31W1867t
321007Jx GyNYJ1351y
33Tx SyS1524z
34W533m
35Q852i
361008Ox CyUTZ588p
37TXX1229r
38S1220c
39T1242i
401009Ux QyTNG1799m
41F897e
42P9u
43J240q
44X14m
451010Ex WyORF1790u
46OKJ313y
47OHT1579w
48Y657g
49A17f
50
Split to rows 2
Cell Formulas
RangeFormula
J2J2=FORMULATEXT(J3)
J3:P49J3=RBYROW(B3:H13,LAMBDA(x,LET(j,TEXTSPLIT(TEXTJOIN("|",,x),{",",";","/"},"|"),TRANSPOSE(IFERROR(--j,j)))))
Dynamic array formulas.
 
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)
    )
)
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
 
Last edited:
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)
 
/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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD
11.
2exclude empty clms
3sample=BYCOL(B4:G9,LAMBDA(x,AND(x="")))=FILTER(B4:G9,NOT(I4#))=LET(a,B4:G9,b,IF(a="","",a),FILTER(b,NOT(I4#)))
4a411eFALSEFALSETRUEFALSETRUEFALSEa411ea411e
5b5fb50fb5f
6c61220c61220c61220
717g170g17g
82103020103021030
93dxh3dxh3dxh
10
11=AVCLEAN(B4:G9,ROWS(B4:G9)-1)=FILTER(IF(B4:G9="","",B4:G9),NOT(BYCOL(B4:G9,LAMBDA(x,AND(x="")))))
12a411ea411e
13b5fb5f
14c61220c61220
1517g17g
162103021030
173dxh3dxh
18
192.
20keep only "full" clms
21=BYCOL(B4:G9,LAMBDA(x,OR(x="")))=FILTER(B4:G9,NOT(I22#))
22FALSETRUETRUETRUETRUEFALSEae
23bf
24c20
251g
26230
273h
28
29=AVCLEAN(B4:G9)=FILTER(B4:G9,NOT(BYCOL(B4:G9,LAMBDA(x,OR(x="")))))
30aeae
31bfbf
32c20c20
331g1g
34230230
353h3h
36
Sheet4
Cell Formulas
RangeFormula
I3,U29,B29,U21,I21,U11,B11,P3,U3I3=FORMULATEXT(I4)
I4:N4I4=BYCOL(B4:G9,LAMBDA(x,AND(x="")))
P4:S9P4=FILTER(B4:G9,NOT(I4#))
U4:X9U4=LET(a,B4:G9,b,IF(a="","",a),FILTER(b,NOT(I4#)))
B12:E17B12=AVCLEAN(B4:G9,ROWS(B4:G9)-1)
U12:X17U12=FILTER(IF(B4:G9="","",B4:G9),NOT(BYCOL(B4:G9,LAMBDA(x,AND(x="")))))
I22:N22I22=BYCOL(B4:G9,LAMBDA(x,OR(x="")))
U22:V27U22=FILTER(B4:G9,NOT(I22#))
B30:C35B30=AVCLEAN(B4:G9)
U30:V35U30=FILTER(B4:G9,NOT(BYCOL(B4:G9,LAMBDA(x,OR(x="")))))
Dynamic array formulas.
 
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
 
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)
Excel Formula:
=LAMBDA(a, FILTER(a,ISNUMBER(a),""))
VCLN(a)
Excel Formula:
=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
ABCDEFGHIJKLMNOPQRSTUVW
1
2sample=CBYCOL(B3:H9,FLTNR)=VCLN(J3#)
3461a9Gx4619646196
43a2b10Hy322102322102
51a3c20J6163209163209
6224d30R22430224302
7b65e23Y952375237
8cb6f43U2643643
9ab7g56W7756756
10
11single cell
12=VCLN(CBYCOL(B3:H9,FLTNR))
1346196
14322102
15163209
1624302
175237
18643
19756
20
Sheet6
Cell Formulas
RangeFormula
J2,B12,R2J2=FORMULATEXT(J3)
J3:P9J3=CBYCOL(B3:H9,FLTNR)
R3:V9R3=VCLN(J3#)
B13:F19B13=VCLN(CBYCOL(B3:H9,FLTNR))
Dynamic array formulas.
 
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.
Excel Formula:
=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)))
    )
)
 

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