AFLAT

=AFLAT(a)

a
array

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

Xlambda

Active Member
Joined
Mar 8, 2021
Messages
300
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

Xlambda

Active Member
Joined
Mar 8, 2021
Messages
300
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

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