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



## Xlambda (Mar 13, 2021)

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.xlsxABCDEFGH1dd4unq. H↔k=121ghd431dh1gh4a341dh5d44a34633d47c2238c2910uniq.all↕k=0unq. V↕k= -111ddd41241gh131a3314g34215hc216a17318c19220AUNIQUE postCell FormulasRangeFormulaE2:G8E2=AUNIQUE(A1:C7,1)A11:A19A11=AUNIQUE(A1:C7,)E11:G15E11=AUNIQUE(A1:C7,-1)Dynamic array formulas.


----------



## Xlambda (Oct 9, 2022)

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.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAI1 =UNIQUE(array,[by_col],[exactly_once])231. UNIQUE is case-insensitive41.a. UNIQUE rows, "by_col" argument omitted1.b. UNIQUE columns, "by_col" argument = 156ar=UNIQUE(B7:B12)ar=UNIQUE(J7:L12)arAabBaraAbBcc7AAaaAaaAaABbc8abAAabB0=UNIQUE(T6:W6,1)Aa9bcbBc00Ab10BdBbcc0=UNIQUE(AC6:AH8,1)11ccblanks=> 0'sabcc12dccaB0c13A00014blanks=> 0's15162. UNIQUE "exactly_once" argument=1 (but not twice, 3 times..etc)171.a. UNIQUE rows, "by_col" argument omitted1.b. UNIQUE columns, "by_col" argument = 11819ar=UNIQUE(B20:B25,,1)ar=UNIQUE(J20:L25,,1)arAabBcdaraAbBcc20AcaaAc00aABbc21adAAacc0=UNIQUE(T19:Y19,1,1)Aa22bbBblanks=> 0'scd23BBb=UNIQUE(AC19:AH21,1,1)24cccc25dcc0c260027blanks=> 0's28concept 1Cell FormulasRangeFormulaD6,AC23,T21,N19,D19,AC10,T8,N6D6=FORMULATEXT(D7)D7:D10D7=UNIQUE(B7:B12)N7:P10N7=UNIQUE(J7:L12)T9:U9T9=UNIQUE(T6:W6,1)AC11:AF13AC11=UNIQUE(AC6:AH8,1)D20:D21D20=UNIQUE(B20:B25,,1)N20:P21N20=UNIQUE(J20:L25,,1)T22:U22T22=UNIQUE(T19:Y19,1,1)AC24:AD26AC24=UNIQUE(AC19:AH21,1,1)Dynamic array formulas.


----------



## Xlambda (Oct 9, 2022)

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.xlsxABCDEFGHIJKLMNO11. ex, omitted2. ex,12oc,omittedoc,1oc,omittedoc,13ar=UNQROWS(B4:B20)=UNQROWS(B4:B20,1)=UNQROWS(B4:B20,,1)=UNQROWS(B4:B20,1,1)4AAeAC5AbFad6acbD7ad=UNIQUE(B4:B20,,1)Be8aeecF9bFFC10bdoc,311B=UNIQUE(B4:B20)oc,5D=UNQROWS(B4:B20,3,1)12BA=UNQROWS(B4:B20,5)ea13BbAFB14ccb15cdoc,-1oc,216Ceoc,2=UNQROWS(B4:B20,-1,1)=UNQROWS(B4:B20,2,1)17dF=UNQROWS(B4:B20,2)A2A18Dda3b19eoc,-1b2c20F=UNQROWS(B4:B20,-1)oc,3B321A5=UNQROWS(B4:B20,3)c2oc,422b5cC1=UNQROWS(B4:B20,4,1)23c3d1#N/A24d2oc,4D125e1=UNQROWS(B4:B20,4)e126F1#N/AF127UNQROWS 1Cell FormulasRangeFormulaD3,G3,G25,M22,G21,D20,G17,J16,M16,G12,M11,D11,G7,J3,M3D3=FORMULATEXT(D4)D4:D9D4=UNQROWS(B4:B20)G4:G5G4=UNQROWS(B4:B20,1)J4:J13J4=UNQROWS(B4:B20,,1)M4:M8M4=UNQROWS(B4:B20,1,1)G8:G9G8=UNIQUE(B4:B20,,1)D12:D17D12=UNIQUE(B4:B20)M12:M13M12=UNQROWS(B4:B20,3,1)G13:G14G13=UNQROWS(B4:B20,5)J17:K26J17=UNQROWS(B4:B20,-1,1)M17:M19M17=UNQROWS(B4:B20,2,1)G18G18=UNQROWS(B4:B20,2)D21:E26D21=UNQROWS(B4:B20,-1)G22G22=UNQROWS(B4:B20,3)M23M23=UNQROWS(B4:B20,4,1)G26G26=UNQROWS(B4:B20,4)Dynamic array formulas.


