AUNSTACK

[Xlambda]

AUNSTACK new!! Array Unstack, unstacks an array for a certain height. !!NEW!! MAKEARRAY

=LAMBDA(a,[h],
    LET(x,ROWS(a),y,COLUMNS(a),k,MEDIAN(1,ABS(h),x),n,ROUNDUP(x/k,0),
       IFERROR(MAKEARRAY(k,n*y,LAMBDA(r,c,INDEX(IF(a="","",a),k*QUOTIENT(c-1,y)+r,MOD(c-1,y)+1))),"")
    )
)

LAMBDA 1.1.2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK
1	sample 1				h,3													sample 2				h,5
2	1	2	3		=AUNSTACK(A2:C13,3)													1	2	3		=AUNSTACK(R2:T13,5)
3	4	5	6		1	2	3	10	11	12	19	20	21	28		30		4	5	6		1	2	3	16		18	31	32	33
4	7	8	9		4	5	6	13	14	15	22	23	24	31	32	33		7	8	9		4	5	6	19	20	21	34	35	36
5	10	11	12		7	8	9	16		18	25	26	27	34	35	36		10	11	12		7	8	9	22	23	24
6	13	14	15															13	14	15		10	11	12	25	26	27
7	16		18		if h<0, h=abs(h)													16		18		13	14	15	28		30
8	19	20	21		=AUNSTACK(A2:C13,-3)													19	20	21
9	22	23	24		1	2	3	10	11	12	19	20	21	28		30		22	23	24
10	25	26	27		4	5	6	13	14	15	22	23	24	31	32	33		25	26	27
11	28	#N/A	30		7	8	9	16		18	25	26	27	34	35	36		28	#N/A	30
12	31	32	33															31	32	33
13	34	35	36															34	35	36
14
15	if h,0 or omitted h=1
16	or equivalent with AUNSTACK(a)=TRANSPOSE(AFLAT(a,1))
17	=AUNSTACK(A2:C13)
18	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16		18	19	20	21	22	23	24	25	26	27	28		30	31	32	33	34	35	36
19
20	if abs(h)>=rws(a), h=rows(a), array will remain intact,(only errors will be replaced with null strings)
21	=AUNSTACK(A2:C13,-20)
22	1	2	3
23	4	5	6
24	7	8	9
25	10	11	12
26	13	14	15
27	16		18
28	19	20	21
29	22	23	24
30	25	26	27
31	28		30
32	31	32	33
33	34	35	36
34
new AUNSTACK post 1

Cell Formulas
Range	Formula
E2,A21,A17,E8,V2	E2	=FORMULATEXT(E3)
E3:P5	E3	=AUNSTACK(A2:C13,3)
V3:AD7	V3	=AUNSTACK(R2:T13,5)
E9:P11	E9	=AUNSTACK(A2:C13,-3)
B11,S11	B11	=NA()
A18:AJ18	A18	=AUNSTACK(A2:C13)
A22:C33	A22	=AUNSTACK(A2:C13,-20)
Dynamic array formulas.

 

[Xlambda]

