AFLAT

4

x

#N/A

x

5

3

y

#DIV/0!

3

6

x

y

7

null string

#N/A

8

blank

9

3

10

11

y

12

AFLAT post

Cell Formulas
Range	Formula
E2,G2	E2	=FORMULATEXT(E3)
C3	C3	=1/0
E3:E11	E3	=AFLATTEN(A3:C5)
G3:G6	G3	=AFLAT(A3:C5)
B2	B2	="↓ "&FORMULATEXT(B3)
B3	B3	=""
B4	B4	=NA()
Dynamic array formulas.

Xlambda · Aug 8, 2021

Taking advantage of new optional arguments , now we can have both functionalities of AFLATTEN and previous AFLAT , in one function, the new AFLAT
Introduced optional argument "ib" (include blanks). Used an intuitive name, because if you ask the question "include blanks?" , the answer is the argument we have to use: 0,no, 1,yes
AFLAT(a,[ib])
ib=0, or omitted, excludes blanks/null strings and errors
ib=1 includes null strings/blanks, errors are replaced by null strings, same AFLATTEN functionality.
ib<>{0,1}, returns "check arg."

Excel Formula:

=LAMBDA(a,[ib],
    IF(AND(ib<>{0,1}),"check arg.",
      LET(r,ROWS(a),c,COLUMNS(a),s,SEQUENCE(r*c),q,QUOTIENT(s-1,c)+1,m,MOD(s-1,c)+1,x,INDEX(IFERROR(IF(a="","",a),""),q,m),
       FILTER(x,IF(ib,TRUE,x<>"")))
    )
)

LAMBDA 1.1.1.xlsx

A

B

C

D

E

F

G

H

I

J

K

1

ib include blanks argument

2

ib=0, or omitted, excludes blanks/null strings and errors

3

ib=1 includes null strings/blanks, errors are replaced by null strings, same AFLATTEN functionality.

4

ib<>{0,1}, returns "check arg."

5

ib,1

ib,omitted

ib,2 <>{0,1}

6

7

a

b

check arg.

8

c

#N/A

b

9

1

2

b

c

10

3

4

1

11

c

2

12

null string

3

13

blank

1

4

14

values

15

2

16

3

17

18

4

19

AFLAT 2

Cell Formulas
Range	Formula
D6	D6	=FORMULATEXT(E7)
G6,I6,B6	G6	=FORMULATEXT(G7)
B7	B7	=""
E7:E18	E7	=AFLAT(A7:C10,1)
G7:G13	G7	=AFLAT(A7:C10)
I7	I7	=AFLAT(A7:C10,2)
C8	C8	=NA()
B9	B9	=1/0
Dynamic array formulas.

Xlambda · Jun 26, 2022

AFLAT/AFLATTEN vs TOCOL
AFLAT is an updated AFLATTEN function when lambda helper functions emerged.
Preamble. Some thoughts about blanks.
The "quantum superposition" of a blank.

ATOCOL ATOROW.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

1

blank

2

A blank cell is a cell that has nothing in it.

3

Is text?

is number?

4

=ISTEXT(B2)

=ISNUMBER(B2)

5

FALSE

6

7

is 0 ?

is empty string? ("")

a blank is a blank

8

9

10

11

1

So, a blank is no text nor number, but, is a 0 and a "" on the same time.

12

2

A blank can only live in a cell reference.

a blank is the only cell

13

3

Is no way a formula or a function to return a blank.

that returns true for this formula

14

15

=LET(a,B17:B19,a)

16

=LET(a,B17:B19,IF(a="","",a))

17

a

0

=>

By default, Excel, when array calculations are involved, or even when we call an array,

18

2

is "collapsing" the quantum state of blank cells into 0's

19

2

20

Since a blank is also an empty string, we can "force" a blank to "collapse" into an empty string.

21

22

=D23=""

23

=>

24

2

25

26

We, as lambda "programmers" we have to choose one or the other. I think the second one is more accurate.