----------



## Xlambda (Oct 9, 2022)

AUNIQUE.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAA11. ex, omitted2. ex,123ar=UNQROWS(B4:D20)=UNQROWS(B4:D20,-1)=UNQROWS(B4:D20,,1)=UNQROWS(B4:D20,1,1)4aaAaaAaaA7aaAaaa5AAabbbb7AAaAAA6aaacc1aaabb7AAAcccc1AAABB8aaAcccccc1bbc9aaABBcc10AAa=UNIQUE(B4:D20)=UNQROWS(B4:D20,1)bBccc11bbaaAcBb12BBbb0ccc=UNQROWS(B4:D20,2,1)13bBc00cccccAAa14Bbcc0cccBb15bBccc=UNIQUE(B4:D20,,1)16bBc00=UNQROWS(B4:D20,-1,1)=UNQROWS(B4:D20,3,1)17Bbcc0aaA3aaA18ccccAAa2bB19ccaaa120ccc=UNQROWS(B4:D20,7)AAA121aaAbb122bbBB123bB324=UNQROWS(B4:D20,3)Bb225#N/Ac126cc127ccc128UNQROWS 2Cell FormulasRangeFormulaF3,K24,K20,Q16,V16,K15,V12,K10,F10,Q3,V3,K3F3=FORMULATEXT(F4)F4:H8F4=UNQROWS(B4:D20)K4:N8K4=UNQROWS(B4:D20,-1)Q4:S14Q4=UNQROWS(B4:D20,,1)V4:X10V4=UNQROWS(B4:D20,1,1)F11:H15F11=UNIQUE(B4:D20)K11:M13K11=UNQROWS(B4:D20,1)V13:X14V13=UNQROWS(B4:D20,2,1)K16:M18K16=UNIQUE(B4:D20,,1)Q17:T27Q17=UNQROWS(B4:D20,-1,1)V17:X18V17=UNQROWS(B4:D20,3,1)K21:M22K21=UNQROWS(B4:D20,7)K25K25=UNQROWS(B4:D20,3)Dynamic array formulas.


----------



