ARESIZE

[Xlambda]

ARESIZE resizes an array Calls AFILL

=LAMBDA(a,r,c,AFILL(REPT("",SEQUENCE(r,c)),a))

LAMBDA 1.1.1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	sample					2,12
2	1	2	3	4		=ARESIZE(A2:D7,2,12)
3	5	6	7	8		1	2	3	4	5	6	7	8	9	10	11	12
4	9	10	11	12		13	14	15	16	17	18	19	20	21	22	23	24			12,2
5	13	14	15	16																=ARESIZE(A2:D7,12,2)
6	17	18	19	20		3,8									8,3					1	2
7	21	22	23	24		=ARESIZE(A2:D7,3,8)									=ARESIZE(A2:D7,8,3)					3	4
8						1	2	3	4	5	6	7	8		1	2	3			5	6
9						9	10	11	12	13	14	15	16		4	5	6			7	8
10						17	18	19	20	21	22	23	24		7	8	9			9	10
11	other functions														10	11	12			11	12
12	APP2H					4,6									13	14	15			13	14
13	AFILL					=ARESIZE(A2:D7,4,6)									16	17	18			15	16
14						1	2	3	4	5	6				19	20	21			17	18
15						7	8	9	10	11	12				22	23	24			19	20
16						13	14	15	16	17	18									21	22
17						19	20	21	22	23	24									23	24
18
19						5,5
20						=AFILL(ARESIZE(A2:D7,5,5),"X")						=APP2H("?",AFILL(ARESIZE(A2:D7,5,6),{2021,"Aug",9,"Check","this","out"}))
21						1	2	3	4	5
22						6	7	8	9	10			?	1	2	3	4	5	6
23						11	12	13	14	15				7	8	9	10	11	12
24						16	17	18	19	20				13	14	15	16	17	18
25						21	22	23	24	X				19	20	21	22	23	24
26														2021	Aug	9	Check	this	out
27
ARESIZE

Cell Formulas
Range	Formula
F2,F20,F13,O7,F7,T5	F2	=FORMULATEXT(F3)
F3:Q4	F3	=ARESIZE(A2:D7,2,12)
T6:U17	T6	=ARESIZE(A2:D7,12,2)
F8:M10	F8	=ARESIZE(A2:D7,3,8)
O8:Q15	O8	=ARESIZE(A2:D7,8,3)
F14:K17	F14	=ARESIZE(A2:D7,4,6)
L20	L20	=FORMULATEXT(M22)
F21:J25	F21	=AFILL(ARESIZE(A2:D7,5,5),"X")
M22:S26	M22	=APP2H("?",AFILL(ARESIZE(A2:D7,5,6),{2021,"Aug",9,"Check","this","out"}))
Dynamic array formulas.

 

[Xlambda]

