AFLATTEN

=AFLATTEN(a)

a
required.array

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

Xlambda

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

GeertD

New Member
Joined
Dec 22, 2020
Messages
45
Office Version
  1. 365
Platform
  1. Windows
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...
 

Xlambda

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

GeertD

New Member
Joined
Dec 22, 2020
Messages
45
Office Version
  1. 365
Platform
  1. Windows
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.
 

GeertD

New Member
Joined
Dec 22, 2020
Messages
45
Office Version
  1. 365
Platform
  1. Windows
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,853
Messages
5,766,776
Members
425,378
Latest member
kapoor2892

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