AFLATTEN

=AFLATTEN(a)
a
required.array
AFLATTEN extracts any array in a column array (vertical), does keep the blanks.
Xlambda

Xlambda

Well-known Member
Joined
Mar 8, 2021
Messages
837
Office Version
  1. 365
Platform
  1. Windows
AFLATTEN extracts any array in a column array (vertical), does keep the blanks
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,
       INDEX(IF(a="","",a),q,m)
    )
)
LAMBDA 5.0.xlsx
ABCD
1dd4
21gh
3dh
4a3
5d44
633
7c22
8
9d
10d
114
121
13g
14h
15
16d
17h
18a
193
20
21d
224
234
243
25
263
27c
282
292
30
AFLATTEN post
Cell Formulas
RangeFormula
A9:A29A9=AFLATTEN(A1:C7)
Dynamic array formulas.
 
Upvote 0
Nice formula.
Truth be told: as soon as I got the LAMBDA-function this was the very first UDF I made myself.
Conceptually it's the same as yours, but I didn't use the QUOTIENT-function.
I would post it (as a separate post) but I don't have the rights to do so. maybe one day...
 
Hi, thanks, probably you have used for q, INT((s-1)/c)+1 or ROUNDUP(s/c,0).
The reason I use QUOTIENT is because uses the same argument as MOD ( s-1,c)+1 and looks more consistent and elegant. ?
 
No, not really. My version is heavily inspired by that one video by Mike "excelisfun" Girvin (that's a hint).
As soon as I can, I'll post it.
 
Hey Cesar,
Here's my Flatten LAMBDA-UDF.
At first I thought to put it in a separate post, but after comparing it with yours and finding out that it's really very similar, I'll just put it here.
The input is a single Array, the output is a single column vector where all columns from the Array are stacked one on top of the other.
Excel Formula: 
=LAMBDA(Array,
LET(
NrRows,ROWS(Array),
NrCols,COLUMNS(Array),
NrCells,NrRows*NrCols,
Rowdex,MOD(SEQUENCE(NrCells,,0),NrRows)+1,
Coldex,SEQUENCE(NrCells,,0,1/NrRows)+1,
INDEX(Array,Rowdex,Coldex)
))
DXLR's LAMBDA.LET Library_v00.02.xlsx
NOPQRST
1Flatten
2=LAMBDA(Array, LET( NrRows,ROWS(Array), NrCols,COLUMNS(Array), NrCells,NrRows*NrCols, Rowdex,MOD(SEQUENCE(NrCells,,0),NrRows)+1, Coldex,SEQUENCE(NrCells,,0,1/NrRows)+1, INDEX(Array,Rowdex,Coldex) ))
3
4Array3:DasiaLorenzoCameron
5AlexandraRodneyVincenzo
6MadysonAlbertConner
7
8Dasia
9Alexandra
10Madyson
11Lorenzo
12Rodney
13Albert
14Cameron
15Vincenzo
16Conner
17
Sandbox
Cell Formulas
RangeFormula
O8:O16O8=Flatten(Array3)
Dynamic array formulas.
Named Ranges
NameRefers ToCells
Array3=Sandbox!$O$4:$Q$6O8
 
You must log in or register to reply here.

Forum statistics

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

Share this page

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