## Xlambda (Oct 9, 2022)

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.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD1Some RBYROW examples. (pattern's paradise)2The only lambda helper function that can expand rows3=RBYROW(SEQUENCE(5),LAMBDA(x,SEQUENCE(,x)))41rpt rows=RBYROW(SEQUENCE(3),LAMBDA(x,SEQUENCE(INDEX(P5:P7,x))^0*x))512Ax316123By41=INDEX(N5:O7,R5#,{1,2})71234Cz21Ax8123452Ax92Ax10=RBYROW(SEQUENCE(5,,5,-1),LAMBDA(x,SEQUENCE(,x)))2By11123452By1212343By131233By1412Cz151Cz1617=RBYROW(SEQUENCE(4,2),LAMBDA(x,SEQUENCE(,MAX(x))))1812191234=RBYROW(SEQUENCE(5),LAMBDA(x,SEQUENCE(3,x)))20123456121123456782=RBYROW(SEQUENCE(5),LAMBDA(x,SEQUENCE(x,x)))223123121224343425561232612345627456789287891234291234567830567891011123191011121314151632123451234533678910678910341112131415111213141535161718192036212223242537RBYROW 1Cell FormulasRangeFormulaB3,U21,N19,B17,B10,T6,R4B3=FORMULATEXT(B4)B4:F8B4=RBYROW(SEQUENCE(5),LAMBDA(x,SEQUENCE(,x)))R5:R13R5=RBYROW(SEQUENCE(3),LAMBDA(x,SEQUENCE(INDEX(P5:P7,x))^0*x))T7:U15T7=INDEX(N5:O7,R5#,{1,2})B11:F15B11=RBYROW(SEQUENCE(5,,5,-1),LAMBDA(x,SEQUENCE(,x)))B18:I21B18=RBYROW(SEQUENCE(4,2),LAMBDA(x,SEQUENCE(,MAX(x))))N20:R34N20=RBYROW(SEQUENCE(5),LAMBDA(x,SEQUENCE(3,x)))U22:Y36U22=RBYROW(SEQUENCE(5),LAMBDA(x,SEQUENCE(x,x)))Dynamic array formulas.


----------



## Xlambda (Oct 9, 2022)

AUNIQUE.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD1RBYROW with UNIQUETask: extract unique positive nr., ascending order23ar=RBYROW(B4:K23,LAMBDA(x,UNIQUE(x,1)))=RBYROW(B4:K23,LAMBDA(x,SORT(UNIQUE(FILTER(x,x>0),1),,,1)))40-32213-2-2320-3213-2123523-1-232-201-223-1-2011236-33-12-3-12-11-2-33-121-212371-221013-13-21-2203-11238011-3312-22-101-332-2-112390-310-2-1-3-2000-31-2-11102303-2-2-1-330230-2-1-323111-210-1-3-1-1-101-20-1-3112-31-1-2-22-3-1-3-2-31-1-221213-3-13003-11-1-3-3-130113141-21-2020-31-11-202-3-11215-1-310-2-233-20-1-310-231316-12-11-120-101-1210121712001-20000120-21218-3-3-3-231-20-2-3-3-231013193-10-2-1-1-2-1-133-10-23202-2-32023-3302-2-303232103-22-3-3-123003-22-3-123221-311-202-1-1-11-3-202-1122303-310-21-30103-31-21324RBYROW 2Cell FormulasRangeFormulaM3M3=FORMULATEXT(N4)V3V3=FORMULATEXT(V4)N4:T23N4=RBYROW(B4:K23,LAMBDA(x,UNIQUE(x,1)))V4:X23V4=RBYROW(B4:K23,LAMBDA(x,SORT(UNIQUE(FILTER(x,x>0),1),,,1)))Dynamic array formulas.


----------



## Xlambda (Oct 9, 2022)

AUNIQUE.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAI1RBYROW with UNIQUE, various2Unique Unique numbers, vertical array (expanding rows), ascending order byrow3ar=RBYROW(B4:L7,LAMBDA(x,UNIQUE(x,1)))=RBYROW(B4:L7,LAMBDA(x,SORT(TOCOL(UNIQUE(FILTER(x,ISNUMBER(x)),1)))))4bb22aaa133cb2a13c15dacccddc233dac2326789988775667895637zyyxx443355zyx4352839Unique text only510=RBYROW(B4:L7,LAMBDA(x,UNIQUE(FILTER(x,ISTEXT(x),""),1)))611bac712dac813914zyx315416Unique numbers, ascending order517=RBYROW(B4:L7,LAMBDA(x,SORT(UNIQUE(FILTER(x,ISNUMBER(x),""),1),,,1)))18123192320567892134522RBYROW 3Cell FormulasRangeFormulaN3,V3N3=FORMULATEXT(N4)N4:S7N4=RBYROW(B4:L7,LAMBDA(x,UNIQUE(x,1)))V4:V16V4=RBYROW(B4:L7,LAMBDA(x,SORT(TOCOL(UNIQUE(FILTER(x,ISNUMBER(x)),1)))))J10J10=FORMULATEXT(N11)N11:P14N11=RBYROW(B4:L7,LAMBDA(x,UNIQUE(FILTER(x,ISTEXT(x),""),1)))H17H17=FORMULATEXT(N18)N18:R21N18=RBYROW(B4:L7,LAMBDA(x,SORT(UNIQUE(FILTER(x,ISNUMBER(x),""),1),,,1)))Dynamic array formulas.


----------



## smozgur (Oct 10, 2022)

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 (Oct 11, 2022)

smozgur said:


> 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 (Oct 11, 2022)

Xlambda said:


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


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)


----------



## Xlambda (Mar 13, 2021)

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.xlsxABCDEFGH1dd4unq. H↔k=121ghd431dh1gh4a341dh5d44a34633d47c2238c2910uniq.all↕k=0unq. V↕k= -111ddd41241gh131a3314g34215hc216a17318c19220AUNIQUE postCell FormulasRangeFormulaE2:G8E2=AUNIQUE(A1:C7,1)A11:A19A11=AUNIQUE(A1:C7,)E11:G15E11=AUNIQUE(A1:C7,-1)Dynamic array formulas.


----------



## jaeiow (Oct 11, 2022)

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.


----------



## Xlambda (Oct 12, 2022)

smozgur said:


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


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.

