AUNIQUE

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

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

A

B

C

D

E

F

G

H

1

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

a

3

4

6

3

d

4

7

c

2

3

8

c

2

9

10

uniq.all

↕

k=0

unq. V

↕

k= -1

11

d

4

12

4

1

g

h

13

1

a

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

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

A

B

C

D

E

F

G

H

I

1

lambda formula without RBYROW:

2

3

Invoices

Products

Quantities

Invoices

Products

Quantities

4

1001

T,B,P,W,H,O

3,18,12,10,6,1

1001

T

3

5

1002

Q,H,O,B,S,V

2,11,4,16,7,1

1001

B

18

6

1003

G,Y,R

11,2,8

1001

P

12

7

1004

J,C,G,N

3,14,4,14

1001

W

10

8

1005

C,K,D,A

18,17,12,19

1001

H

6

9

1006

C,T,G,S,W

6,10,18,1,18

1001

O

1

10

1007

J,S,W,Q

13,15,5,8

1002

Q

2

11

1008

Z,X,S,T

5,12,12,12

1002

H

11

12

1009

G,F,P,J,X

17,8,9,2,14

1002

O

4

13

1010

F,J,T,Y,A

17,3,15,6,17

1002

B

16

14

1002

S

7

15

1002

V

1

16

1003

G

11

17

1003

Y

2

18

1003

R

8

19

1004

J

3

20

1004

C

14

21

1004

G

4

22

1004

N

14

23

1005

C

18

24

1005

K

17

25

1005

D

12

26

1005

A

19

27

1006

C

6

28

1006

T

10

29

1006

G

18

30

1006

S

1

31

1006

W

18

32

1007

J

13

33

1007

S

15

34

1007

W

5

35

1007

Q

8

36

1008

Z

5

37

1008

X

12

38

1008

S

12

39

1008

T

12

40

1009

G

17

41

1009

F

8

42

1009

P

9

43

1009

J

2

44

1009

X

14

45

1010

F

17

46

1010

J

3

47

1010

T

15

48

1010

Y

6

49

1010

A

17

50

Split to rows

Cell Formulas
Range	Formula
F3:H49	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))
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.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

1

1st scenario, more columns, consistent elements

2

=RBYROW(B3:F13,LAMBDA(x,LET(j,IFNA(TEXTSPLIT(TEXTJOIN("|",,x),{",",";"},"|"),INDEX(x,1)),TRANSPOSE(IFERROR(--j,j)))))

3

Invoices

Products

Quantities

Revenues

Region

Invoices

Products

Quantities

Revenues

Region

4

1001

T,B,P,W,H,O

3,18,12,10,6,1

21,,59,14,30,25

v;;w;r;n;i

1001

T

3

21

v

5

1002

Q,H,O,B,S,V

2,11,4,16,,1

28,48,86,15,,84

j;y;u;y;;q

1001

B

18

6

1003

G,Y,R

11,2,8

12,27,56

x;b;e

1001

P

12

59

w

7

1004

J,C,G,N

3,14,4,14

53,94,38,60

j;u;g;o

1001

W

10

14

r

8

1005

C,K,D,A

18,17,12,19

48,85,12,18

f;j;q;l

1001

H

6

30

n

9

1006

C,T,G,S,W

6,10,18,1,18

39,28,83,89,67

b;t;k;h;t

1001

O

1

25

i

10

1007

J,S,W,Q

13,15,5,8

51,24,33,52

y;z;m;i

1002

Q

2

28

j

11

1008

Z,X,S,T

5,12,12,12

88,29,20,42

p;r;c;i

1002

H

11

48

y

12

1009

G,F,P,J,X

17,8,9,2,14

99,97,11,40,88

m;e;u;q;m

1002

O

4

86

u

13

1010

F,J,T,Y,A

17,3,15,6,17

90,13,79,57,37

u;y;w;g;f

1002

B

16

15

y

14

1002

S

15

1002

V

1

84

q

16

1003

G

11

12

x

17

1003

Y

2

27

b

18

1003

R

8

56

e

19

1004

J

3

53

j

20

1004

C

14

94

u

21

1004

G

4

38

g

22

1004

N

14

60

o

23

1005

C

18

48

f

24

1005

K

17

85

j

25

1005

D

12

q

26

1005

A

19

18

l

27

1006

C

6

39

b

28

1006

T