New function, ARESIZE(a,[r],[c]), same name, same arguments (can be omitted this time), same functionality, different approach.
[r]: new rows nr. ; [c]: new clms. nr.
As much as I like the first version of the formula, came out with a longer one that does not call any other lambda and adds some new tweaks:
-if r/c omitted, array not resized, only errors/blanks replaced with null strings.
-if only one of the r/c arguments is omitted, the function calculates the smallest other argument to accommodate all elements of initial array.

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

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
1	a,sample
2	1	2	3	4	5	6		r,c,omitted								r,3,c,8
3	7	8	A	a		12		=ARESIZE(A2:F5)								=ARESIZE(A2:F5,3,8)
4	13		b	C	17	18		1	2	3	4	5	6			1	2	3	4	5	6	7	8
5	19	20	21	#N/A	23	24		7	8	A	a		12			A	a		12	13		b	C
6								13		b	C	17	18			17	18	19	20	21		23	24
7		blank						19	20	21		23	24
8		null string (cell F3: ="")					errors/blanks replaced with null strings
9		numeric string (B2:="2")					text/numbers kept as they are, same size shape
10		error							=ISTEXT(I4)			=ISNUMBER(M7)
11									TRUE			TRUE
12										check
13	r,1,c,omitted <=> transpose(aflat(a))
14	=ARESIZE(A2:F5,1)
15	1	2	3	4	5	6	7	8	A	a		12	13		b	C	17	18	19	20	21		23	24
16
17	r,omitted,c,1 <=> aflat(a,1)						Note: If one of the r/c is omitted, the function calculates the smallest
18	=ARESIZE(A2:F5,,1)						other argument (integer) to accommodate all elements of initial array
19	1
20	2					r,3,c,omitted (smallest c=8)
21	3					=ARESIZE(A2:F5,3)
22	4					1	2	3	4	5	6	7	8
23	5					A	a		12	13		b	C
24	6					17	18	19	20	21		23	24
25	7
26	8					r,omitted,c,4 (smallest r=6)							r,5,c,omitted (smallest c=5)
27	A					=ARESIZE(A2:F5,,4)							=ARESIZE(A2:F5,5)
28	a					1	2	3	4				1	2	3	4	5
29						5	6	7	8				6	7	8	A	a
30	12					A	a		12					12	13		b
31	13					13		b	C				C	17	18	19	20
32						17	18	19	20				21		23	24
33	b					21		23	24
34	C
35	17					Only if we want other value than the smallest one,
36	18					we have to introduce it explicitly
37	19					r,5,c,6								r,5,c,4
38	20					=ARESIZE(A2:F5,5,6)								=ARESIZE(A2:F5,5,4)
39	21					1	2	3	4	5	6			1	2	3	4
40						7	8	A	a		12			5	6	7	8
41	23					13		b	C	17	18			A	a		12
42	24					19	20	21		23	24			13		b	C
43														17	18	19	20
44						initial array expanded,								initial array shrinked,
45						null strings added to fit								has less elements than initial array
46						the new dimensions
47
new ARESIZE

Cell Formulas
Range	Formula
B2	B2	="2"
E3	E3	=""
H3,N38,F38,M27,F27,F21,A18,A14,I10,L10,P3	H3	=FORMULATEXT(H4)
H4:M7	H4	=ARESIZE(A2:F5)
P4:W6	P4	=ARESIZE(A2:F5,3,8)
D5	D5	=NA()
I11	I11	=ISTEXT(I4)
L11	L11	=ISNUMBER(M7)
A15:X15	A15	=ARESIZE(A2:F5,1)
A19:A42	A19	=ARESIZE(A2:F5,,1)
F22:M24	F22	=ARESIZE(A2:F5,3)
F28:I33	F28	=ARESIZE(A2:F5,,4)
M28:Q32	M28	=ARESIZE(A2:F5,5)
F39:K43	F39	=ARESIZE(A2:F5,5,6)
N39:Q43	N39	=ARESIZE(A2:F5,5,4)
Dynamic array formulas.

 

[Xlambda]

Use of ARESIZE in real life scenarios, classic unstacking.