```
=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.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAA1Part 1. ROWSUNQ, ex, omitted2spin ?noc,-1oc,"-" => min(oc)oc,"\5"=> oc>=5oc,">3"oc,"#3" => oc<>33=ROWSUNQ(D4#,-1)=ROWSUNQ(D4#,"-")=ROWSUNQ(D4#,"\5")=ROWSUNQ(D4#,">3")=ROWSUNQ(D4#,"#3")4Aaaaa9ab2aa9aa9aa95BaAab2ac5bc4ab26Cabbc4oc,"+" => max(oc)cB6ac5bc47abcac5=ROWSUNQ(D4#,"+")bb5cB6ac58bbCcB6aa9bb5cB69cacbb5oc,"/3" => oc<=3bb510AACa3=ROWSUNQ(D4#,"/3")oc,"<5"11Aacc3ab2=ROWSUNQ(D4#,"<5")oc,312cBBa3Ca3ab2=ROWSUNQ(D4#,3)13cbcc3bc4Ca14aAcheck (UNQROWS)Ba3Ca3cc15bb=UNQROWS(D4#,-1)cc3Ba16bcaa9Ba317bBab218bBbc4oc,7oc,"\10"  => oc>=10oc,"%" wrong char19AAac5=ROWSUNQ(D4#,7)=ROWSUNQ(D4#,"\10")=ROWSUNQ(D4#,"%")20CacB6#N/A#N/A#N/A21AAbb522AcCa323cccc324ACBa3circular reference25aCsample formula to freeze RANDARRAY to recalculate26BB=AND(G4#=G16#)D4: =IF(D2="y",INDEX(B4:B9,RANDARRAY(40,2,1,6,1)),D4#)27abTRUE28bB29BC30cb31Ba32Ca33bA34cC35aA36Cb37Ba38cc39AC40cB41ca42cB43AA44ROWSUNQ 1Cell FormulasRangeFormulaG3,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.


----------



## Xlambda (Oct 12, 2022)

AUNIQUE.xlsxBCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAF1Part 2. ROWSUNQ, ex,12spin ?ncheck (UNQROWS)oc,-1oc,"-" => min(oc)oc,"\3"=> oc>=3oc,">1"oc,"#1" => oc<>13=UNQROWS(D4#,-1,1)=ROWSUNQ(D4#,-1,1)=ROWSUNQ(D4#,"-",1)=ROWSUNQ(D4#,"\3",1)=ROWSUNQ(D4#,">1",1)=ROWSUNQ(D4#,"#1",1)4Aaaaa1aa1aa1aA3aA3aA35BaAaA3aA3bC1AA4ab2ab26Cabab2ab2ac1cB3bc2bc27abcbc2bc2Aa1bB3AA4AA48bbCbC1bC1bb1cB3cB39cacac1ac1Ac1oc,"/2" => oc<=2cb2cb210AAAA4AA4aC1=ROWSUNQ(D4#,"/2",1)bB3bB311AaAa1Aa1BB1aa1Ca2Ca212cBcB3cB3BC1ab2cc2cc213cbcb2cb2bA1bc2AC2AC214aAbb1bb1cC1bC1Ba2Ba215bbbB3bB3Cb1ac116bcCa2Ca2ca1Aa1oc,"<4"oc,117bBAc1Ac1cb2=ROWSUNQ(D4#,"<4",1)=ROWSUNQ(D4#,1,1)18bBcc2cc2oc,"+" => max(oc)bb1aa1aa19AAAC2AC2=ROWSUNQ(D4#,"+",1)Ca2aA3bC20CaaC1aC1AA4Ac1ab2ac21AABB1BB1cc2bc2Aa22AcBC1BC1AC2bC1bb23ccBa2Ba2aC1ac1Ac24ACbA1bA1BB1Aa1aC25aCcC1cC1BC1cB3BB26BBCb1Cb1Ba2cb2BC27abca1ca1bA1bb1bA28bBcC1bB3cC29BCCb1Ca2Cb30cbca1Ac1ca31Bacc232CaAC233bAaC134cCBB135aABC136CbBa237BabA138cccC139ACCb140cBca141ca42cB43AA44ROWSUNQ 1Cell FormulasRangeFormulaG3,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.


----------



## Xlambda (Oct 12, 2022)

jaeiow said:


> 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


----------



## jaeiow (Oct 12, 2022)

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.


----------



## Xlambda (Oct 13, 2022)

jaeiow said:


> 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)

```
=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.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQAR1CBYCOL works with any function , not only UNIQUE2=RANDARRAY(7,20,1,6,1)=CBYCOL(SEQUENCE(,7),LAMBDA(x,SEQUENCE(x)))31462221325155254361511111114526322321612343264162222225352652512651135545353333363531251266145312421144447365433156364522612515558662624135622145216116694555164156426611353171011Unique bycolTop 3 largest, exclude ties12=CBYCOL(B3#,LAMBDA(x,UNIQUE(x)))=CBYCOL(B3#,LAMBDA(x,LARGE(UNIQUE(x),SEQUENCE(3))))1314622213251552543615666656556665665666561452235532165234326436555535435554543545351535363351636113154551443424322342332434111666511445524662612174464118519Unique with  CHOOSECOLS20Sort Unique bycol=CBYCOL(CHOOSECOLS(B3#,{1,3,5,7,9,11}),LAMBDA(x,UNIQUE(x)))21=CBYCOL(B3#,LAMBDA(x,SORT(UNIQUE(x))))16212122122112111311121112115253152334322332252233223435333566244553344356445434455665145225566455556556655664426656662762829Sort Unique Filter bycol30=CBYCOL(B3#,LAMBDA(x,SORT(UNIQUE(FILTER(x,x>=3)))))313433333353443334343532455454456555545545563356655566666663466635CBYCOLCell FormulasRangeFormulaB2,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.


----------



## jaeiow (Oct 13, 2022)

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:


```
=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))
```


----------



## Xlambda (Oct 13, 2022)

jaeiow said:


> 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 !! ✌️


----------



## Xlambda (Oct 13, 2022)

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.xlsxABCDEFGHIJKLMNOPQRS1Split 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))))))3InvoicesProductsQuantitiesInvoicesProductsQuantities41001T,B,P,W,H,O3,18,12,10,6,11001T3steps for 1 row51002Q,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,171004J,C,G,N3,14,4,141001W1081005C,K,D,A18,17,12,191001H6=IFNA(TEXTSPLIT(J6,",","|"),INDEX(B4:D4,1))91006C,T,G,S,W6,10,18,1,181001O1100110011001100110011001101007J,S,W,Q13,15,5,81002Q2TBPWHO111008Z,X,S,T5,12,12,121002H11318121061121009G,F,P,J,X17,8,9,2,141002O4131010F,J,T,Y,A17,3,15,6,171002B16=TRANSPOSE(IFERROR(--J9#,J9#))141002S71001T3151002V11001B18161003G111001P12171003Y21001W10181003R81001H6191004J31001O1201004C14211004G4221004N14231005C18241005K17251005D12261005A19271006C6281006T10291006G18301006S1311006W18321007J13331007S15341007W5351007Q8361008Z5371008X12381008S12391008T12401009G17411009F8421009P9431009J2441009X14451010F17461010J3471010T15481010Y6491010A1750Split to rowsCell FormulasRangeFormulaF2,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.


----------



## st001 (Oct 14, 2022)

Great application of how to do this without any M-Code, on Invoices, Products, Quantities!


----------



## Xlambda (Mar 13, 2021)

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.xlsxABCDEFGH1dd4unq. H↔k=121ghd431dh1gh4a341dh5d44a34633d47c2238c2910uniq.all↕k=0unq. V↕k= -111ddd41241gh131a3314g34215hc216a17318c19220AUNIQUE postCell FormulasRangeFormulaE2:G8E2=AUNIQUE(A1:C7,1)A11:A19A11=AUNIQUE(A1:C7,)E11:G15E11=AUNIQUE(A1:C7,-1)Dynamic array formulas.


----------



## Xlambda (Oct 14, 2022)

*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:

```
=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.xlsxABCDEFGHI1lambda formula without RBYROW:23InvoicesProductsQuantitiesInvoicesProductsQuantities41001T,B,P,W,H,O3,18,12,10,6,11001T351002Q,H,O,B,S,V2,11,4,16,7,11001B1861003G,Y,R11,2,81001P1271004J,C,G,N3,14,4,141001W1081005C,K,D,A18,17,12,191001H691006C,T,G,S,W6,10,18,1,181001O1101007J,S,W,Q13,15,5,81002Q2111008Z,X,S,T5,12,12,121002H11121009G,F,P,J,X17,8,9,2,141002O4131010F,J,T,Y,A17,3,15,6,171002B16141002S7151002V1161003G11171003Y2181003R8191004J3201004C14211004G4221004N14231005C18241005K17251005D12261005A19271006C6281006T10291006G18301006S1311006W18321007J13331007S15341007W5351007Q8361008Z5371008X12381008S12391008T12401009G17411009F8421009P9431009J2441009X14451010F17461010J3471010T15481010Y6491010A1750Split to rowsCell FormulasRangeFormulaF3: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.


----------



## Xlambda (Oct 14, 2022)

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.xlsxABCDEFGHIJKLMNOPQRS11st scenario, more columns, consistent elements2=RBYROW(B3:F13,LAMBDA(x,LET(j,IFNA(TEXTSPLIT(TEXTJOIN("|",,x),{",",";"},"|"),INDEX(x,1)),TRANSPOSE(IFERROR(--j,j)))))3InvoicesProductsQuantitiesRevenuesRegionInvoicesProductsQuantitiesRevenuesRegion41001T,B,P,W,H,O3,18,12,10,6,121,,59,14,30,25v;;w;r;n;i1001T321v51002Q,H,O,B,S,V2,11,4,16,,128,48,86,15,,84j;y;u;y;;q1001B1861003G,Y,R11,2,812,27,56x;b;e1001P1259w71004J,C,G,N3,14,4,1453,94,38,60j;u;g;o1001W1014r81005C,K,D,A18,17,12,1948,85,12,18f;j;q;l1001H630n91006C,T,G,S,W6,10,18,1,1839,28,83,89,67b;t;k;h;t1001O125i101007J,S,W,Q13,15,5,851,24,33,52y;z;m;i1002Q228j111008Z,X,S,T5,12,12,1288,29,20,42p;r;c;i1002H1148y121009G,F,P,J,X17,8,9,2,1499,97,11,40,88m;e;u;q;m1002O486u131010F,J,T,Y,A17,3,15,6,1790,13,79,57,37u;y;w;g;f1002B1615y141002S151002V184q161003G1112x171003Y227b181003R856e191004J353j201004C1494u211004G438g221004N1460o231005C1848f241005K1785j251005D1212q261005A1918l271006C639b281006T1028t291006G1883k301006S189h311006W1867t321007J1351y331007S1524z341007W533m351007Q852i361008Z588p371008X1229r381008S1220c391008T1242i401009G1799m411009F897e421009P911u431009J240q441009X1488m451010F1790u461010J313y471010T1579w481010Y657g491010A1737f50Split to rows 1Cell FormulasRangeFormulaH2H2=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.


----------



## Xlambda (Oct 14, 2022)

*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.xlsxABCDEFGHIJKLMNOPQRSTU11st scenario, more columns, consistent elements2=RBYROW(B3:H13,LAMBDA(x,LET(j,TEXTSPLIT(TEXTJOIN("|",,x),{",",";","/"},"|"),TRANSPOSE(IFERROR(--j,j)))))3InvoicesAgentBranchProductsQuantitiesRevenuesRegionInvoicesAgentBranchProductsQuantitiesRevenuesRegion41001Ax ByLA/NYT,B,P,W,H,O3,18,12,10,621,,59,14,30,25v;;w;r;n;i1001Ax ByLAT321v51002Cx TyIL/KY/MEQ,H,O,B,S,V2,11,4,16,7,128,,86,15j;y;u;y;;qNYB1861003Kx WyCT/DEG,Y,R11,2,812,27,56x;b;eP1259w71004Px My/Ex DyFL/KS/ID/MIJ,C,G,N3,14,4,1453,94,38j;u;g;oW1014r81005Dx ZyMN/MIC,K,D,A18,17,12,1948,85,12,18f;j;q;lH630n91006Fx RyFLC,T,,S,W6,,18,1,1839,28,,89,67b;t;k;h;tO25i101007Jx Gy/Tx SyNYJ,S,W,Q13,15,5,851,24,33,52y;z;m;i1002Cx TyILQ228j111008Ox CyUT/TXZ,X,S,T5,12,12,1288,29,20,42p;r;c;iKYH11y121009Ux QyTNG,F,P,J,X17,8,9,2,1499,97,,40m;e;u;q;mMEO486u131010Ex WyOR/OK/OHF,J,T,Y,A17,3,15,6,1790,13,79,57u;y;w;g;fB1615y14S715V1q161003Kx WyCTG1112x17DEY227b18R856e191004Px MyFLJ353j20Ex DyKSC1494u21IDG438g22MIN14o231005Dx ZyMNC1848f24MIK1785j25D1212q26A1918l271006Fx RyFLC639b28T28t2918k30S189h31W1867t321007Jx GyNYJ1351y33Tx SyS1524z34W533m35Q852i361008Ox CyUTZ588p37TXX1229r38S1220c39T1242i401009Ux QyTNG1799m41F897e42P9u43J240q44X14m451010Ex WyORF1790u46OKJ313y47OHT1579w48Y657g49A17f50Split to rows 2Cell FormulasRangeFormulaJ2J2=FORMULATEXT(J3)J3:P49J3=RBYROW(B3:H13,LAMBDA(x,LET(j,TEXTSPLIT(TEXTJOIN("|",,x),{",",";","/"},"|"),TRANSPOSE(IFERROR(--j,j)))))Dynamic array formulas.


----------



## jaeiow (Nov 9, 2022)

Xlambda said:


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


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


----------



## Xlambda (Nov 13, 2022)

jaeiow said:


> 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)


----------



## jaeiow (Nov 13, 2022)

Xlambda said:


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


----------



## Xlambda (Nov 14, 2022)

jaeiow said:


> /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.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD11.2exclude empty clms3sample=BYCOL(B4:G9,LAMBDA(x,AND(x="")))=FILTER(B4:G9,NOT(I4#))=LET(a,B4:G9,b,IF(a="","",a),FILTER(b,NOT(I4#)))4a411eFALSEFALSETRUEFALSETRUEFALSEa411ea411e5b5fb50fb5f6c61220c61220c61220717g170g17g8210302010302103093dxh3dxh3dxh1011=AVCLEAN(B4:G9,ROWS(B4:G9)-1)=FILTER(IF(B4:G9="","",B4:G9),NOT(BYCOL(B4:G9,LAMBDA(x,AND(x="")))))12a411ea411e13b5fb5f14c61220c612201517g17g162103021030173dxh3dxh18192.20keep only "full" clms21=BYCOL(B4:G9,LAMBDA(x,OR(x="")))=FILTER(B4:G9,NOT(I22#))22FALSETRUETRUETRUETRUEFALSEae23bf24c20251g26230273h2829=AVCLEAN(B4:G9)=FILTER(B4:G9,NOT(BYCOL(B4:G9,LAMBDA(x,OR(x="")))))30aeae31bfbf32c20c20331g1g34230230353h3h36Sheet4Cell FormulasRangeFormulaI3,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.


----------



## jaeiow (Nov 14, 2022)

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


----------



## Xlambda (Nov 20, 2022)

jaeiow said:


> 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)*

```
=LAMBDA(a, FILTER(a,ISNUMBER(a),""))
```
*VCLN(a)*

```
=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.xlsxABCDEFGHIJKLMNOPQRSTUVW12sample=CBYCOL(B3:H9,FLTNR)=VCLN(J3#)3461a9Gx461964619643a2b10Hy32210232210251a3c20J61632091632096224d30R224302243027b65e23Y9523752378cb6f43U26436439ab7g56W77567561011single cell12=VCLN(CBYCOL(B3:H9,FLTNR))134619614322102151632091624302175237186431975620Sheet6Cell FormulasRangeFormulaJ2,B12,R2J2=FORMULATEXT(J3)J3:P9J3=CBYCOL(B3:H9,FLTNR)R3:V9R3=VCLN(J3#)B13:F19B13=VCLN(CBYCOL(B3:H9,FLTNR))Dynamic array formulas.


----------



## jaeiow (Dec 22, 2022)

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.

```
=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)))
    )
)
```


----------



## Xlambda (Mar 13, 2021)

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.xlsxABCDEFGH1dd4unq. H↔k=121ghd431dh1gh4a341dh5d44a34633d47c2238c2910uniq.all↕k=0unq. V↕k= -111ddd41241gh131a3314g34215hc216a17318c19220AUNIQUE postCell FormulasRangeFormulaE2:G8E2=AUNIQUE(A1:C7,1)A11:A19A11=AUNIQUE(A1:C7,)E11:G15E11=AUNIQUE(A1:C7,-1)Dynamic array formulas.


----------



## Xlambda (Dec 25, 2022)

jaeiow said:


> 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 (Dec 25, 2022)

Excel 365 Beta

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


----------



## jaeiow (Dec 29, 2022)

Xlambda said:


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


----------