10

28

t

29

1006

G

18

83

k

30

1006

S

1

89

h

31

1006

W

18

67

t

32

1007

J

13

51

y

33

1007

S

15

24

z

34

1007

W

5

33

m

35

1007

Q

8

52

i

36

1008

Z

5

88

p

37

1008

X

12

29

r

38

1008

S

12

20

c

39

1008

T

12

42

i

40

1009

G

17

99

m

41

1009

F

8

97

e

42

1009

P

9

11

u

43

1009

J

2

40

q

44

1009

X

14

88

m

45

1010

F

17

90

u

46

1010

J

3

13

y

47

1010

T

15

79

w

48

1010

Y

6

57

g

49

1010

A

17

37

f

50

Split to rows 1

Cell Formulas
Range	Formula
H2	H2	=FORMULATEXT(H3)
H3:L49	H3	=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.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

1

1st scenario, more columns, consistent elements

2

=RBYROW(B3:H13,LAMBDA(x,LET(j,TEXTSPLIT(TEXTJOIN("|",,x),{",",";","/"},"|"),TRANSPOSE(IFERROR(--j,j)))))

3

Invoices

Agent

Branch

Products

Quantities

Revenues

Region

Invoices

Agent

Branch

Products

Quantities

Revenues

Region

4

1001

Ax By

LA/NY

T,B,P,W,H,O

3,18,12,10,6

21,,59,14,30,25

v;;w;r;n;i

1001

Ax By

LA

T

3

21

v

5

1002

Cx Ty

IL/KY/ME

Q,H,O,B,S,V

2,11,4,16,7,1

28,,86,15

j;y;u;y;;q

NY

B

18

6

1003

Kx Wy

CT/DE

G,Y,R

11,2,8

12,27,56

x;b;e

P

12

59

w

7

1004

Px My/Ex Dy

FL/KS/ID/MI

J,C,G,N

3,14,4,14

53,94,38

j;u;g;o

W

10

14

r

8

1005

Dx Zy

MN/MI

C,K,D,A

18,17,12,19

48,85,12,18

f;j;q;l

H

6

30

n

9

1006

Fx Ry

FL

C,T,,S,W

6,,18,1,18

39,28,,89,67

b;t;k;h;t

O

25

i

10

1007

Jx Gy/Tx Sy

NY

J,S,W,Q

13,15,5,8

51,24,33,52

y;z;m;i

1002

Cx Ty

IL

Q

2

28

j

11

1008

Ox Cy

UT/TX

Z,X,S,T

5,12,12,12

88,29,20,42

p;r;c;i

KY

H

11

y

12

1009

Ux Qy

TN

G,F,P,J,X

17,8,9,2,14

99,97,,40

m;e;u;q;m

ME

O

4

86

u

13

1010

Ex Wy

OR/OK/OH

F,J,T,Y,A

17,3,15,6,17

90,13,79,57

u;y;w;g;f

B

16

15

y

14

S

7

15

V

1

q

16

1003

Kx Wy

CT

G

11

12

x

17

DE

Y

2

27

b

18

R

8

56

e

19

1004

Px My

FL

J

3

53

j

20

Ex Dy

KS

C

14

94

u

21

ID

G

4

38

g

22

MI

N

14

o

23

1005

Dx Zy

MN

C

18

48

f

24

MI

K

17

85

j

25

D

12

q

26

A

19

18

l

27

1006

Fx Ry

FL

C

6

39

b

28

T

28

t

29

18

k

30

S

1

89

h

31

W

18

67

t

32

1007

Jx Gy

NY

J

13

51

y

33

Tx Sy

S

15

24

z

34

W

5

33

m

35

Q

8

52

i

36

1008

Ox Cy

UT

Z

5

88

p

37

TX

X

12

29

r

38

S

12

20

c

39

T

12

42

i

40

1009

Ux Qy

TN

G

17

99

m

41

F

8

97

e

42

P

9

u

43

J

2

40

q

44

X

14

m

45

1010

Ex Wy

OR

F

17

90

u

46

OK

J

3

13

y

47

OH

T

15

79

w

48

Y

6

57

g

49

A

17

f

50

Split to rows 2

Cell Formulas
Range	Formula
J2	J2	=FORMULATEXT(J3)
J3:P49	J3	=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.

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

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

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

1

1.

2

exclude empty clms

3

sample