27

Believe it or not, Excel, on certain circumstances, turns blanks into empty strings

28

empty string

or treats empty strings as blanks.

29

=COUNTBLANK(B30:B33)

or Pivot Tables

a

b

Row Labels

Sum of b

Count of b

30

1

A

2

A

2

1

31

B

32

Notes:

C

4

C

4

1

33

0

This is very often called Excel inconsistency

Grand Total

6

2

34

with blank cells, but I will not call it that way.

35

When no array calculation is involved

=M31:N31=""

36

empty strings are considered somehow blanks.

=FILTER(C4:F6,B4:B6<>"b")

37

Everybody can choose how to "collapse" their blank cells.

38

My personal choice: empty strings (like PT behavior).

39

I understand also that, even for the newest functions, Excel has to

40

be consistent with all the other functions behavioral "tradition".

41

And TOCOL/TOROW are no exception of that.

42

ATOCOL 1

Cell Formulas
Range	Formula
D4,F4,M35,D29,B26,F22,D21,D15,K10,F8,D8,H8	D4	=FORMULATEXT(D5)
D5	D5	=ISTEXT(B2)
F5	F5	=ISNUMBER(B2)
D9	D9	=B2=0
F9	F9	=B2=""
H9	H9	=ISBLANK(B2)
K11	K11	=AND(B2=0,B2="")
D16:D18	D16	=LET(a,B17:B19,a)
D22:D24	D22	=LET(a,B17:B19,IF(a="","",a))
F23	F23	=D23=""
B27,B31	B27	=""
D30	D30	=COUNTBLANK(B30:B33)
A31	A31	=FORMULATEXT(B31)
M36:N36	M36	=M31:N31=""
Dynamic array formulas.

Xlambda · Jun 26, 2022

Example:

ATOCOL ATOROW.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

1

Extracting unique values of records that are not B

2

3

step 1 , filter

step 2, TOCOL+ unique

4

A

2

3