LAMBDA 1.1.3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	1.) Classic unstacking
2			The only thing we have to know is not total number of records,
3			only the number of fields of each record, ARESIZE will do the rest
4	sample
5	16-09-21		=ARESIZE(A5:A22,,3)				To filter the record with no data
6	Inv. 01		16-09-21	Inv. 01	23		=AHCLEAN(C6#)
7	23		16-09-21	Inv. 02	32		16-09-21	Inv. 01	23
8	16-09-21		17-09-21	Inv. 03	47		16-09-21	Inv. 02	32
9	Inv. 02		18-09-21	Inv. 04	18		17-09-21	Inv. 03	47
10	32		29-09-21	Inv. 05			18-09-21	Inv. 04	18
11	17-09-21		30-09-21	Inv. 06	47		30-09-21	Inv. 06	47
12	Inv. 03
13	47		sample with "gaps"				with gaps included, nr.fields=4
14	18-09-21		16-09-21				=ARESIZE(C14:C36,,4)
15	Inv. 04		Inv. 01				16-09-21	Inv. 01	23
16	18		23				16-09-21	Inv. 02	32
17	29-09-21						17-09-21	Inv. 03	47
18	Inv. 05		16-09-21				18-09-21	Inv. 04	18
19	#N/A		Inv. 02				29-09-21	Inv. 05
20	30-09-21		32				30-09-21	Inv. 06	47
21	Inv. 06				(we selected C14:C36, (23 rows) without extra gap of last record,
22	47		17-09-21		c was set to 4, r omitted, function knows to calculate smallest multiple of 4
23			Inv. 03		and returns 6 rows)
24			47
25							cleaning
26			18-09-21				=AVCLEAN(AHCLEAN(G15#,1))					other functions
27			Inv. 04				16-09-21	Inv. 01	23			AHCLEAN/AVCLEAN
28			18				16-09-21	Inv. 02	32
29							17-09-21	Inv. 03	47
30			29-09-21				18-09-21	Inv. 04	18
31			Inv. 05				30-09-21	Inv. 06	47
32			#N/A
33							single cell
34			30-09-21				=AVCLEAN(AHCLEAN(ARESIZE(C14:C36,,4),1))
35			Inv. 06				16-09-21	Inv. 01	23
36			47				16-09-21	Inv. 02	32
37							17-09-21	Inv. 03	47
38							18-09-21	Inv. 04	18
39							30-09-21	Inv. 06	47
40
new ARESIZE post 2

Cell Formulas
Range	Formula
C5,G34,G26,G14,G6	C5	=FORMULATEXT(C6)
C6:E11	C6	=ARESIZE(A5:A22,,3)
G7:I11	G7	=AHCLEAN(C6#)
G15:J20	G15	=ARESIZE(C14:C36,,4)
A19,C32	A19	=NA()
G27:I31	G27	=AVCLEAN(AHCLEAN(G15#,1))
G35:I39	G35	=AVCLEAN(AHCLEAN(ARESIZE(C14:C36,,4),1))
Dynamic array formulas.

 

[Xlambda]

LAMBDA 1.1.3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	2.) Classic unstacking, when sample data updates, dynamic single cell formula,no refresh.
2	In case that sample will update in the future with more data records, the only thing we have to do
3	sample 1		is to choose as array argument, a bigger range, even if it has no data yet, like A4:A43
4	16-09-21
5	Inv. 01		before cleaning will look like this				after cleaning, single cell,
6	23		=ARESIZE(A4:A43,,3)				expands when more data is added, dynamically
7	16-09-21		16-09-21	Inv. 01	23
8	Inv. 02		16-09-21	Inv. 02	32		=AHCLEAN(ARESIZE(A4:A43,,3))
9	32		17-09-21	Inv. 03	47		16-09-21	Inv. 01	23		future error replacement with values, updates also, instantly
10	17-09-21		18-09-21	Inv. 04	18		16-09-21	Inv. 02	32		sample 2
11	Inv. 03		18-09-21	Inv. 05			17-09-21	Inv. 03	47		16-09-21		=AHCLEAN(ARESIZE(K11:K50,,3))
12	47		19-09-21	Inv. 06	47		18-09-21	Inv. 04	18		Inv. 01		16-09-21	Inv. 01	23
13	18-09-21		29-09-21	Inv. 07	52		19-09-21	Inv. 06	47		23		16-09-21	Inv. 02	32
14	Inv. 04		30-09-21	Inv. 08	30		29-09-21	Inv. 07	52		16-09-21		17-09-21	Inv. 03	47
15	18						30-09-21	Inv. 08	30		Inv. 02		18-09-21	Inv. 04	18
16	18-09-21										32		18-09-21	Inv. 05	100
17	Inv. 05										17-09-21		19-09-21	Inv. 06	47
18	#N/A						other functions				Inv. 03		23-09-21	Inv. 07	52
19	19-09-21						AHCLEAN				47		29-09-21	Inv. 08	30
20	Inv. 06										18-09-21		30-09-21	Inv. 08	99
21	47										Inv. 04
22	29-09-21										18
23	Inv. 07			first data set							18-09-21
24	52			2nd update							Inv. 05
25	30-09-21			3rd update							100
26	Inv. 08										19-09-21
27	30										Inv. 06
28											47
29											23-09-21
30											Inv. 07
31											52
32											29-09-21
33											Inv. 08
34											30
35											30-09-21
36											Inv. 08
37											99
38
39
40
41
42
43
44
45
46
47
48
49
50
51
new ARESIZE post 3

Cell Formulas
Range	Formula
C6,M11,G8	C6	=FORMULATEXT(C7)
C7:E20	C7	=ARESIZE(A4:A43,,3)
G9:I15	G9	=AHCLEAN(ARESIZE(A4:A43,,3))
M12:O20	M12	=AHCLEAN(ARESIZE(K11:K50,,3))
A18	A18	=NA()
Dynamic array formulas.

 

[Xlambda]

ARESIZE, very handy when it comes to reshaping oversized arrays to fit certain regions of our spreadsheet's real estate, keeping the array sequential order of its elements.

AXMAS.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Task. Reshape a wide array (the ornaments array for AXMAS function) keeping the left to right unicodes values in ascending order. Doing it manually is a hassle.
2
3	⏩	⏪	⏫	⏬	♔	♕	⚓	⚡	⚽	⛄	✅	✳	✴	✵	✶	✷	✸	✹	✺	✻	✼	✽
4	?	?	?	?	?	?	?	?	?	?	?	?	?	?	?	?	?	?	?	?	?	?
5	?	?	?	?	?	?	?	?	?	?	?	?	?	?	?	?	?	?	?	?	?	?
6	?	?	?	?	?	?	?	?	?	?	?	?	?	?	?	?	?	?	?	?	?	?
7
8	=UNICODE(A3:AN6)
9	9193	9194	9195	9196	9812	9813	9875	9889	9917	9924	9989	10035	10036	10037	10038	10039	10040	10041	10042	10043	10044	10045
10	127757	127758	127759	127760	127761	127762	127763	127764	127765	127766	127767	127768	127769	127770	127771	127772	127773	127774	127775	127776	127792	127793
11	127817	127818	127819	127820	127821	127822	127823	127824	127825	127826	127827	127851	127852	127853	127870	127871	127872	127873	127874	127875	127876	127877
12	128151	128152	128153	128154	128155	128156	128157	128158	128159	128160	128161	128162	128205	128269	128270	128302	128303	128308	128309	128310	128311	128312
13
14	r,omitted,c,10
15	=ARESIZE(A9#,,10)											=ARESIZE(A3:AN6,,10)
16	9193	9194	9195	9196	9812	9813	9875	9889	9917	9924		⏩	⏪	⏫	⏬	♔	♕	⚓	⚡	⚽	⛄
17	9989	10035	10036	10037	10038	10039	10040	10041	10042	10043		✅	✳	✴	✵	✶	✷	✸	✹	✺	✻
18	10044	10045	10046	10047	10048	10049	10050	10051	10052	10053		✼	✽	✾	✿	❀	❁	❂	❃	❄	❅
19	10054	10055	10056	10057	10058	10060	10062	11088	11093	127744		❆	❇	❈	❉	❊	❌	❎	⭐	⭕	?
20	127757	127758	127759	127760	127761	127762	127763	127764	127765	127766		?	?	?	?	?	?	?	?	?	?
21	127767	127768	127769	127770	127771	127772	127773	127774	127775	127776		?	?	?	?	?	?	?	?	?	?
22	127792	127793	127794	127799	127800	127801	127802	127803	127804	127805		?	?	?	?	?	?	?	?	?	?
23	127806	127807	127808	127809	127810	127811	127812	127813	127815	127816		?	?	?	?	?	?	?	?	?	?
24	127817	127818	127819	127820	127821	127822	127823	127824	127825	127826		?	?	?	?	?	?	?	?	?	?
25	127827	127851	127852	127853	127870	127871	127872	127873	127874	127875		?	?	?	?	?	?	?	?	?	?
26	127876	127877	127878	127879	127880	127881	127882	127883	127890	127936		?	?	?	?	?	?	?	?	?	?
27	127944	128030	128036	128037	128038	128039	128040	128125	128141	128142		?	?	?	?	?	?	?	?	?	?
28	128151	128152	128153	128154	128155	128156	128157	128158	128159	128160		?	?	?	?	?	?	?	?	?	?
29	128161	128162	128205	128269	128270	128302	128303	128308	128309	128310		?	?	?	?	?	?	?	?	?	?
30	128311	128312	128313	128314	128315	128508	128509	128756	128757	128758		?	?	?	?	?	?	?	?	?	?
31	128759	128760	128761	128762	128763	128764	129351	129352	129353	129358		?	?	?	?	?	?	?	?	?	?
32
33
34												=L16#&" "&A16#
35												⏩ 9193	⏪ 9194	⏫ 9195	⏬ 9196	♔ 9812	♕ 9813	⚓ 9875	⚡ 9889	⚽ 9917	⛄ 9924
36												✅ 9989	✳ 10035	✴ 10036	✵ 10037	✶ 10038	✷ 10039	✸ 10040	✹ 10041	✺ 10042	✻ 10043
37												✼ 10044	✽ 10045	✾ 10046	✿ 10047	❀ 10048	❁ 10049	❂ 10050	❃ 10051	❄ 10052	❅ 10053
38												❆ 10054	❇ 10055	❈ 10056	❉ 10057	❊ 10058	❌ 10060	❎ 10062	⭐ 11088	⭕ 11093	? 127744
39												? 127757	? 127758	? 127759	? 127760	? 127761	? 127762	? 127763	? 127764	? 127765	? 127766
40												? 127767	? 127768	? 127769	? 127770	? 127771	? 127772	? 127773	? 127774	? 127775	? 127776
41												? 127792	? 127793	? 127794	? 127799	? 127800	? 127801	? 127802	? 127803	? 127804	? 127805
42												? 127806	? 127807	? 127808	? 127809	? 127810	? 127811	? 127812	? 127813	? 127815	? 127816
43												? 127817	? 127818	? 127819	? 127820	? 127821	? 127822	? 127823	? 127824	? 127825	? 127826
44												? 127827	? 127851	? 127852	? 127853	? 127870	? 127871	? 127872	? 127873	? 127874	? 127875
45												? 127876	? 127877	? 127878	? 127879	? 127880	? 127881	? 127882	? 127883	? 127890	? 127936
46												? 127944	? 128030	? 128036	? 128037	? 128038	? 128039	? 128040	? 128125	? 128141	? 128142
47												? 128151	? 128152	? 128153	? 128154	? 128155	? 128156	? 128157	? 128158	? 128159	? 128160
48												? 128161	? 128162	? 128205	? 128269	? 128270	? 128302	? 128303	? 128308	? 128309	? 128310
49												? 128311	? 128312	? 128313	? 128314	? 128315	? 128508	? 128509	? 128756	? 128757	? 128758
50												? 128759	? 128760	? 128761	? 128762	? 128763	? 128764	? 129351	? 129352	? 129353	? 129358
51
ARESIZE ornaments

Cell Formulas
Range	Formula
A8,L34,L15,A15	A8	=FORMULATEXT(A9)
A9:AN12	A9	=UNICODE(A3:AN6)
A16:J31	A16	=ARESIZE(A9#,,10)
L16:U31	L16	=ARESIZE(A3:AN6,,10)
L35:U50	L35	=L16#&" "&A16#
Dynamic array formulas.