ASTACK

[Xlambda]

ASTACK new!! Array Stack, stacks an array for a certain width. Calls AUNSTACK

=LAMBDA(a,[w],TRANSPOSE(AUNSTACK(TRANSPOSE(a),w)))

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
1	sample 1															if w,0 or omitted w=1
2	1	2	3	4	5	6	7	8	9	10	11	12				or equivalent ASTACK(a)=AFLAT(TRANSPOSE(a),1)
3	13	14		16	17	18	19	20	21	22	23	24						=ASTACK(A2:L4)
4	25	26	27	28	29	30	31	#N/A	33	34	35	36						1
5																		13
6	w,3				sample 2													25
7	=ASTACK(A2:L4,3)				1	2	3	4	5	6	7	8	9	10	11	12		2
8	1	2	3		13	14		16	17	18	19	20	21	22	23	24		14
9	13	14			25	26	27	28	29	30	31	#N/A	33	34	35	36		26
10	25	26	27															3
11	4	5	6		w,5						if w<0,w,-5 w=abs(w)=5
12	16	17	18		=ASTACK(E7:P9,5)						=ASTACK(E7:P9,-5)							27
13	28	29	30		1	2	3	4	5		1	2	3	4	5			4
14	7	8	9		13	14		16	17		13	14		16	17			16
15	19	20	21		25	26	27	28	29		25	26	27	28	29			28
16	31		33		6	7	8	9	10		6	7	8	9	10			5
17	10	11	12		18	19	20	21	22		18	19	20	21	22			17
18	22	23	24		30	31		33	34		30	31		33	34			29
19	34	35	36		11	12					11	12						6
20					23	24					23	24						18
21					35	36					35	36						30
22																		7
23	if abs(w)>=clms(a), w=clms(a), array will remain intact,(only errors will be replaced with null strings)																	19
24	=ASTACK(A2:L4,13)																	31
25	1	2	3	4	5	6	7	8	9	10	11	12						8
26	13	14		16	17	18	19	20	21	22	23	24						20
27	25	26	27	28	29	30	31		33	34	35	36
28																		9
29																		21
30																		33
31																		10
32																		22
33																		34
34																		11
35																		23
36																		35
37																		12
38																		24
39																		36
40
new ASTACK post 1

Cell Formulas
Range	Formula
R3,A24,K12,E12,A7	R3	=FORMULATEXT(R4)
H4,L9	H4	=NA()
R4:R39	R4	=ASTACK(A2:L4)
A8:C19	A8	=ASTACK(A2:L4,3)
E13:I21	E13	=ASTACK(E7:P9,5)
K13:O21	K13	=ASTACK(E7:P9,-5)
A25:L27	A25	=ASTACK(A2:L4,13)
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]

Used same examples since both functions are used.

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.

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(AFLAT(AHCLEAN(ASTACK(B10:G30,2))),6)),SEQUENCE(,6))
13				25-08-21	C	03-08-21	D		03-08-21	D	Inv.46	D3	d1	12
14				Inv.23	C2	Inv.46	D3		06-08-21	A	Inv.72	A3	a8	29
15				c4	32	d1	12		06-08-21	B	Inv.58	B4	b9	29
16				15-08-21	B				15-08-21	B	Inv.18	B2	b5	19
17				Inv.18	B2				15-08-21	D	Inv.33	D7	d8	28
18				b5	19				17-08-21	A	Inv.12	A7	a1	15
19		17-08-21	A			17-08-21	C		17-08-21	C	Inv.09	C5	c3	47
20		Inv.12	A7			Inv.09	C5		18-08-21	A	Inv.01	A3	a6	23
21		a1	15			c3	47		25-08-21	C	Inv.23	C2	c4	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("",I13#),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									15-08-21	D	Inv.33	D7	d8	28
33		functions							17-08-21	A	Inv.12	A7	a1	15
34		ASTACK							17-08-21	C	Inv.09	C5	c3	47
35		AUNSTACK							18-08-21	A	Inv.01	A3	a6	23
36		AFLAT							25-08-21	C	Inv.23	C2	c4	32
37		AHCLEAN
38		APP2V
39		AFILL
40
Sheet4

Cell Formulas
Range	Formula
I12,I26	I12	=FORMULATEXT(I13)
I13:N21	I13	=SORT(TRANSPOSE(AUNSTACK(AFLAT(AHCLEAN(ASTACK(B10:G30,2))),6)),SEQUENCE(,6))
I27:N36	I27	=AFILL(APP2V("",I13#),B4:C6)
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	Q	R	S	T	U
1	Quite unlikely to receive data as in previous post, but if we do, the combo will do the trick.													If not for subtotals versatility, most common scenarios
2	Combo was designed also, for subtotals constructions. Check										ASUBTOT			of unstacking and stacking do not even require dedicated functions,
3	This is how subtotals concept works in a basic example, step by step:
4	Task: Insert subtotals values every 3 rows													sample data that has 4 records with 3 fields each
5								4. astack and clean						Inv 01		unstacking
6	1		1. aunstack					=AHCLEAN(ASTACK(C19#))						04-09-21	=XLOOKUP(SEQUENCE(4,3),SEQUENCE(4*3),N5:N16)
7	3		=AUNSTACK(A6:A16,3)					1						23		Inv 01	04-09-21	23
8	4		1	5	6	8		3		5. append Grand Total				Inv 02		Inv 02	05-09-21	17
9	5		3	8	4	9		4		=APP2V(H7#,SUM(A6:A16))				05-09-21		Inv 03	05-09-21	17
10	8		4	7	6			8		1				17		Inv 04	06-09-21	43
11	7							5		3				Inv 03		or
12	6							8		4				05-09-21		=ARESIZE(N5:N16,4,3)
13	4		2. totals					7		8				17		Inv 01	04-09-21	23
14	6		=BYCOL(C8#,LAMBDA(a,SUM(a)))					20		5				Inv 04		Inv 02	05-09-21	17
15	8		8	20	16	17		6		8				06-09-21		Inv 03	05-09-21	17
16	9							4		7				43		Inv 04	06-09-21	43
17			3. appending					6		20
18			=APP2V(C8#,C15#)					16		6				and for stacking			=AFLAT(P7#)
19			1	5	6	8		8		4							Inv 01
20			3	8	4	9		9		6							04-09-21
21			4	7	6			17		16							23
22			8	20	16	17				8							Inv 02
23										9							05-09-21
24		Note: At step 2. other functions can be used								17							17
25		MIN,MAX, AVERAGE..etc.								61							Inv 03
26																	05-09-21
27		functions															17
28		AHCLEAN															Inv 04
29		APP2V															06-09-21
30		ARESIZE															43
31
stk-ustk 6

Cell Formulas
Range	Formula
H6,Q18,C18,C14,P12,J9,C7	H6	=FORMULATEXT(H7)
O6	O6	=FORMULATEXT(P7)
H7:H21	H7	=AHCLEAN(ASTACK(C19#))
P7:R10	P7	=XLOOKUP(SEQUENCE(4,3),SEQUENCE(4*3),N5:N16)
C8:F10	C8	=AUNSTACK(A6:A16,3)
J10:J25	J10	=APP2V(H7#,SUM(A6:A16))
P13:R16	P13	=ARESIZE(N5:N16,4,3)
C15:F15	C15	=BYCOL(C8#,LAMBDA(a,SUM(a)))
C19:F22	C19	=APP2V(C8#,C15#)
Q19:Q30	Q19	=AFLAT(P7#)
Dynamic array formulas.