AFLAT

=AFLAT(a)

a
array

"flattens" any array and filters out blanks, null strings, errors

Xlambda

Well-known Member
Joined
Mar 8, 2021
Messages
837
Office Version
  1. 365
Platform
  1. Windows
AFLAT "flattens" any array and filters out blanks, null strings, errors
Other function on minisheet AFLATTEN
Excel Formula:
=LAMBDA(a,
    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,x<>"")
    )
)
LAMBDA 10.0.xlsx
ABCDEFGHIJ
1AFLAT function, same functionality as AFLATTEN, only that filters out null strings blanks and errors
2sample↓ =""=AFLATTEN(A3:C5)=AFLAT(A3:C5)
32 #DIV/0!22
4x#N/Ax
53y#DIV/0!3
6xy
7null string#N/A
8blank
93
10
11y
12
AFLAT post
Cell Formulas
RangeFormula
E2,G2E2=FORMULATEXT(E3)
C3C3=1/0
E3:E11E3=AFLATTEN(A3:C5)
G3:G6G3=AFLAT(A3:C5)
B2B2="↓ "&FORMULATEXT(B3)
B3B3=""
B4B4=NA()
Dynamic array formulas.
 
Upvote 0
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
ABCDEFGHIJK
1ib include blanks argument
2ib=0, or omitted, excludes blanks/null strings and errors
3ib=1 includes null strings/blanks, errors are replaced by null strings, same AFLATTEN functionality.
4ib<>{0,1}, returns "check arg."
5ib,1ib,omittedib,2 <>{0,1}
6=""=AFLAT(A7:C10,1)=AFLAT(A7:C10)=AFLAT(A7:C10,2)
7a baacheck arg.
8c#N/Ab
91#DIV/0!2bc
10341
11c2
12null string3
13blank14
14values
152
163
17
184
19
AFLAT 2
Cell Formulas
RangeFormula
D6D6=FORMULATEXT(E7)
G6,I6,B6G6=FORMULATEXT(G7)
B7B7=""
E7:E18E7=AFLAT(A7:C10,1)
G7:G13G7=AFLAT(A7:C10)
I7I7=AFLAT(A7:C10,2)
C8C8=NA()
B9B9=1/0
Dynamic array formulas.
 
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
ABCDEFGHIJKLMNO
1blank
2A blank cell is a cell that has nothing in it.
3Is text?is number?
4=ISTEXT(B2)=ISNUMBER(B2)
5FALSEFALSE
6
7is 0 ?is empty string? ("")a blank is a blank
8=B2=0=B2=""=ISBLANK(B2)
9TRUETRUETRUE
10=AND(B2=0,B2="")
111So, a blank is no text nor number, but, is a 0 and a "" on the same time.TRUE
122A blank can only live in a cell reference.a blank is the only cell
133Is no way a formula or a function to return a blank.that returns true for this formula
14
15=LET(a,B17:B19,a)
16a
17a0 =>By default, Excel, when array calculations are involved, or even when we call an array,
182is "collapsing" the quantum state of blank cells into 0's
192
20Since a blank is also an empty string, we can "force" a blank to "collapse" into an empty string.
21=LET(a,B17:B19,IF(a="","",a))
22a=D23=""
23 =>TRUE
242
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
28empty stringor treats empty strings as blanks.
29=COUNTBLANK(B30:B33)or Pivot TablesabRow LabelsSum of bCount of b
3012A2A21
31="" BB
32Notes:C4C41
330This is very often called Excel inconsistencyGrand Total62
34with blank cells, but I will not call it that way.
35When no array calculation is involved=M31:N31=""
36empty strings are considered somehow blanks.TRUETRUE
37Everybody can choose how to "collapse" their blank cells.
38My personal choice: empty strings (like PT behavior).
39I understand also that, even for the newest functions, Excel has to
40be consistent with all the other functions behavioral "tradition".
41And TOCOL/TOROW are no exception of that.
42
ATOCOL 1
Cell Formulas
RangeFormula
D4,F4,M35,D29,B26,F22,D21,D15,K10,F8,D8,H8D4=FORMULATEXT(D5)
D5D5=ISTEXT(B2)
F5F5=ISNUMBER(B2)
D9D9=B2=0
F9F9=B2=""
H9H9=ISBLANK(B2)
K11K11=AND(B2=0,B2="")
D16:D18D16=LET(a,B17:B19,a)
D22:D24D22=LET(a,B17:B19,IF(a="","",a))
F23F23=D23=""
B27,B31B27=""
D30D30=COUNTBLANK(B30:B33)
A31A31=FORMULATEXT(B31)
M36:N36M36=M31:N31=""
Dynamic array formulas.
 