=UNIQUE(TOCOL(H5#))

5

B

1

4

0

2

3

=FILTER(IF(C4:F6="","",C4:F6),B4:B6<>"b")

6

C

3

4

0

3

4

0

7

3

8

to get rid of the 0's

4

9

10

=UNIQUE(TOCOL(H11#,1))

11

3

12

3

4

no value of TOCOL ignore argument

13

3

can help us to exclude the initial blanks that

14

4

now are actually empty strings.

15

16

With AFLAT, no problem

17

=UNIQUE(AFLAT(H11#))

18

19

3

20

4

21

22

compromise solution, without AFLAT, turn empty strings into errors

23

to be able to use TOCOL's ignore argument value 2

24

25

=IF(H11#="",NA(),H11#)

=UNIQUE(TOCOL(H26#,2))

or

=FILTER(M11#,M11#<>"")

26

#N/A

2

3

27

#N/A

3

4

3

28

4

29

30

It's feasible, but, if we want for example to return the records extracted, we have to turn back the NA()'s to empty strings.

31

We can do this every time or we can rewrite AFLAT to include the versatility of TOCOL (scan by col) and adapt ignore argument to deal also with

32

empty strings.

33

ATOCOL 2

Cell Formulas
Range	Formula
H4,M4,Q25,M25,H25,M17,M10	H4	=FORMULATEXT(H5)
H5:K6	H5	=FILTER(C4:F6,B4:B6<>"b")
M5:M8	M5	=UNIQUE(TOCOL(H5#))
G10	G10	=FORMULATEXT(H11)
H11:K12	H11	=FILTER(IF(C4:F6="","",C4:F6),B4:B6<>"b")
M11:M14	M11	=UNIQUE(TOCOL(H11#,1))
M18:M20	M18	=UNIQUE(AFLAT(H11#))
H26:K27	H26	=IF(H11#="",NA(),H11#)
M26:M28	M26	=UNIQUE(TOCOL(H26#,2))
Q26:Q28	Q26	=FILTER(M11#,M11#<>"")
Dynamic array formulas.

Xlambda · Jun 26, 2022

ATOCOL, Array TO COLumn, same arguments, versatility, as TOCOL, (scan by column), can deal with empty strings.
ATOCOL(ar,[i ],[c])
ar: array or range to return as a column
[i ]: ignore argument
- if 0 or omitted, keeps all values, identical to TOCOL
- if 1 ignores blanks/empty strings
- if 2 ignores errors
- if 3 ignores blanks/empty strings and errors
[c]: scan_by_column argument
- if 0 or omitted, scanned by row
- if 1or <>0 , scanned by column

Excel Formula:

=LAMBDA(ar,[i],[c],
    LET(
        a, IF(ar = "", "", ar),
        t, TOCOL(a, 0, c),
        e, NOT(ISERROR(t)),
        n, IFERROR(t <> "", 1),
        SWITCH(
            i,
            0,
            TOCOL(ar, i, c),
            1,
            FILTER(t, n),
            2,
            FILTER(t, e),
            3,
            FILTER(t, e * n)
        )
    )
)

TOCOL vs ATOCOL

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

1

TOCOL vs ATOCOL neck to neck

2

ar

a

b

c

TOCOL, scan by row

TOCOL, scan by clm

3

0

1

2

keep all values

ignore blanks

ignore errors

ignore both

keep all values

ignore blanks

ignore errors

ignore both

4

d

3

4

=TOCOL($B$2:$E$5,1,1)

=TOCOL($B$2:$E$5,2,1)

=TOCOL($B$2:$E$5,3,1)

5

e

5

6

b

0

7

0

c

0

c

8

c

0

c

0

e

9

empty string

0

1

0

1

b

10

blank

0

2

0

2

0

d

0

d

11

error

1

d

#DIV/0!

d

1

12

0

initial 0 value

2

d

2

d

#DIV/0!

1

0

3

13

3

0

3

1

14

d

4

d

4

1

3

c

15

3

e

3

e

3

c

2

16

4

#DIV/0!

4

2

c

4

17

e

5

c

4

2

5

18

#DIV/0!

5

2

5

4

19

5

4

5

20

5

21

22

TOCOL turns blanks into 0's, does not ignore empty strings for any values of ignore argument

23

24

ATOCOL, scan by row c, omitted

ATOCOL, scan by clm, c,1

25

keep all values

ignore blanks

ignore errors

ignore both

keep all values

ignore blanks

ignore errors

ignore both

26

i, omitted

i,1

i,2

i,3

i,omitted

i,1

i,2

i,3

27

=ATOCOL($B$2:$E$5,,1)

=ATOCOL($B$2:$E$5,1,1)

=ATOCOL($B$2:$E$5,2,1)

=ATOCOL($B$2:$E$5,3,1)

28

29

b

0

30

0

c

e

31

c

0

c

0

e

b

e

b

32

0

1

0

1

b

d

b

d

33

0

2

0

#DIV/0!

1

34

1

d

1

d

1

d

3

35

2

3

2

3

#DIV/0!

3

c

36

4

0

c

1

2

37

d

e

d

e

1

2

3

4

38

3

#DIV/0!

3

5

3

4

5

39

4

5

4

5

c

40

e

c

2

41

#DIV/0!

2

4

42

5

4

5

43

5

44

blnk/empt str

blnk=>""

blnk/empt str,

blnk/empt str

blnk=>""

blnk/empt str,

45

same values as TOCOL

ignored

errors ignored

same values as TOCOL

ignored

errors ignored

46

ATOCOL

Cell Formulas
Range	Formula
A4	A4	=FORMULATEXT(B4)
B4,A9,D5	B4	=""
G4,I4,K4,M4,P4,R4,T4,V4,G27,I27,K27,M27,P27,R27,T27,V27,A8	G4	=FORMULATEXT(G5)
C5	C5	=1/0
G5:G20	G5	=TOCOL($B$2:$E$5)
I5:I18	I5	=TOCOL($B$2:$E$5,1)
K5:K19	K5	=TOCOL($B$2:$E$5,2)
M5:M17	M5	=TOCOL($B$2:$E$5,3)
P5:P20	P5	=TOCOL($B$2:$E$5,,1)
R5:R18	R5	=TOCOL($B$2:$E$5,1,1)
T5:T19	T5	=TOCOL($B$2:$E$5,2,1)
V5:V17	V5	=TOCOL($B$2:$E$5,3,1)
D6	D6	=FORMULATEXT(D5)
G28:G43	G28	=ATOCOL($B$2:$E$5)
I28:I39	I28	=ATOCOL($B$2:$E$5,1)
K28:K42	K28	=ATOCOL($B$2:$E$5,2)
M28:M38	M28	=ATOCOL($B$2:$E$5,3)
P28:P43	P28	=ATOCOL($B$2:$E$5,,1)
R28:R39	R28	=ATOCOL($B$2:$E$5,1,1)
T28:T42	T28	=ATOCOL($B$2:$E$5,2,1)
V28:V38	V28	=ATOCOL($B$2:$E$5,3,1)
Dynamic array formulas.

Xlambda · Jun 26, 2022

ATOROW, Array TO ROW, same arguments, versatility, as TOROW, (scan by column), can deal with empty strings.
ATOROW(ar,[i ],[c])
ar: array or range to return as a row
[i ]: ignore argument
- if 0 or omitted, keeps all values, identical to TOROW
- if 1 ignores blanks/empty strings
- if 2 ignores errors
- if 3 ignores blanks/empty strings and errors
[c]: scan_by_column argument
- if 0 or omitted, scanned by row
- if 1or <>0 , scanned by column

Excel Formula:

=LAMBDA(ar, [i], [c],
    LET(
        a, IF(ar = "", "", ar),
        t, TOROW(a, 0, c),
        e, NOT(ISERROR(t)),
        n, IFERROR(t <> "", 1),
        SWITCH(
            i,
            0,
            TOROW(ar, i, c),
            1,
            FILTER(t, n),
            2,
            FILTER(t, e),
            3,
            FILTER(t, e * n)
        )
    )
)

TOROW vs ATOROW

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

ar

a

b

c

TOROW

3

0

1

2

=TOROW(B2:E5)

4

d

3

4

b

0

c

0

1

2

d

3

4

e

#DIV/0!

5

e

5

6

=TOROW(B2:E5,1)

7

b

c

0

1

2

d

3

4

e

#DIV/0!

5

8

9

empty string

=TOROW(B2:E5,2)

10

blank

b

0

c

0

1

2

d

3

4

e

5

11

error

12

0

initial 0 value

=TOROW(B2:E5,3)

13

b

c

0

1

2

d

3

4

e

5

14

15

=TOROW(B2:E5,,1)

16

0

e

b

0

d

#DIV/0!

0

1

3

c

2

4

5

17

18

=TOROW(B2:E5,1,1)

19

0

e

b

d

#DIV/0!

1

3

c

2

4

5

20

21

=TOROW(B2:E5,2,1)

22

0

e

b

0

d

0

1

3

c

2

4

5

23

24

=TOROW(B2:E5,3,1)

25

0

e

b

d

1

3

c

2

4

5

26

27

ATOROW

28

=ATOROW(B2:E5)

29

b

0

c

0

1

2

d

3

4

e

#DIV/0!

5

30

31

=ATOROW(B2:E5,1)

32

b

c

0

1

2

d

3

4

e

#DIV/0!

5

33

34

=ATOROW(B2:E5,2)

35

b

c

0

1

2

d

3

4

e

5

36

37

=ATOROW(B2:E5,3)

38

b

c

0

1

2

d

3

4

e

5

39

40

=ATOROW(B2:E5,,1)

41

0

e

b

0

d

#DIV/0!

0

1

3

c

2

4

5

42

43

=ATOROW(B2:E5,1,1)

44

0

e

b

d

#DIV/0!

1

3

c

2

4

5

45

46

=ATOROW(B2:E5,2,1)

47

0

e

b

d

1

3

c

2

4

5

48

49

=ATOROW(B2:E5,3,1)

50

=LET(ar,B2:F6,r,B2:B6,e,H3,a,IF(ar="","",ar),CHOOSEROWS(a,XMATCH(TEXTSPLIT(e,","),r)))

0

e

b

d

1

3

c

2

4

5

51

ATOROW

Cell Formulas
Range	Formula
G3,G49,G46,G43,G40,G37,G34,G31,G28,G24,G21,G18,G15,G12,G9,A8,G6	G3	=FORMULATEXT(G4)
A4	A4	=FORMULATEXT(B4)
B4,A9,D5	B4	=""
G4:V4	G4	=TOROW(B2:E5)
C5	C5	=1/0
D6	D6	=FORMULATEXT(D5)
G7:T7	G7	=TOROW(B2:E5,1)
G10:U10	G10	=TOROW(B2:E5,2)
G13:S13	G13	=TOROW(B2:E5,3)
G16:V16	G16	=TOROW(B2:E5,,1)
G19:T19	G19	=TOROW(B2:E5,1,1)
G22:U22	G22	=TOROW(B2:E5,2,1)
G25:S25	G25	=TOROW(B2:E5,3,1)
G29:V29	G29	=ATOROW(B2:E5)
G32:R32	G32	=ATOROW(B2:E5,1)
G35:U35	G35	=ATOROW(B2:E5,2)
G38:Q38	G38	=ATOROW(B2:E5,3)
G41:V41	G41	=ATOROW(B2:E5,,1)
G44:R44	G44	=ATOROW(B2:E5,1,1)
G47:U47	G47	=ATOROW(B2:E5,2,1)
G50:Q50	G50	=ATOROW(B2:E5,3,1)
Dynamic array formulas.

Xlambda · Jun 26, 2022

Example using ATOCOL/ATOROW

ATOCOL ATOROW.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

1

2

A

2

3

Task: Extract records in this order

3

B

1

4

D,C,E

4

C

3

4

5

D

5

4

3

6

E

#N/A

2

3

D

5

4

3

7

C

3

4

8

=UNIQUE(ATOROW(I6:L8,3),1)

E

#N/A

2

3

9

empty string

10

blank

Unique values of extracted records

11

12

13

5

4

3

2

14

15

=UNIQUE(ATOCOL(I6:L8,3))

16

5

17

4

18

3

19

2

20

Sheet1

Cell Formulas
Range	Formula
D5,A9,E6	D5	=""
H5,H15,H12,A8	H5	=FORMULATEXT(H6)
C6	C6	=NA()
H6:L8	H6	=LET(ar,B2:F6,r,B2:B6,e,H3,a,IF(ar="","",ar),CHOOSEROWS(a,XMATCH(TEXTSPLIT(e,","),r)))
H13:K13	H13	=UNIQUE(ATOROW(I6:L8,3),1)
H16:H19	H16	=UNIQUE(ATOCOL(I6:L8,3))
Dynamic array formulas.

Xlambda · Sep 16, 2022

Using ATOCOL to solve a challenge posted by somebody at the comment section of a YT video.

repeat product names.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

1

from this:

1.

single cell formula

2

prod

rept time

=C3:C5>=SEQUENCE(,MAX(C3:C5))

=LET(p,B3:B5,n,C3:C5,ATOCOL(IF(n>=SEQUENCE(,MAX(n)),p,""),1))

3

TATA

4

4

MARUTI

3

TRUE

FALSE

TATA

5

HERO

2

TRUE

FALSE

TATA

6

2.

TATA

7

to this

=IF(E3#,B3:B5,"")

MARUTI

8

TATA

MARUTI

9

TATA

MARUTI

10

TATA

HERO

11

TATA

3.

HERO

12

MARUTI

=ATOCOL(E8#,1)

13

MARUTI

=LET(p,B3:B5,n,C3:C5,TOCOL(IF(n>=SEQUENCE(,MAX(n)),p,NA()),2))

fair example of why ignore blanks argument in TOCOL (1)

14

MARUTI

TATA

is not helping after some array calculations (blanks live only in ranges)

15

HERO

TATA

so we have to "force" FALSE into errors and use ignore errors (2)

16

HERO

TATA

17

MARUTI

=--(SEQUENCE(3)>=SEQUENCE(,3))

18

MARUTI

TATA

19

MARUTI

TATA

20

HERO

TATA

21

HERO

MARUTI

22

MARUTI

23

MARUTI

24

HERO

25

HERO

26

Sheet1

Cell Formulas
Range	Formula
E2,J16,E12,E7,J2	E2	=FORMULATEXT(E3)
E3:H5	E3	=C3:C5>=SEQUENCE(,MAX(C3:C5))
J3:J11	J3	=LET(p,B3:B5,n,C3:C5,ATOCOL(IF(n>=SEQUENCE(,MAX(n)),p,""),1))
E8:H10	E8	=IF(E3#,B3:B5,"")
E13:E21	E13	=ATOCOL(E8#,1)
J17:J25	J17	=LET(p,B3:B5,n,C3:C5,TOCOL(IF(n>=SEQUENCE(,MAX(n)),p,NA()),2))
Dynamic array formulas.

Xlambda · Sep 16, 2022

repeat product names.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

1

Alternative solution with XLOOKUP and running totals

2

running totals

3

prod

rept time

=MMULT(E4#,C4:C6)

4

TATA

4

1

0

4

5

MARUTI

3

1

0

7

6

HERO

2

1

9

7

8

nr items =sum(C4:C6)=9

9

=SEQUENCE(9)

=XLOOKUP(E10#,I4#,B4:B6,,1)

10

1

=LET(p,B4:B6,n,C4:C6,r,ROWS(n),XLOOKUP(SEQUENCE(SUM(n)),MMULT(--(SEQUENCE(r)>=SEQUENCE(,r)),n),p,,1))

11

2

TATA

12

3

TATA

13

4

TATA

14

5

MARUTI

15

6

MARUTI

16

7

MARUTI

17

8

HERO

18

9

HERO

19

20

single cell:

21

22

=REDUCE(SEQUENCE(SUM(C4:C6)),SEQUENCE(ROWS(C4:C6)),LAMBDA(v,i,IF(v<=SUM(INDEX(C4:C6,SEQUENCE(i))),INDEX(B4:B6,i),v)))

23

TATA

24

TATA

25

TATA

26

MARUTI

27

MARUTI

28

MARUTI

29

HERO

30

HERO

31

Sheet2

Cell Formulas
Range	Formula
E3,I3,I9,E9	E3	=FORMULATEXT(E4)
E4:G6	E4	=--(SEQUENCE(3)>=SEQUENCE(,3))
I4:I6	I4	=MMULT(E4#,C4:C6)
E10:E18	E10	=SEQUENCE(9)
I10:I18	I10	=XLOOKUP(E10#,I4#,B4:B6,,1)
E21	E21	=FORMULATEXT(I22)
I22:I30	I22	=LET(p,B4:B6,n,C4:C6,r,ROWS(n),XLOOKUP(SEQUENCE(SUM(n)),MMULT(--(SEQUENCE(r)>=SEQUENCE(,r)),n),p,,1))
Dynamic array formulas.

Xlambda · Sep 17, 2022

repeat product names.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

1

Alternative solution using REDUCE, relevant for the versatility of REDUCE function's arguments.

2

3

prod

rept time

4

TATA

4

5

MARUTI

3

6

HERO

2

7

8

lambda formula

9

10