ARESIZE

[schardt679]

ARESIZE resizes an array given the number of rows and/or columns to add or remove and values to use for new rows/columns.

Thanks to RicoS's RESIZEARRAY function for the inspiration.
If both rows and columns are added, the column value(s) takes precedence. To change this, take the first two conditions of PreRes and Result and swap them
Row_Val can handle multiple values in row format (across columns). Col_Val can handle multiple values in column format (down rows).
Optional arguments require comma to work. Calls CELLCOUNT & ACONSTANT.
Other functions on minisheet: AFORMULATEXT.

Screen Tip/ Comment: =ARESIZE(Array☛ range, [nRow_Top]☛ #⇧Ｒ; =0, [nRow_Bottom]☛ #⇩Ｒ; =0, [nCol_Left]☛ #⇦Ｃ; =0, [nCol_Right]☛ #⇨Ｃ; =0, [Row_Val]☛ Ｒ; ="", [Col_Val]☛ Ｃ; ="") ⁂[]=optional: use ","; =default; #=±INT; =omit; =value; Ｒ/Ｃ=Row/Col; ⇧⇩⇦⇨=Loc

=LAMBDA(Array,nRow_Top,nRow_Bottom,nCol_Left,nCol_Right,Row_Val,Col_Val,
      LET(Arr, Array,          nRowT, MAX(N(IFERROR(nRow_Top,))),          nRowB, MAX(N(IFERROR(nRow_Bottom,))),
             nColL, MAX(N(IFERROR(nCol_Left,))),          nColR, MAX(N(IFERROR(nCol_Right,))),
             RowCt, CELLCOUNT(Arr, 1),          RowAdd, nRowT+nRowB,          ColCt, CELLCOUNT(Arr, 2),          ColAdd, nColL+nColR,
             NumScan, OR(RowAdd<=-RowCt,  ColAdd<=-ColCt),          NumMSG, "Remove less rows/columns",
             RowSeq, SEQUENCE(RowCt+RowAdd),          ColSeq, SEQUENCE(, ColCt+ColAdd),
             RowVal, ACONSTANT(Row_Val,, ColSeq),          ColVal, ACONSTANT(Col_Val, RowSeq,),
             RowValColCt, CELLCOUNT(RowVal, 2),          ColValRowCt, CELLCOUNT(ColVal, 1),
             RowValScan, ColSeq>RowValColCt,          ColValScan, RowSeq> ColValRowCt,
             PreRowT, ACONSTANT(RowVal, nRowT,),          PreRowB, ACONSTANT(RowVal, nRowB,),
             PreColL, ACONSTANT(ColVal,, nColL),          PreColR, ACONSTANT(ColVal,, nColR),
             ValRowT, INDEX(PreRowT, RowSeq, ColSeq),          ValRowB, INDEX(PreRowB, RowSeq-RowCt-nRowT, ColSeq),
             ValColL, INDEX(PreColL, RowSeq, ColSeq),          ValColR, INDEX(PreColR, RowSeq, ColSeq-ColCt-nColL),
             ValArr, INDEX(IF(Arr="", "", Arr), RowSeq-nRowT, ColSeq-nColL),
             PreRes, IFS(RowSeq<=nRowT, IF(RowValScan, "", ValRowT),  RowSeq>RowCt+nRowT,  IF(RowValScan, "", ValRowB),  1, ValArr),
             Result, IFS(ColSeq<=nColL, IF(ColValScan, "", ValColL),  ColSeq>ColCt+nColL, IF(ColValScan, "", ValColR),  1, PreRes),
             Return, IF(NumScan, NumMSG, Result),
             Return
      )
  )

LAMBDA Examples.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		ARESIZE
2		Sample Data				Rows_Top:	1		Result
3		Apple	Watermelon	Pineapple		Rows_Bottom:				Fruit	Fruit	Fruit
4		Banana	Apple			Columns_Left:	1			Apple	Watermelon	Pineapple
5		#N/A	Pear	Grape		Columns_Right:				Banana	Apple
6		Pineapple	Banana	Pear		Row_Value:	Fruit			#N/A	Pear	Grape
7						Column_Value:				Pineapple	Banana	Pear
8
9		1 row is added to the top and 0 rows are added to the bottom.
10		1 column is added to the left and 0 columns are added to the right.
11		The new row value is a single repeated value.
12		The new column value is an empty string.
13		=ARESIZE(B3:D6, G2, G3, G4, G5, G6, G7)
14
15
16
17		Sample Data				Rows_Top:			Result
18		Apple	Watermelon	Pineapple		Rows_Bottom:	-1		Apple	Watermelon	Pineapple	Lemon
19		Banana	Apple			Columns_Left:			Banana	Apple		Canteloupe
20		#N/A	Pear	Grape		Columns_Right:	1		#N/A	Pear	Grape	Honeydew
21		Pineapple	Banana	Pear		Row_Value:	Fruit
22						Column_Value:	Lemon
23							Canteloupe
24		0 rows are added to the top and 1 row is removed from the bottom.					Honeydew
25		0 columns are added to the left and 1 column is added to the right.
26		The new row value is a single repeated value.
27		The new column value is an array of values
28		=ARESIZE(B18:D21, G17, G18, G19, G20, G21, G22:G24)
29
30
31
32		Sample Data				Rows_Top:	-1		Result
33		Apple	Watermelon	Pineapple		Rows_Bottom:	2		1	Banana	Apple
34		Banana	Apple			Columns_Left:	1		2	#N/A	Pear	Grape
35		#N/A	Pear	Grape		Columns_Right:			3	Pineapple	Banana	Pear
36		Pineapple	Banana	Pear		Row_Value:	Apple			Apple	Apple	Apple
37						Column_Value:	1			Apple	Apple	Apple
38							2
39		1 row is removed from the top and 2 rows are added to the bottom.					3
40		1 column is added to the left and 0 columns are added to the right.
41		The new row value is a single repeated value.
42		The new column value is an array of values
43		=ARESIZE(B33:D36, G32, G33, G34, G35, G36, G37:G39)
44
ARESIZE

Cell Formulas
Range	Formula
I3:L7	I3	=ARESIZE(B3:D6, G2, G3, G4, G5, G6, G7)
B13,B43,B28	B13	=FORMULATEXT(I3)
I18:L20,I33:L37	I18	=ARESIZE(B18:D21, G17, G18, G19, G20, G21, G22:G24)
Dynamic array formulas.

 

[schardt679]

I have been adding screen tips to my LAMBDA functions. Right now, you are limited to 255 characters or less. So I put each function name, argument or note in a separate cell using commas and parentheses when applicable. I combine them using a double space in TEXTJOIN for clarity. Finally I have a small character count check to ensure the comment does not exceed the 255 limit.

LAMBDA Examples.xlsx
	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
47		Screen Tip Comment Setup:
48		=ARESIZE(Array☛ range,
49		[nRow_Top]☛ #⇧Ｒ; ❎=0✅,
50		[nRow_Bottom]☛ #⇩Ｒ; ❎=0✅,
51		[nCol_Left]☛ #⇦Ｃ; ❎=0✅,
52		[nCol_Right]☛ #⇨Ｃ; ❎=0✅,
53		[Row_Val]☛ Ｒ❔; ❎=""✅,
54		[Col_Val]☛ Ｃ❔; ❎=""✅)
55		⁂[]=optional: use ","; ✅=default; #=±INT; ❎=omit; ❔=value; Ｒ/Ｃ=Row/Col; ⇧⇩⇦⇨=Loc
56
57		Screen Tip Comment:
58		=ARESIZE(Array☛ range, [nRow_Top]☛ #⇧Ｒ; ❎=0✅, [nRow_Bottom]☛ #⇩Ｒ; ❎=0✅, [nCol_Left]☛ #⇦Ｃ; ❎=0✅, [nCol_Right]☛ #⇨Ｃ; ❎=0✅, [Row_Val]☛ Ｒ❔; ❎=""✅, [Col_Val]☛ Ｃ❔; ❎=""✅) ⁂[]=optional: use ","; ✅=default; #=±INT; ❎=omit; ❔=value; Ｒ/Ｃ=Row/Col; ⇧⇩⇦⇨=Loc
59
60
61		Total Char:	252
62
ARESIZE

Cell Formulas
Range	Formula
O58	O58	=TEXTJOIN(" ",, O48:O55)
P61	P61	=LEN(O58)

 

[schardt679]

ARESIZE has been rewritten. It is a little shorter, takes advantage of the optional argument syntax, and values can now be repeated in a looped manner.

ARESIZE resizes an array given the number of rows and/or columns to add or remove and values to use for new rows/columns.

Thanks to RicoS's RESIZEARRAY function for the inspiration.
If both rows and columns are added, Col_Val takes precedence. To change this, swap the first two conditions of PreRes and Result.
Row_Val and Col_Val can handle arrays of values.
Optional arguments require comma to work. Calls IFBLANK, CELLCOUNT & ACONSTANT.
Other functions on minisheet: AFORMULATEXT.

Screen Tip/ Comment: =ARESIZE(Array☛ range, [nRow_Top]☛ #⇧ＲＡ; =0, [nRow_Bottom]☛ #⇩ＲＡ; =0, [nCol_Left]☛ #⇦ＣＡ; =0, [nCol_Right]☛ #⇨ＣＡ; =0, [Row_Val]☛ Ｒ; ="", [Col_Val]☛ Ｃ; ="") ⁂[]=optional; =default; #=±INT; =omit; =value; Ａ/Ｒ/Ｃ=Array/Row/Col; ⇧⇩⇦⇨=Loc

The syntax is the same: ARESIZE(Array,[nRow_Top],[nRow_Bottom],[nCol_Left],[nCol_Right],[Row_Val],[Col_Val])

The arguments are the same nut it now repeats the Value argument differently.
1. Single values still repeat across rows and columns.
2. If nRow_Top and/or nRow_Bottom or nCol_Left and/or nCol_Right are greater than Row_Val or Col_Val row or column count, the values are looped until that number is reached.
3. If nRow_Top and/or nRow_Bottom or nCol_Left and/or nCol_Right are less than Row_Val or Col_Val, the values are indexed to that row or column number.
[nRow_Top]: Optional. Number of rows to add (+) or remove (-) at the top side of the array; ignored☛ 0.
[nRow_Bottom]: Optional. Number of rows to add (+) or remove (-) at the bottom side of the array; ignored☛ 0.
[nCol_Left]: Optional. Number of columns to add (+) or remove (-) at the left side of the array; ignored☛ 0.
[nCol_Right]: Optional. Number of columns to add (+) or remove (-) at the right side of the array; ignored☛ 0.
[Row_Val]: Optional. Value(s) to use for new rows in row format (across columns); ignored☛ empty string.
[Col_Val]: Optional. Value(s) to use for new columns in column format (down rows); ignored☛ empty string.

=LAMBDA(Array,[nRow_Top],[nRow_Bottom],[nCol_Left],[nCol_Right],[Row_Val],[Col_Val],
      LET(Arr, IFBLANK(Array, ""),          nRowTop, MAX(IFERROR(--(nRow_Top),)),          nRowBot, MAX(IFERROR(--(nRow_Bottom),)),
             nColLef, MAX(IFERROR(--(nCol_Left),)),          nColRig, MAX(IFERROR(--(nCol_Right),)),
             RowCt, CELLCOUNT(Arr, 1),          RowSum, nRowTop+nRowBot,          ColCt, CELLCOUNT(Arr, 2),          ColSum, nColLef+nColRig,
             NumScan, OR(RowSum<=-RowCt,  ColSum<=-ColCt),          NumMSG, "Remove less rows/columns",
             RowSeq, SEQUENCE(RowCt+RowSum),          ColSeq, SEQUENCE(, ColCt+ColSum),
             RowVal, ACONSTANT(Row_Val, RowSeq, ColSeq),          ColVal, ACONSTANT(Col_Val, RowSeq, ColSeq),
             RowTopVal, INDEX(RowVal, RowSeq, ColSeq),          RowBotVal, INDEX(RowVal, RowSeq-RowCt-nRowTop, ColSeq),
             OrgArr, INDEX(Arr, RowSeq-nRowTop, ColSeq-nColLef),
             ColLefVal, INDEX(ColVal, RowSeq, ColSeq),          ColRigVal, INDEX(ColVal, RowSeq, ColSeq-ColCt-nColLef),
             PreRes, IFS(RowSeq<=nRowTop, RowTopVal,  RowSeq>RowCt+nRowTop, RowBotVal,  1, OrgArr),
             Result, IFS(ColSeq<=nColLef, ColLefVal,  ColSeq>ColCt+nColLef, ColRigVal,  1, PreRes),
             Return, IF(NumScan, NumMSG,  Result),
             Return
      )
  )

LAMBDA Examples.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		ARESIZE
2
3		Sample Data				Rows_Top:	1			Result
4		Apple	Watermelon	Pineapple		Rows_Bottom:					Fruit	Fruit	Fruit
5		Banana	Apple			Columns_Left:	1				Apple	Watermelon	Pineapple
6		#N/A	Pear	Grape		Columns_Right:					Banana	Apple
7		Pineapple	Banana	Pear		Row_Value:	Fruit				#N/A	Pear	Grape
8						Column_Value:					Pineapple	Banana	Pear
9
10		1 row is added to the top and 0 rows are added to the bottom.
11		1 column is added to the left and 0 columns are added to the right.
12		The new row value is a single repeated value.
13		The new column value is an empty string.
14		=ARESIZE(B4:D7, G3,, G5,, G7)
15
16
17
18		Sample Data				Rows_Top:				Result
19		Apple	Watermelon	Pineapple		Rows_Bottom:	-1			Apple	Watermelon	Pineapple	Lemon
20		Banana	Apple			Columns_Left:				Banana	Apple		Canteloupe
21		#N/A	Pear	Grape		Columns_Right:	1			#N/A	Pear	Grape	Honeydew
22		Pineapple	Banana	Pear		Row_Value:
23						Column_Value:	Lemon
24							Canteloupe
25							Honeydew
26
27		0 rows are added to the top and 1 row is removed from the bottom.
28		0 columns are added to the left and 1 column is added to the right.
29		Since no rows are being Row_Value has been omitted.
30		The new column value is a vertical array of values.
31		=ARESIZE(B19:D22,, G19,, G21,, G23:G25)
32
33
34
35		Sample Data				Rows_Top:	-1			Result
36		Apple	Watermelon	Pineapple		Rows_Bottom:	2			Apple
37		Banana	Apple			Columns_Left:	-1			Pear	Grape
38		#N/A	Pear	Grape		Columns_Right:				Banana	Pear
39		Pineapple	Banana	Pear		Row_Value:	Canteloupe	Peach		Canteloupe	Peach
40						Column_Value:				Canteloupe	Peach
41
42		1 row is removed from the top and 2 rows are added to the bottom.
43		1 column is removed from the left and 0 columns are added to the right.
44		The new row value is a horizontal array of values.
45		Since no columns are being added, Column_Value has been omitted.
46		=ARESIZE(B36:D39, G35, G36, G37,, G39:H39)
47
48
49
50		Sample Data				Rows_Top:				Result
51		Apple	Watermelon	Pineapple		Rows_Bottom:	4			Watermelon	Pineapple	Lemon	Kiwi
52		Banana	Apple			Columns_Left:	-1			Apple		Lime	Plum
53		#N/A	Pear	Grape		Columns_Right:	2			Pear	Grape	Honeydew	Clementine
54		Pineapple	Banana	Pear		Row_Value:	Canteloupe	Peach		Banana	Pear	Lemon	Kiwi
55							Orange	Strawberry		Canteloupe	Peach	Lime	Plum
56						Column_Value:	Lemon	Kiwi		Orange	Strawberry	Honeydew	Clementine
57							Lime	Plum		Canteloupe	Peach	Lemon	Kiwi
58							Honeydew	Clementine		Orange	Strawberry	Lime	Plum
59
60		0 rows are added to the top and 4 rows are added to the bottom.
61		1 column is removed from the left and 2 columns are added to the right.
62		The new row value is a 2D array of values.
63		The new column value is a 2D array of values.
64		=ARESIZE(B51:D54,, G51, G52, G53, G54:H55, G56:H58)
65
ARESIZE

Cell Formulas
Range	Formula
J4:M8	J4	=ARESIZE(B4:D7, G3,, G5,, G7)
B14,B46	B14	=FORMULATEXT(J4)
J19:M21	J19	=ARESIZE(B19:D22,, G19,, G21,, G23:G25)
B31	B31	=FORMULATEXT(J19)
J36:K40	J36	=ARESIZE(B36:D39, G35, G36, G37,, G39:H39)
J51:M58	J51	=ARESIZE(B51:D54,, G51, G52, G53, G54:H55, G56:H58)
B64	B64	=FORMULATEXT(J51)
Dynamic array formulas.

 

[schardt679]

ARESIZE has been rewritten again. It is a little shorter It should be a lot faster.
With the looping index functionality of ACONSTANT, I realized the following:
1. RowVal was exactly the same as RowTopVal and RowBotVal | ColVal was exactly the same as ColLefVal and ColRigVal
2. They were redundant, so I removed them. This shortened version functions exactly like the previous revision.

ARESIZE resizes an array given the number of rows and/or columns to add or remove and values to use for new rows/columns.

Thanks to RicoS's RESIZEARRAY function for the inspiration.
If both rows and columns are added, Col_Val takes precedence. To change this, swap the first two logical_tests & value_if_trues in Result.
Row_Val and Col_Val can handle arrays of values.
Optional arguments require comma to work. Calls IFBLANK, CELLCOUNT & ACONSTANT.
Other functions on minisheet: AFORMULATEXT.

Screen Tip/ Comment: =ARESIZE(Array☛ range, [Num_Top]☛ #⇧ＲＡ; =0, [Num_Bottom]☛ #⇩ＲＡ; =0, [Num_Left]☛ #⇦ＣＡ; =0, [Num_Right]☛ #⇨ＣＡ; =0, [Row_Val]☛ Ｒ; ="", [Col_Val]☛ Ｃ; ="") ⁂[]=optional; =default; #=±INT; =omit; =value; Ａ/Ｒ/Ｃ=Array/Row/Col; ⇧⇩⇦⇨=Loc

The syntax is the same: ARESIZE(Array,[Num_Top],[Num_Bottom],[Num_Left],[Num_Right],[Row_Val],[Col_Val])

The arguments are the same but it now repeats the Value arguments differently.
1. Single values still repeat across rows and columns.
2. If Num_Top and/or Num_Bottom or Num_Left and/or Num_Right are greater than Row_Val or Col_Val row or column count, the values are looped until that number is reached.
3. If Num_Top and/or Num_Bottom or Num_Left and/or Num_Right are less than Row_Val or Col_Val, the values are indexed to that row or column number.
[Num_Top]: Optional. Number of rows to add (+) or remove (-) at the top side of the array; ignored☛ 0.
[Num_Bottom]: Optional. Number of rows to add (+) or remove (-) at the bottom side of the array; ignored☛ 0.
[Num_Left]: Optional. Number of columns to add (+) or remove (-) at the left side of the array; ignored☛ 0.
[Num_Right]: Optional. Number of columns to add (+) or remove (-) at the right side of the array; ignored☛ 0.
[Row_Val]: Optional. Value(s) to use for new rows in row format (across columns); ignored☛ empty string.
[Col_Val]: Optional. Value(s) to use for new columns in column format (down rows); ignored☛ empty string.

=LAMBDA(Array,[Num_Top],[Num_Bottom],[Num_Left],[Num_Right],[Row_Val],[Col_Val],
      LET(Arr, IFBLANK(Array, ""),          nTop, MAX(IFERROR(--(Num_Top),)),          nBot, MAX(IFERROR(--(Num_Bottom),)),
             nLef, MAX(IFERROR(--(Num_Left),)),          nRig, MAX(IFERROR(--(Num_Right),)),
             RowCt, CELLCOUNT(Arr, 1),          RowSum, nTop+nBot,          ColCt, CELLCOUNT(Arr, 2),          ColSum, nLef+nRig,
             NumScan, OR(RowSum<=-RowCt,  ColSum<=-ColCt),          NumMSG, "Remove less rows/columns",
             RowSeq, SEQUENCE(RowCt+RowSum),          ColSeq, SEQUENCE(, ColCt+ColSum),          Val, INDEX(Arr, RowSeq-nTop, ColSeq-nLef),
             RowVal, ACONSTANT(Row_Val, RowSeq, ColSeq),          ColVal, ACONSTANT(Col_Val, RowSeq, ColSeq),
             RowScan, (RowSeq<=nTop)+(RowSeq>RowCt+nTop),          ColScan, (ColSeq<=nLef)+(ColSeq>ColCt+nLef),
             Result, IFS(ColScan, ColVal,  RowScan, RowVal,  1, Val),          Return, IF(NumScan, NumMSG,  Result),
             Return
      )
  )

LAMBDA Examples.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		ARESIZE
2
3		Sample Data				Num_Top:	1			Result
4		Apple	Watermelon	Pineapple		Num_Bottom:					Fruit	Fruit	Fruit
5		Banana	Apple			Num_Left:	1				Apple	Watermelon	Pineapple
6		#N/A	Pear	Grape		Num_Right:					Banana	Apple
7		Pineapple	Banana	Pear		Row_Val:	Fruit				#N/A	Pear	Grape
8						Col_Val:					Pineapple	Banana	Pear
9
10		1 row is added to the top and 0 rows are added to the bottom.
11		1 column is added to the left and 0 columns are added to the right.
12		The new row value is a single repeated value.
13		The new column value is an empty string.
14		=ARESIZE(B4:D7, G3,, G5,, G7)
15
ARESIZE

Cell Formulas
Range	Formula
J4:M8	J4	=ARESIZE(B4:D7, G3,, G5,, G7)
B14	B14	=FORMULATEXT(J4)
Dynamic array formulas.