Example:
ATOCOL ATOROW.xlsx
ABCDEFGHIJKLMNOPQRSTUV
1Extracting unique values of records that are not B
2
3step 1 , filterstep 2, TOCOL+ unique
4A223=FILTER(C4:F6,B4:B6<>"b")=UNIQUE(TOCOL(H5#))
5B11420232
6C33403340
73
8to get rid of the 0's4
9
10=FILTER(IF(C4:F6="","",C4:F6),B4:B6<>"b")=UNIQUE(TOCOL(H11#,1))
112232
12334no value of TOCOL ignore argument
133can help us to exclude the initial blanks that
144now are actually empty strings.
15
16With AFLAT, no problem
17=UNIQUE(AFLAT(H11#))
182
193
204
21
22compromise solution, without AFLAT, turn empty strings into errors
23to be able to use TOCOL's ignore argument value 2
24
25=IF(H11#="",NA(),H11#)=UNIQUE(TOCOL(H26#,2))or=FILTER(M11#,M11#<>"")
262#N/A2322
27#N/A33433
2844
29
30It's feasible, but, if we want for example to return the records extracted, we have to turn back the NA()'s to empty strings.
31We 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
32empty strings.
33
ATOCOL 2
Cell Formulas
RangeFormula
H4,M4,Q25,M25,H25,M17,M10H4=FORMULATEXT(H5)
H5:K6H5=FILTER(C4:F6,B4:B6<>"b")
M5:M8M5=UNIQUE(TOCOL(H5#))
G10G10=FORMULATEXT(H11)
H11:K12H11=FILTER(IF(C4:F6="","",C4:F6),B4:B6<>"b")
M11:M14M11=UNIQUE(TOCOL(H11#,1))
M18:M20M18=UNIQUE(AFLAT(H11#))
H26:K27H26=IF(H11#="",NA(),H11#)
M26:M28M26=UNIQUE(TOCOL(H26#,2))
Q26:Q28Q26=FILTER(M11#,M11#<>"")
Dynamic array formulas.
 
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
ABCDEFGHIJKLMNOPQRSTUVWX
1TOCOL vs ATOCOL neck to neck
2arabcTOCOL, scan by rowTOCOL, scan by clm
3012keep all valuesignore blanksignore errorsignore bothkeep all valuesignore blanksignore errorsignore both
4="" d34=TOCOL($B$2:$E$5)=TOCOL($B$2:$E$5,1)=TOCOL($B$2:$E$5,2)=TOCOL($B$2:$E$5,3)=TOCOL($B$2:$E$5,,1)=TOCOL($B$2:$E$5,1,1)=TOCOL($B$2:$E$5,2,1)=TOCOL($B$2:$E$5,3,1)
5e#DIV/0! 5aaaaaaaa
6=""bbbb0000
70c0c
8=""c0c0eeee
9 empty string0101bbbb
10blank02020d0d
11error11d#DIV/0!d1
120initial 0 value2d2d#DIV/0!103
1333031
14d4d413c
153e3e3c2
164#DIV/0!42c4
17ee5c425
18#DIV/0!5254
19545
2055
21
22TOCOL turns blanks into 0's, does not ignore empty strings for any values of ignore argument
23
24ATOCOL, scan by row c, omittedATOCOL, scan by clm, c,1
25keep all valuesignore blanksignore errorsignore bothkeep all valuesignore blanksignore errorsignore both
26i, omittedi,1i,2i,3i,omittedi,1i,2i,3
27=ATOCOL($B$2:$E$5)=ATOCOL($B$2:$E$5,1)=ATOCOL($B$2:$E$5,2)=ATOCOL($B$2:$E$5,3)=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)
28aaaaaaaa
29bbbb0000
300ccee
31c0c0ebeb
320101bdbd
330220#DIV/0!1
341d1dd1d3
352323#DIV/0!3c
36440c12
37dede1234
383#DIV/0!35345
394545c
40eec2
41#DIV/0!24
42545
4355
44blnk/empt strblnk=>""blnk/empt str,blnk/empt strblnk=>""blnk/empt str,
45same values as TOCOLignorederrors ignorederrors ignoredsame values as TOCOLignorederrors ignorederrors ignored
46
ATOCOL
Cell Formulas
RangeFormula
A4A4=FORMULATEXT(B4)
B4,A9,D5B4=""
G4,I4,K4,M4,P4,R4,T4,V4,G27,I27,K27,M27,P27,R27,T27,V27,A8G4=FORMULATEXT(G5)
C5C5=1/0
G5:G20G5=TOCOL($B$2:$E$5)
I5:I18I5=TOCOL($B$2:$E$5,1)
K5:K19K5=TOCOL($B$2:$E$5,2)
M5:M17M5=TOCOL($B$2:$E$5,3)
P5:P20P5=TOCOL($B$2:$E$5,,1)
R5:R18R5=TOCOL($B$2:$E$5,1,1)
T5:T19T5=TOCOL($B$2:$E$5,2,1)
V5:V17V5=TOCOL($B$2:$E$5,3,1)
D6D6=FORMULATEXT(D5)
G28:G43G28=ATOCOL($B$2:$E$5)
I28:I39I28=ATOCOL($B$2:$E$5,1)
K28:K42K28=ATOCOL($B$2:$E$5,2)
M28:M38M28=ATOCOL($B$2:$E$5,3)
P28:P43P28=ATOCOL($B$2:$E$5,,1)
R28:R39R28=ATOCOL($B$2:$E$5,1,1)
T28:T42T28=ATOCOL($B$2:$E$5,2,1)
V28:V38V28=ATOCOL($B$2:$E$5,3,1)
Dynamic array formulas.
 
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
ABCDEFGHIJKLMNOPQRSTUVW
1
2arabcTOROW
3012=TOROW(B2:E5)
4="" d34ab0c0012d34e#DIV/0!5
5e#DIV/0! 5
6=""=TOROW(B2:E5,1)
7abc012d34e#DIV/0!5
8=""
9 empty string=TOROW(B2:E5,2)
10blankab0c0012d34e5
11error
120initial 0 value=TOROW(B2:E5,3)
13abc012d34e5
14
15=TOROW(B2:E5,,1)
16a0eb0d#DIV/0!013c245
17
18=TOROW(B2:E5,1,1)
19a0ebd#DIV/0!13c245
20
21=TOROW(B2:E5,2,1)
22a0eb0d013c245
23
24=TOROW(B2:E5,3,1)
25a0ebd13c245
26
27ATOROW
28=ATOROW(B2:E5)
29ab0c0012d34e#DIV/0!5
30
31=ATOROW(B2:E5,1)
32abc012d34e#DIV/0!5
33
34=ATOROW(B2:E5,2)
35abc012d34e5
36
37=ATOROW(B2:E5,3)
38abc012d34e5
39
40=ATOROW(B2:E5,,1)
41a0eb0d#DIV/0!013c245
42
43=ATOROW(B2:E5,1,1)
44a0ebd#DIV/0!13c245
45
46=ATOROW(B2:E5,2,1)
47a0ebd13c245
48
49=ATOROW(B2:E5,3,1)
50a0ebd13c245
51
ATOROW
Cell Formulas
RangeFormula
G3,G49,G46,G43,G40,G37,G34,G31,G28,G24,G21,G18,G15,G12,G9,A8,G6G3=FORMULATEXT(G4)
A4A4=FORMULATEXT(B4)
B4,A9,D5B4=""
G4:V4G4=TOROW(B2:E5)
C5C5=1/0
D6D6=FORMULATEXT(D5)
G7:T7G7=TOROW(B2:E5,1)
G10:U10G10=TOROW(B2:E5,2)
G13:S13G13=TOROW(B2:E5,3)
G16:V16G16=TOROW(B2:E5,,1)
G19:T19G19=TOROW(B2:E5,1,1)
G22:U22G22=TOROW(B2:E5,2,1)
G25:S25G25=TOROW(B2:E5,3,1)
G29:V29G29=ATOROW(B2:E5)
G32:R32G32=ATOROW(B2:E5,1)
G35:U35G35=ATOROW(B2:E5,2)
G38:Q38G38=ATOROW(B2:E5,3)
G41:V41G41=ATOROW(B2:E5,,1)
G44:R44G44=ATOROW(B2:E5,1,1)
G47:U47G47=ATOROW(B2:E5,2,1)
G50:Q50G50=ATOROW(B2:E5,3,1)
Dynamic array formulas.
 
Example using ATOCOL/ATOROW
ATOCOL ATOROW.xlsx
ABCDEFGHIJKLMNOPQR
1
2A223Task: Extract records in this order
3B114D,C,E
4C334
5D5 43=LET(ar,B2:F6,r,B2:B6,e,H3,a,IF(ar="","",ar),CHOOSEROWS(a,XMATCH(TEXTSPLIT(e,","),r)))
6E#N/A2 3D543
7C334
8=""E#N/A23
9 empty string
10blankUnique values of extracted records
11
12=UNIQUE(ATOROW(I6:L8,3),1)
135432
14
15=UNIQUE(ATOCOL(I6:L8,3))
165
174
183
192
20
Sheet1
Cell Formulas
RangeFormula
D5,A9,E6D5=""
H5,H15,H12,A8H5=FORMULATEXT(H6)
C6C6=NA()
H6:L8H6=LET(ar,B2:F6,r,B2:B6,e,H3,a,IF(ar="","",ar),CHOOSEROWS(a,XMATCH(TEXTSPLIT(e,","),r)))
H13:K13H13=UNIQUE(ATOROW(I6:L8,3),1)
H16:H19H16=UNIQUE(ATOCOL(I6:L8,3))
Dynamic array formulas.
 
Using ATOCOL to solve a challenge posted by somebody at the comment section of a YT video.
repeat product names.xlsx
ABCDEFGHIJKLMNO
1from this:1.single cell formula
2prodrept time=C3:C5>=SEQUENCE(,MAX(C3:C5))=LET(p,B3:B5,n,C3:C5,ATOCOL(IF(n>=SEQUENCE(,MAX(n)),p,""),1))
3TATA4TRUETRUETRUETRUETATA
4MARUTI3TRUETRUETRUEFALSETATA
5HERO2TRUETRUEFALSEFALSETATA
62.TATA
7to this=IF(E3#,B3:B5,"")MARUTI
8TATATATATATATATATATAMARUTI
9TATAMARUTIMARUTIMARUTIMARUTI
10TATAHEROHEROHERO
11TATA3.HERO
12MARUTI=ATOCOL(E8#,1)
13MARUTITATAfair example of why ignore blanks argument in TOCOL (1)
14MARUTITATAis not helping after some array calculations (blanks live only in ranges)
15HEROTATAso we have to "force" FALSE into errors and use ignore errors (2)
16HEROTATA=LET(p,B3:B5,n,C3:C5,TOCOL(IF(n>=SEQUENCE(,MAX(n)),p,NA()),2))
17MARUTITATA
18MARUTITATA
19MARUTITATA
20HEROTATA
21HEROMARUTI
22MARUTI
23MARUTI
24HERO
25HERO
26
Sheet1
Cell Formulas
RangeFormula
E2,J16,E12,E7,J2E2=FORMULATEXT(E3)
E3:H5E3=C3:C5>=SEQUENCE(,MAX(C3:C5))
J3:J11J3=LET(p,B3:B5,n,C3:C5,ATOCOL(IF(n>=SEQUENCE(,MAX(n)),p,""),1))
E8:H10E8=IF(E3#,B3:B5,"")
E13:E21E13=ATOCOL(E8#,1)
J17:J25J17=LET(p,B3:B5,n,C3:C5,TOCOL(IF(n>=SEQUENCE(,MAX(n)),p,NA()),2))
Dynamic array formulas.
 
repeat product names.xlsx
ABCDEFGHIJKLMNO
1Alternative solution with XLOOKUP and running totals
2running totals
3prodrept time=--(SEQUENCE(3)>=SEQUENCE(,3))=MMULT(E4#,C4:C6)
4TATA41004
5MARUTI31107
6HERO21119
7
8nr items =sum(C4:C6)=9
9=SEQUENCE(9)=XLOOKUP(E10#,I4#,B4:B6,,1)
101TATA
112TATA
123TATA
134TATA
145MARUTI
156MARUTI
167MARUTI
178HERO
189HERO
19
20single cell:
21=LET(p,B4:B6,n,C4:C6,r,ROWS(n),XLOOKUP(SEQUENCE(SUM(n)),MMULT(--(SEQUENCE(r)>=SEQUENCE(,r)),n),p,,1))
22TATA
23TATA
24TATA
25TATA
26MARUTI
27MARUTI
28MARUTI
29HERO
30HERO
31
Sheet2
Cell Formulas
RangeFormula
E3,I3,I9,E9E3=FORMULATEXT(E4)
E4:G6E4=--(SEQUENCE(3)>=SEQUENCE(,3))
I4:I6I4=MMULT(E4#,C4:C6)
E10:E18E10=SEQUENCE(9)
I10:I18I10=XLOOKUP(E10#,I4#,B4:B6,,1)
E21E21=FORMULATEXT(I22)
I22:I30I22=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.
 
repeat product names.xlsx
ABCDEFGHIJKLM
1Alternative solution using REDUCE, relevant for the versatility of REDUCE function's arguments.
2
3prodrept time
4TATA4
5MARUTI3
6HERO2
7
8lambda formula
9=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)))
10TATA
11TATA
12TATA
13TATA
14MARUTI
15MARUTI
16MARUTI
17HERO
18HERO
19
Sheet3
Cell Formulas
RangeFormula
B9B9=FORMULATEXT(B10)
B10:B18B10=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)))
Dynamic array formulas.
 

Forum statistics

Threads
1,216,505
Messages
6,131,026
Members
449,616
Latest member
PsychoCube

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