=BYCOL(B4:G9,LAMBDA(x,AND(x="")))

=FILTER(B4:G9,NOT(I4#))

=LET(a,B4:G9,b,IF(a="","",a),FILTER(b,NOT(I4#)))

4

a

4

11

e

FALSE

TRUE

FALSE

TRUE

FALSE

a

4

11

e

a

4

11

e

5

b

5

f

b

5

0

f

b

5

f

6

c

6

12

20

c

6

12

20

c

6

12

20

7

1

7

g

1

7

0

g

1

7

g

8

2

10

30

2

0

10

30

2

10

30

9

3

d

x

h

3

d

x

h

3

d

x

h

10

11

=AVCLEAN(B4:G9,ROWS(B4:G9)-1)

=FILTER(IF(B4:G9="","",B4:G9),NOT(BYCOL(B4:G9,LAMBDA(x,AND(x="")))))

12

a

4

11

e

a

4

11

e

13

b

5

f

b

5

f

14

c

6

12

20

c

6

12

20

15

1

7

g

1

7

g

16

2

10

30

2

10

30

17

3

d

x

h

3

d

x

h

18

19

2.

20

keep only "full" clms

21

=BYCOL(B4:G9,LAMBDA(x,OR(x="")))

=FILTER(B4:G9,NOT(I22#))

22

FALSE

TRUE

FALSE

a

e

23

b

f

24

c

20

25

1

g

26

2

30

27

3

h

28

29

=AVCLEAN(B4:G9)

=FILTER(B4:G9,NOT(BYCOL(B4:G9,LAMBDA(x,OR(x="")))))

30

a

e

a

e

31

b

f

b

f

32

c

20

c

20

33

1

g

1

g

34

2

30

2

30

35

3

h

3

h

36

Sheet4

Cell Formulas
Range	Formula
I3,U29,B29,U21,I21,U11,B11,P3,U3	I3	=FORMULATEXT(I4)
I4:N4	I4	=BYCOL(B4:G9,LAMBDA(x,AND(x="")))
P4:S9	P4	=FILTER(B4:G9,NOT(I4#))
U4:X9	U4	=LET(a,B4:G9,b,IF(a="","",a),FILTER(b,NOT(I4#)))
B12:E17	B12	=AVCLEAN(B4:G9,ROWS(B4:G9)-1)
U12:X17	U12	=FILTER(IF(B4:G9="","",B4:G9),NOT(BYCOL(B4:G9,LAMBDA(x,AND(x="")))))
I22:N22	I22	=BYCOL(B4:G9,LAMBDA(x,OR(x="")))
U22:V27	U22	=FILTER(B4:G9,NOT(I22#))
B30:C35	B30	=AVCLEAN(B4:G9)
U30:V35	U30	=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)

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

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

1

2

sample

=CBYCOL(B3:H9,FLTNR)

=VCLN(J3#)

3

4

6

1

a

9

G

x

4

6

1

9

6

4

6

1

9

6

4

3

a

2

b

10

H

y

3

2

10

2

3

2

10

2

5

1

a

3

c

20

J

6

1

6

3

20

9

1

6

3

20

9

6

2

4

d

30

R

2

4

30

2

4

30

2

7

b

6

5

e

23

Y

9

5

23

7

5

23

7

8

c

b

6

f

43

U

2

6

43

6

43

9

a

b

7

g

56

W

7

56

7

56

10

11

single cell

12

=VCLN(CBYCOL(B3:H9,FLTNR))

13

4

6

1

9

6

14

3

2

10

2

15

1

6

3

20

9

16

2

4

30

2

17

5

23

7

18

6

43

19

7

56

20

Sheet6

Cell Formulas
Range	Formula
J2,B12,R2	J2	=FORMULATEXT(J3)
J3:P9	J3	=CBYCOL(B3:H9,FLTNR)
R3:V9	R3	=VCLN(J3#)
B13:F19	B13	=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.

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

AUNIQUE

Xlambda

Well-known Member

Xlambda

Well-known Member

Xlambda

Well-known Member

Xlambda

Well-known Member

jaeiow

Board Regular

Xlambda

Well-known Member

jaeiow

Board Regular

Xlambda

Well-known Member

jaeiow

Board Regular

Xlambda

Well-known Member

jaeiow

Board Regular

Forum statistics

Share this page

AUNIQUE

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock