AFLATTEN

[Xlambda]

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

=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
	A	B	C	D
1	d	d	4
2	1	g	h
3		d	h
4	a	3
5	d	4	4
6	3		3
7	c	2	2
8
9	d
10	d
11	4
12	1
13	g
14	h
15
16	d
17	h
18	a
19	3
20
21	d
22	4
23	4
24	3
25
26	3
27	c
28	2
29	2
30
AFLATTEN post

Cell Formulas
Range	Formula
A9:A29	A9	=AFLATTEN(A1:C7)
Dynamic array formulas.

 

[GeertD]

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]

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]

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]

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.

=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
	N	O	P	Q	R	S	T
1		Flatten
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
4	Array3:	Dasia	Lorenzo	Cameron
5		Alexandra	Rodney	Vincenzo
6		Madyson	Albert	Conner
7
8		Dasia
9		Alexandra
10		Madyson
11		Lorenzo
12		Rodney
13		Albert
14		Cameron
15		Vincenzo
16		Conner
17
Sandbox

Cell Formulas
Range	Formula
O8:O16	O8	=Flatten(Array3)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Array3	=Sandbox!$O$4:$Q$6	O8

 

[Xlambda]

Great job!!