AFLAT

=AFLAT(a)

a
array

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

Xlambda

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

Xlambda

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

1,148,108
Messages
5,744,878
Members
423,908
Latest member
Getfour

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.

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

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