LAMBDA 1.1.2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	sample 1		Task 1: transform sample 1 in a proper data set							or
2	08-08-21						=APP2V({"Date","Doc","Sales"},C4#)				=AOVERLAP(C4#,{"Date","Doc","Sales"},-1)
3	Invoice 1		=ASTACK(AUNSTACK(A2:A19),3)				Date	Doc	Sales		Date	Doc	Sales
4	30		08-08-21	Invoice 1	30		08-08-21	Invoice 1	30		08-08-21	Invoice 1	30
5	10-08-21		10-08-21	Invoice 2	15		10-08-21	Invoice 2	15		10-08-21	Invoice 2	15
6	Invoice 2		15-08-21	Invoice 3	23		15-08-21	Invoice 3	23		15-08-21	Invoice 3	23
7	15		18-08-21	Invoice 4	16		18-08-21	Invoice 4	16		18-08-21	Invoice 4	16
8	15-08-21		21-08-21	Invoice 5	18		21-08-21	Invoice 5	18		21-08-21	Invoice 5	18
9	Invoice 3		24-08-21	Invoice 6	28		24-08-21	Invoice 6	28		24-08-21	Invoice 6	28
10	23
11	18-08-21		sample 2
12	Invoice 4		08-08-21	10-08-21	15-08-21	18-08-21	21-08-21	24-08-21
13	16		Invoice 1	Invoice 2	Invoice 3	Invoice 4	Invoice 5	Invoice 6
14	21-08-21		30	15	23	16	18	28
15	Invoice 5
16	18		Task 2: Transform sample 2 in a proper data set
17	24-08-21		=TRANSPOSE(C12:H14)
18	Invoice 6		08-08-21	Invoice 1	30		functions minisheet
19	28		10-08-21	Invoice 2	15		ASTACK
20			15-08-21	Invoice 3	23		AUNSTACK
21			18-08-21	Invoice 4	16		APP2V
22			21-08-21	Invoice 5	18		AOVERLAP
23			24-08-21	Invoice 6	28
24
stk-ustk 1

Cell Formulas
Range	Formula
G2,K2,C17,C3	G2	=FORMULATEXT(G3)
G3:I9	G3	=APP2V({"Date","Doc","Sales"},C4#)
K3:M9	K3	=AOVERLAP(C4#,{"Date","Doc","Sales"},-1)
C4:E9	C4	=ASTACK(AUNSTACK(A2:A19),3)
C18:E23	C18	=TRANSPOSE(C12:H14)
Dynamic array formulas.

 

[Xlambda]

LAMBDA 1.1.2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	sample 1			Task 1: Transform sample 1 in a proper data set for invoices that already have data
2	08-08-21	19-08-21		step 1: proper data set					step 2: refining
3	Invoice 1	Invoice 7		=ASTACK(AUNSTACK(ASTACK(A2:B22)),3)					=SORT(AHCLEAN(D4#))
4	30	21		08-08-21	Invoice 1	30			08-08-21	Invoice 1	30
5	10-08-21	20-08-21		10-08-21	Invoice 2	15			10-08-21	Invoice 2	15
6	Invoice 2	Invoice 8		12-08-21	Invoice 3	23			12-08-21	Invoice 3	23
7	15	12		13-08-21	Invoice 4	16			13-08-21	Invoice 4	16
8	12-08-21	21-08-21		15-08-21	Invoice 5	18			15-08-21	Invoice 5	18
9	Invoice 3	Invoice 9		18-08-21	Invoice 6				16-08-21	Invoice 13	29
10	23								19-08-21	Invoice 7	21
11	13-08-21	21-08-21		19-08-21	Invoice 7	21			20-08-21	Invoice 8	12
12	Invoice 4	Invoice 10		20-08-21	Invoice 8	12			21-08-21	Invoice 10	14
13	16	14		21-08-21	Invoice 9				23-08-21	Invoice 11	19
14	15-08-21	23-08-21		21-08-21	Invoice 10	14			24-08-21	Invoice 12	20
15	Invoice 5	Invoice 11		23-08-21	Invoice 11	19
16	18	19		24-08-21	Invoice 12	20
17	18-08-21	24-08-21		16-08-21	Invoice 13	29
18	Invoice 6	Invoice 12
19		20
20		16-08-21
21		Invoice 13
22		29
23				Task 2: Transform sample 2 in a proper data set, other method, single cell
24	sample 2			=TRANSPOSE(AUNSTACK(ASTACK(A25:B51),3))
25	08-08-21	19-08-21		08-08-21	Invoice 1	30
26	Invoice 1	Invoice 7		10-08-21	Invoice 2	15
27	30	21		12-08-21	Invoice 3	23
28	10-08-21	20-08-21		13-08-21	Invoice 4	16
29	Invoice 2	Invoice 8		15-08-21	Invoice 5	18			Date	Doc	Sales
30	15	12		18-08-21	Invoice 6	33			08-08-21	Invoice 1	30
31	12-08-21	21-08-21							10-08-21	Invoice 2	15
32	Invoice 3	Invoice 9		14-08-21	Invoice 14	30			12-08-21	Invoice 3	23
33	23	32							13-08-21	Invoice 4	16
34	13-08-21	21-08-21		19-08-21	Invoice 7	21			14-08-21	Invoice 14	30
35	Invoice 4	Invoice 10		20-08-21	Invoice 8	12			15-08-21	Invoice 5	18
36	16	14		21-08-21	Invoice 9	32			16-08-21	Invoice 13	29
37	15-08-21	23-08-21		21-08-21	Invoice 10	14			18-08-21	Invoice 6	33
38	Invoice 5	Invoice 11		23-08-21	Invoice 11	19			19-08-21	Invoice 7	21
39	18	19		24-08-21	Invoice 12	20			20-08-21	Invoice 8	12
40	18-08-21	24-08-21		16-08-21	Invoice 13	29			21-08-21	Invoice 9	32
41	Invoice 6	Invoice 12							21-08-21	Invoice 10	14
42	33	20		21-08-21	Invoice 15	29			21-08-21	Invoice 15	29
43		16-08-21							23-08-21	Invoice 11	19
44		Invoice 13							24-08-21	Invoice 12	20
45		29	I29:	=AVINSERT(SORT(AHCLEAN(TRANSPOSE(AUNSTACK(ASTACK(A25:B51),3)))),{"Date","Doc","Sales"})
46	14-08-21
47	Invoice 14			functions minisheet
48	30			ASTACK
49		21-08-21		AUNSTACK
50		Invoice 15		AHCLEAN
51		29		AVINSERT
52
stk-ustk 2

Cell Formulas
Range	Formula
D3,I3,D24	D3	=FORMULATEXT(D4)
D4:F17	D4	=ASTACK(AUNSTACK(ASTACK(A2:B22)),3)
I4:K14	I4	=SORT(AHCLEAN(D4#))
D25:F42	D25	=TRANSPOSE(AUNSTACK(ASTACK(A25:B51),3))
I29:K44	I29	=AVINSERT(SORT(AHCLEAN(TRANSPOSE(AUNSTACK(ASTACK(A25:B51),3)))),{"Date","Doc","Sales"})
D45	D45	=FORMULATEXT(I29)
Dynamic array formulas.

 

[Xlambda]

LAMBDA 1.1.2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	sample
2	08-08-21	10-08-21	12-08-21	13-08-21	15-08-21	18-08-21		14-08-21
3	Invoice 1	Invoice 2	Invoice 3	Invoice 4	Invoice 5	Invoice 6		Invoice 14
4	30	15	23	16	18	33		30
5	19-08-21	20-08-21	21-08-21	21-08-21	23-08-21	24-08-21	16-08-21		21-08-21
6	Invoice 7	Invoice 8	Invoice 9	Invoice 10	Invoice 11	Invoice 12	Invoice 13		Invoice 15
7	21	12	32	14	19	20	29		29
8
9	Task 1: Transform sample data in a proper data set, single cell									Task 2: Update report with new data
10										=SORT(AFILL(TRANSPOSE(AUNSTACK(A2:I7,3)),ASTACK(F13:H15)))
11	=AOVERLAP(SORT(AHCLEAN(TRANSPOSE(AUNSTACK(A2:I7,3)))),{"Date","Doc","Sales"},-1)									08-08-21	Invoice 1	30
12	Date	Doc	Sales							09-08-21	Invoice 16	22
13	08-08-21	Invoice 1	30		New data:	09-08-21	16-08-21	25-08-21		10-08-21	Invoice 2	15
14	10-08-21	Invoice 2	15			Invoice 16	Invoice 17	Invoice 18		12-08-21	Invoice 3	23
15	12-08-21	Invoice 3	23			22	27	33		13-08-21	Invoice 4	16
16	13-08-21	Invoice 4	16							14-08-21	Invoice 14	30
17	14-08-21	Invoice 14	30							15-08-21	Invoice 5	18
18	15-08-21	Invoice 5	18		functions minisheet					16-08-21	Invoice 17	27
19	16-08-21	Invoice 13	29		ASTACK					16-08-21	Invoice 13	29
20	18-08-21	Invoice 6	33		AUNSTACK					18-08-21	Invoice 6	33
21	19-08-21	Invoice 7	21		AHCLEAN					19-08-21	Invoice 7	21
22	20-08-21	Invoice 8	12		AOVERLAP					20-08-21	Invoice 8	12
23	21-08-21	Invoice 9	32		AFILL					21-08-21	Invoice 9	32
24	21-08-21	Invoice 10	14							21-08-21	Invoice 10	14
25	21-08-21	Invoice 15	29							21-08-21	Invoice 15	29
26	23-08-21	Invoice 11	19							23-08-21	Invoice 11	19
27	24-08-21	Invoice 12	20							24-08-21	Invoice 12	20
28										25-08-21	Invoice 18	33
29
stk-ustk 3

Cell Formulas
Range	Formula
J10,A11	J10	=FORMULATEXT(J11)
J11:L28	J11	=SORT(AFILL(TRANSPOSE(AUNSTACK(A2:I7,3)),ASTACK(F13:H15)))
A12:C27	A12	=AOVERLAP(SORT(AHCLEAN(TRANSPOSE(AUNSTACK(A2:I7,3)))),{"Date","Doc","Sales"},-1)
Dynamic array formulas.

 

[Xlambda]

The combo ASTACK/ANSTACK is created to solve any stacking/unstacking problem with ease, as long there is a structure pattern 1D or 2D.
Note: Posted same example at ASTACK, but here is solved in a different way, with same outcome.
Formula used in ASTACK post: =SORT(TRANSPOSE(AUNSTACK(AFLAT(AHCLEAN(ASTACK(B10:G30,2))),6)),SEQUENCE(,6)) calls AFLAT and AHCLEAN
Formula used here : =SORT(TRANSPOSE(AUNSTACK(ASTACK(AVCLEAN(AUNSTACK(B10:G30,3))),6)),SEQUENCE(,6)) calls AVCLEAN

LAMBDA 1.1.2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	I will try to complicate the example as much as possible with a 2D data structure.
2	Imagine that we recive data in this form factor, like a 2D card with this fields:
3
4		Date	Company		Company names: from A to D
5		Invoice nr.	Sales pers.		A1 or B3 represent sales persons of company A , respectivly B
6		Product	Amount		a1 or d4 represent products of respective companies
7
8	And we receive data in a crazy way like an unfinished puzzle:								Task: Transform in a proper sorted data set
9									Note: What is important, card is 3 by 2 and has 3*2=6 elements
10		18-08-21	A
11		Inv.01	A3						Single cell formula:
12		a6	23						=SORT(TRANSPOSE(AUNSTACK(ASTACK(AVCLEAN(AUNSTACK(B10:G30,3))),6)),SEQUENCE(,6))
13				25-08-21	C	03-08-21	D		03-08-21	Inv.46	d1	D	D3	12
14				Inv.23	C2	Inv.46	D3		06-08-21	Inv.58	b9	B	B4	29
15				c4	32	d1	12		06-08-21	Inv.72	a8	A	A3	29
16				15-08-21	B				15-08-21	Inv.18	b5	B	B2	19
17				Inv.18	B2				15-08-21	Inv.33	d8	D	D7	28
18				b5	19				17-08-21	Inv.09	c3	C	C5	47
19		17-08-21	A			17-08-21	C		17-08-21	Inv.12	a1	A	A7	15
20		Inv.12	A7			Inv.09	C5		18-08-21	Inv.01	a6	A	A3	23
21		a1	15			c3	47		25-08-21	Inv.23	c4	C	C2	32
22						15-08-21	D
23						Inv.33	D7		As we see, is so easy to check that data is consistent.
24						d8	28
25				06-08-21	B				To add the headers
26				Inv.58	B4				=AFILL(APP2V("",F46#),B4:C6)
27				b9	29				Date	Company	Invoice nr.	Sales pers.	Product	Amount
28						06-08-21	A		03-08-21	D	Inv.46	D3	d1	12
29						Inv.72	A3		06-08-21	A	Inv.72	A3	a8	29
30						a8	29		06-08-21	B	Inv.58	B4	b9	29
31									15-08-21	B	Inv.18	B2	b5	19
32		functions							15-08-21	D	Inv.33	D7	d8	28
33		ASTACK							17-08-21	A	Inv.12	A7	a1	15
34		AUNSTACK							17-08-21	C	Inv.09	C5	c3	47
35		AVCLEAN							18-08-21	A	Inv.01	A3	a6	23
36		APP2V							25-08-21	C	Inv.23	C2	c4	32
37		AFILL
38
Sheet4

Cell Formulas
Range	Formula
I12,I26	I12	=FORMULATEXT(I13)
I13:N21	I13	=SORT(TRANSPOSE(AUNSTACK(ASTACK(AVCLEAN(AUNSTACK(B10:G30,3))),6)),SEQUENCE(,6))
I27:N36	I27	=AFILL(APP2V("",F46#),B4:C6)
Dynamic array formulas.

 

[jaeiow]

Would AUNSTACK feasibly upgrade from using MakeArray? It seems very slow on large data sets.

 

[Xlambda]

Sorry for replying so late. With the upcoming Easter holiday, finally, I am having some spare time to catch up.
Indeed, all lambda helper functions, as iterative algorithms, are more or less,.."slow".
Back to 2021, this function was in the phase of enthusiastic experimental mastering of lambda helper functions My iterative algorithmic awareness came later here ( ASCAN post 44-46 ).when I compared 3 of my functions, 2 iterative methods ("digital"), 1 non iterative ("analogic") and one that tboulden uses for his THUNKIT and SCANBYROWS designs that use makearray and came out to be really really slow.
That's why, using the experience we have accumulated with these functions so far, I think it is time for us to contribute, to write and adopt a Lambda Performance Act (LPA). This should include general rules and recommendations of modern lambda "programming".
A glimpse of my LPA's draft golden rules:
- Use an iterative algorithm design (lambda helper functions) ONLY when there is no other non-iterative variant possible.
- All functions (especially the ones that use new lambda helper functions) should be accompanied by a Lambda Performance Certificate (LPC).
This should contain, like any "responsible" algorithm design, info about: identifying the iterative variable (nr.rows or nr.unique values/vector…) ; the complexity of the algorithm ( Big O notation, like O(nr.rows^2) or O(3*nr.clms) ) ; speed estimation for different sample data sets sizes. Like 30s/1Mrows or instant/<=10k colums or fair/100k elements when fair means less then 30s calc time...etc ; error management level, data types spectrum etc.. (To be continued...)

To cut my revolutionary long ideas short and get back to the function, following the 1st golden rule,for AUNSTACK we can have a quite simple non-iterative, or "analogic" or "in bulk" lighting fast alternative. Positive that this function will comply any speed performance requirements
(no MAKEARRAY) same arguments, name and functionality: AUNSTACK(ar,[h])

=LAMBDA(ar, [h],
    LET(
        a, IF(ar = "", "", ar),
        x, ROWS(a),
        y, COLUMNS(a),
        k, MEDIAN(1, h, x),
        s, SEQUENCE(, ROUNDUP(x / k, 0) * y),
        q, QUOTIENT(s - 1, y),
        m, MOD(s - 1, y) + 1,
        IFERROR(INDEX(a, SEQUENCE(k) + k * q, m), "")
    )
)

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1								h,10 (height or new nr. of rows)
2		ar						=AUNSTACK(B3#,10)
3		1	2	3	4			1	2	3	4	41	42	43	44	81	82	83	84
4		5	6	7	8			5	6	7	8	45	46	47	48	85	86	87	88
5		9	10	11	12			9	10	11	12	49	50	51	52	89	90	91	92
6		13	14	15	16			13	14	15	16	53	54	55	56	93	94	95	96
7		17	18	19	20			17	18	19	20	57	58	59	60	97	98	99	100
8		21	22	23	24			21	22	23	24	61	62	63	64	101	102	103	104
9		25	26	27	28			25	26	27	28	65	66	67	68	105	106	107	108
10		29	30	31	32			29	30	31	32	69	70	71	72
11		33	34	35	36			33	34	35	36	73	74	75	76
12		37	38	39	40			37	38	39	40	77	78	79	80
13		41	42	43	44
14		45	46	47	48			Note: As we see, AUNSTACK has nothing in common with transpose
15		49	50	51	52
16		53	54	55	56
17		57	58	59	60
18		61	62	63	64
19		65	66	67	68
20		69	70	71	72
21		73	74	75	76
22		77	78	79	80
23		81	82	83	84
24		85	86	87	88
25		89	90	91	92
26		93	94	95	96
27		97	98	99	100
28		101	102	103	104
29		105	106	107	108
30
Sheet1

Cell Formulas
Range	Formula
H2	H2	=FORMULATEXT(H3)
B3:E29	B3	=SEQUENCE(27,4)
H3:S12	H3	=AUNSTACK(B3#,10)
Dynamic array formulas.

 

[Xlambda]

Check this out, latest YT of PQ's master Goodly, and our lambda's alternatives.

First thing first, the ASTACK version of latest "analogic" non iterative concept used for AUNSTACK.
ASTACK(ar,[w]) (w: width)

=LAMBDA(ar, [w],
    LET(
        a, IF(ar = "", "", ar),
        x, ROWS(a),
        y, COLUMNS(a),
        k, MEDIAN(1, w, y),
        s, SEQUENCE(ROUNDUP(y / k, 0) * x),
        q, QUOTIENT(s - 1, x),
        m, MOD(s - 1, x) + 1,
        IFERROR(INDEX(a, m, SEQUENCE(, k) + k * q), "")
    )
)

Book1.xlsx
	A	B	C	D	E	F	G	H
1
2				2 ways of solving
3				=ASTACK(TRANSPOSE(Table1[Data]),4)
4		Data		Cecilia Chapman	New York	42	(257) 563-7401
5		Cecilia Chapman		Iris Watson	Hong Kong	33	(372) 587-2335
6		New York		Joanne M	Delhi	39	(786) 713-8616
7		42		Peter Highes	Delhi	39	(786) 713-8616
8		(257) 563-7401		Chandeep	Gurgaon	36	(786) 713-8616
9		Iris Watson
10		Hong Kong		=TRANSPOSE(AUNSTACK(Table1[Data],4))
11		33		Cecilia Chapman	New York	42	(257) 563-7401
12		(372) 587-2335		Iris Watson	Hong Kong	33	(372) 587-2335
13		Joanne M		Joanne M	Delhi	39	(786) 713-8616
14		Delhi		Peter Highes	Delhi	39	(786) 713-8616
15		39		Chandeep	Gurgaon	36	(786) 713-8616
16		(786) 713-8616
17		Peter Highes
18		Delhi
19		39
20		(786) 713-8616
21		Chandeep
22		Gurgaon
23		36
24		(786) 713-8616
25
26
Sheet2

Cell Formulas
Range	Formula
D3,D10	D3	=FORMULATEXT(D4)
D4:G8	D4	=ASTACK(TRANSPOSE(Table1[Data]),4)
D11:G15	D11	=TRANSPOSE(AUNSTACK(Table1[Data],4))
Dynamic array formulas.

Another example for ASTACK

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1
2		ar
3		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23
4		24	25	26	27	28	29	30	31	32	33	34	35	36	37	38	39	40	41	42	43	44	45	46
5		47	48	49	50	51	52	53	54	55	56	57	58	59	60	61	62	63	64	65	66	67	68	69
6		70	71	72	73	74	75	76	77	78	79	80	81	82	83	84	85	86	87	88	89	90	91	92
7		93	94	95	96	97	98	99	100	101	102	103	104	105	106	107	108	109	110	111	112	113	114	115
8		116	117	118	119	120	121	122	123	124	125	126	127	128	129	130	131	132	133	134	135	136	137	138
9		139	140	141	142	143	144	145	146	147	148	149	150	151	152	153	154	155	156	157	158	159	160	161
10
11		w,7
12		=ASTACK(B3#,7)
13		1	2	3	4	5	6	7
14		24	25	26	27	28	29	30
15		47	48	49	50	51	52	53
16		70	71	72	73	74	75	76
17		93	94	95	96	97	98	99
18		116	117	118	119	120	121	122
19		139	140	141	142	143	144	145
20		8	9	10	11	12	13	14
21		31	32	33	34	35	36	37
22		54	55	56	57	58	59	60
23		77	78	79	80	81	82	83
24		100	101	102	103	104	105	106
25		123	124	125	126	127	128	129
26		146	147	148	149	150	151	152
27		15	16	17	18	19	20	21
28		38	39	40	41	42	43	44
29		61	62	63	64	65	66	67
30		84	85	86	87	88	89	90
31		107	108	109	110	111	112	113
32		130	131	132	133	134	135	136
33		153	154	155	156	157	158	159
34		22	23
35		45	46
36		68	69
37		91	92
38		114	115
39		137	138
40		160	161
41
Sheet3

Cell Formulas
Range	Formula
B3:X9	B3	=SEQUENCE(7,23)
B12	B12	=FORMULATEXT(B13)
B13:H40	B13	=ASTACK(B3#,7)
Dynamic array formulas.

 

[Xlambda]

Part II - Unstack a Million Uneven Rows to Columns in Power Query

Solving this with a simple formula or a lambda if we want.
advantages: max nr. of fields (5) calculated implicitly, no refresh, calc time instant (for 910k rows PQ needed 2min 10sec, seems that any calculation implying any kind of iterations takes time, compared with non-iterative dynamic "in bulk" instant calculations)
VUNSTACK(v) Vector Unstack v: column vector

=LAMBDA(v,
    LET(
        a, (v = "") * SEQUENCE(ROWS(v)),
        b, FILTER(a, a),
        c, VSTACK(0, DROP(b, -1)),
        d, b - c - 1,
        e, SEQUENCE(ROWS(b)) ^ 0 + SEQUENCE(, MAX(d)) - 1,
        IF(e <= d, INDEX(v, e + c), "")
    )
)

Unstack-Uneven-Rows-in-Columns PQ.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3		Data		=VUNSTACK(LargeData[Data])						rows	=ROWS(LargeData[Data])
4		Cecilia Chapman		Cecilia Chapman	New York	42	(257) 563-7401			table	910419
5		New York		Iris Watson	Hong Kong						=ROWS(D4#)
6		42		Joanne M	Delhi	39	(786) 713-8616			result	197917
7		(257) 563-7401		Peter Highes	Delhi	39
8				Chandeep	Dubai	36	999	Red
9		Iris Watson		Cecilia Chapman	New York	42	(257) 563-7401
10		Hong Kong		Iris Watson	Hong Kong
11				Joanne M	Delhi	39	(786) 713-8616
12		Joanne M		Peter Highes	Delhi	39
13		Delhi		Chandeep	Dubai	36	999	Red
14		39		Cecilia Chapman	New York	42	(257) 563-7401
15		(786) 713-8616		Iris Watson	Hong Kong
16				Joanne M	Delhi	39	(786) 713-8616
17		Peter Highes		Peter Highes	Delhi	39
18		Delhi		Chandeep	Dubai	36	999	Red
19		39		Cecilia Chapman	New York	42	(257) 563-7401
20				Iris Watson	Hong Kong
21		Chandeep		Joanne M	Delhi	39	(786) 713-8616
22		Dubai		Peter Highes	Delhi	39
23		36		Chandeep	Dubai	36	999	Red
24		999		Cecilia Chapman	New York	42	(257) 563-7401
25		Red		Iris Watson	Hong Kong
26				Joanne M	Delhi	39	(786) 713-8616
27		Cecilia Chapman		Peter Highes	Delhi	39
28		New York		Chandeep	Dubai	36	999	Red
29		42		Cecilia Chapman	New York	42	(257) 563-7401
30		(257) 563-7401		Iris Watson	Hong Kong
31				Joanne M	Delhi	39	(786) 713-8616
32		Iris Watson		Peter Highes	Delhi	39
33		Hong Kong		Chandeep	Dubai	36	999	Red
34				Cecilia Chapman	New York	42	(257) 563-7401
35		Joanne M		Iris Watson	Hong Kong
36		Delhi		Joanne M	Delhi	39	(786) 713-8616
37		39		Peter Highes	Delhi	39
38		(786) 713-8616		Chandeep	Dubai	36	999	Red
39				Cecilia Chapman	New York	42	(257) 563-7401
40		Peter Highes		Iris Watson	Hong Kong
41		Delhi		Joanne M	Delhi	39	(786) 713-8616
42		39		Peter Highes	Delhi	39
43				Chandeep	Dubai	36	999	Red
44		Chandeep		Cecilia Chapman	New York	42	(257) 563-7401
45		Dubai		Iris Watson	Hong Kong
46		36		Joanne M	Delhi	39	(786) 713-8616
47		999		Peter Highes	Delhi	39
48		Red		Chandeep	Dubai	36	999	Red
49				Cecilia Chapman	New York	42	(257) 563-7401
50		Cecilia Chapman		Iris Watson	Hong Kong
51		New York		Joanne M	Delhi	39	(786) 713-8616
52		42		Peter Highes	Delhi	39
53		(257) 563-7401		Chandeep	Dubai	36	999	Red
54				Cecilia Chapman	New York	42	(257) 563-7401
55		Iris Watson		Iris Watson	Hong Kong
56		Hong Kong		Joanne M	Delhi	39	(786) 713-8616
57				Peter Highes	Delhi	39
58		Joanne M		Chandeep	Dubai	36	999	Red
59		Delhi		Cecilia Chapman	New York	42	(257) 563-7401
60		39		Iris Watson	Hong Kong
61		(786) 713-8616		Joanne M	Delhi	39	(786) 713-8616
62				Peter Highes	Delhi	39
63		Peter Highes		Chandeep	Dubai	36	999	Red
64		Delhi		Cecilia Chapman	New York	42	(257) 563-7401
65		39		Iris Watson	Hong Kong
66				Joanne M	Delhi	39	(786) 713-8616
67		Chandeep		Peter Highes	Delhi	39
68		Dubai		Chandeep	Dubai	36	999	Red
69		36		Cecilia Chapman	New York	42	(257) 563-7401
70		999		Iris Watson	Hong Kong
71		Red		Joanne M	Delhi	39	(786) 713-8616
72				Peter Highes	Delhi	39
73		Cecilia Chapman		Chandeep	Dubai	36	999	Red
74		New York		Cecilia Chapman	New York	42	(257) 563-7401
75		42		Iris Watson	Hong Kong
76		(257) 563-7401		Joanne M	Delhi	39	(786) 713-8616
77				Peter Highes	Delhi	39
78		Iris Watson		Chandeep	Dubai	36	999	Red
79		Hong Kong		Cecilia Chapman	New York	42	(257) 563-7401
80				Iris Watson	Hong Kong
81		Joanne M		Joanne M	Delhi	39	(786) 713-8616
82		Delhi		Peter Highes	Delhi	39
83		39		Chandeep	Dubai	36	999	Red
84		(786) 713-8616		Cecilia Chapman	New York	42	(257) 563-7401
85				Iris Watson	Hong Kong
86		Peter Highes		Joanne M	Delhi	39	(786) 713-8616
87		Delhi		Peter Highes	Delhi	39
88		39		Chandeep	Dubai	36	999	Red
89				Cecilia Chapman	New York	42	(257) 563-7401
90		Chandeep		Iris Watson	Hong Kong
91		Dubai		Joanne M	Delhi	39	(786) 713-8616
92		36		Peter Highes	Delhi	39
93		999		Chandeep	Dubai	36	999	Red
94		Red		Cecilia Chapman	New York	42	(257) 563-7401
95				Iris Watson	Hong Kong
96		Cecilia Chapman		Joanne M	Delhi	39	(786) 713-8616
97		New York		Peter Highes	Delhi	39
98		42		Chandeep	Dubai	36	999	Red
99		(257) 563-7401		Cecilia Chapman	New York	42	(257) 563-7401
100				Iris Watson	Hong Kong
101		Iris Watson		Joanne M	Delhi	39	(786) 713-8616
102		Hong Kong		Peter Highes	Delhi	39
103				Chandeep	Dubai	36	999	Red
Sheet1

Cell Formulas
Range	Formula
D3,K5,K3	D3	=FORMULATEXT(D4)
D4:H197920	D4	=VUNSTACK(LargeData[Data])
K4	K4	=ROWS(LargeData[Data])
K6	K6	=ROWS(D4#)
Dynamic array formulas.

 

[Xlambda]

How easy is to solve a task with the proper lambda tools?
Check this out Minda's latest YT:

=LET(
    q, C3:N7,
    b, LAMBDA(x, BYROW(x, LAMBDA(x, SUM(x)))),
    HSTACK(AUNSTACK(b(ASTACK(q, 3)), 5), b(q))
)

or without ASTACK, AUNSTACK

=LET(
    q, C3:N7,
    b, LAMBDA(x, BYROW(x, LAMBDA(x, SUM(x)))),
    HSTACK(TRANSPOSE(WRAPCOLS(b(WRAPROWS(TOCOL(q), 3)), 4)), b(q))
)

or , for fun:

=LET(
    q, C3:N7,
    b, LAMBDA(x, BYROW(x, LAMBDA(x, SUM(x)))),
    DROP(REDUCE(0, {1, 4, 7, 10, 0}, LAMBDA(v, i, HSTACK(v, IF(i, b(CHOOSECOLS(q, SEQUENCE(3, , i))), b(q))))), , 1)
)

summarise_mths_into_qtrs.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2			Jan-13	Feb-13	Mar-13	Apr-13	May-13	Jun-13	Jul-13	Aug-13	Sep-13	Oct-13	Nov-13	Dec-13
3		Things 1	9	5	4	4	9	4	2	8	1	8	8	3
4		Things 2	9	3	2	6	3	1	10	3	4	6	10	4
5		Things 3	2	9	8	9	5	7	5	5	9	5	5	9
6		Things 4	10	9	1	8	3	6	5	8	3	7	6	1
7		Things 5	1	5	1	7	4	9	7	3	10	6	7	3
8
9
10		C12	=LET(q,C3:N7,b,LAMBDA(x,BYROW(x,LAMBDA(x,SUM(x)))),HSTACK(AUNSTACK(b(ASTACK(q,3)),5),b(q)))
11			Quarter 1	Quarter 2	Quarter 3	Quarter 4	Total
12		Things 1	18	17	11	19	65
13		Things 2	14	10	17	20	61
14		Things 3	19	21	19	19	78
15		Things 4	20	17	16	14	67
16		Things 5	7	20	20	16	63
17
18
19			=LET(q,C3:N7,b,LAMBDA(x,BYROW(x,LAMBDA(x,SUM(x)))),HSTACK(TRANSPOSE(WRAPCOLS(b(WRAPROWS(TOCOL(q),3)),4)),b(q)))
20			18	17	11	19	65
21			14	10	17	20	61
22			19	21	19	19	78
23			20	17	16	14	67
24			7	20	20	16	63
25
26			=LET(q,C3:N7,b,LAMBDA(x,BYROW(x,LAMBDA(x,SUM(x)))),DROP(REDUCE(0,{1,4,7,10,0},LAMBDA(v,i,HSTACK(v,IF(i,b(CHOOSECOLS(q,SEQUENCE(3,,i))),b(q))))),,1))
27			18	17	11	19	65
28			14	10	17	20	61
29			19	21	19	19	78
30			20	17	16	14	67
31			7	20	20	16	63
32
Sheet2

Cell Formulas
Range	Formula
C10	C10	=FORMULATEXT(C12)
C12:G16	C12	=LET(q,C3:N7,b,LAMBDA(x,BYROW(x,LAMBDA(x,SUM(x)))),HSTACK(AUNSTACK(b(ASTACK(q,3)),5),b(q)))
C19,C26	C19	=FORMULATEXT(C20)
C20:G24	C20	=LET(q,C3:N7,b,LAMBDA(x,BYROW(x,LAMBDA(x,SUM(x)))),HSTACK(TRANSPOSE(WRAPCOLS(b(WRAPROWS(TOCOL(q),3)),4)),b(q)))
C27:G31	C27	=LET(q,C3:N7,b,LAMBDA(x,BYROW(x,LAMBDA(x,SUM(x)))),DROP(REDUCE(0,{1,4,7,10,0},LAMBDA(v,i,HSTACK(v,IF(i,b(CHOOSECOLS(q,SEQUENCE(3,,i))),b(q))))),,1))
Dynamic array formulas.