AHINSERT

[Xlambda]

AHINSERT Array Horizontal Insert . Inserts an array into other array that splits in 2 parts. Calls APP2H , APP2V
AVINSERT will follow here as well, asap.

=LAMBDA(a,b,[cl],[o],[ci],
    LET(r,ROWS(a),c,COLUMNS(a),s,SEQUENCE(,c),
      IFS(cl<1,APP2H(b,a,ci,-o),cl>=c,APP2H(a,b,,o),
        TRUE,APP2H(APP2H(FILTER(a,s<=cl),b,,o),FILTER(a,s>cl),ci,IF(o<0,-o,0)))
    )
)

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	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM
1	AHINSERT splits array in 2 parts (a1,a2) after a column numbers,inserts other array b at an offset level,column index options for second part a2
2	cl: after column cl, insertion will take place												o: offset arg.							ci: colmn index only for 2nd part of array a,a2													b
3	if cl<1,array b to the left of a like APP2H(b,a)												o=0 or omitted leveled with a							same column index functionality of APP2H										APP2H			A1	B1
4	if cl>=clms(a) array b to the right of a, like APP2H(a,b)												o>0, array b,lowered o rows							ci>0 all clms >=ci of a2 are selected													A2	B2
5													o<0, array b lifted abs(o) rows							ci<0 all clms of a2 <=abs(i) are selected													A3	B3
6																																	A4	B4
7
8										cl,2,o and ci omitted										cl out of range cl<1 or cl>=clms(a)
9		cl,2	➡							=AHINSERT(A10#,AG3:AH6,2)											cl omitted or c<1								all arg. omitted, (b to the left of a)
10	1	2	3	4	5	6	7			1	2	A1	B1	3	4	5	6	7		a									=AHINSERT(T11#,AG3:AH6)
11	8	9	10	11	12	13	14			8	9	A2	B2	10	11	12	13	14		1	2	3	4	5	6	7			A1	B1	1	2	3	4	5	6	7
12	15	16	17	18	19	20	21			15	16	A3	B3	17	18	19	20	21		8	9	10	11	12	13	14			A2	B2	8	9	10	11	12	13	14
13	a1		a2									A4	B4							15	16	17	18	19	20	21			A3	B3	15	16	17	18	19	20	21
14												b																	A4	B4
15										cl,3,o,-2
16			cl,3	➡						=AHINSERT(A17#,AG3:AH6,3,-2)													ci,4				cl<1		cl,-2,o,1,ci,4
17	1	2	3	4	5	6	7						A1	B1						a			➡						=AHINSERT(T18#,AG3:AH6,-2,1,4)
18	8	9	10	11	12	13	14		⬆				A2	B2						1	2	3	4	5	6	7		o,1			4	5	6	7
19	15	16	17	18	19	20	21		o,-2	1	2	3	A3	B3	4	5	6	7		8	9	10	11	12	13	14		⬇	A1	B1	11	12	13	14
20	a1			a2						8	9	10	A4	B4	11	12	13	14		15	16	17	18	19	20	21			A2	B2	18	19	20	21
21										15	16	17			18	19	20	21											A3	B3
22																													A4	B4
23										cl,6,o,1
24						cl,6	➡			=AHINSERT(A25#,AG3:AH6,6,1)													ci,-4						cl,,o,-2,ci,-4
25	1	2	3	4	5	6	7		o,1	1	2	3	4	5	6			7		a			⬅						=AHINSERT(T26#,AG3:AH6,,-2,-4)
26	8	9	10	11	12	13	14		⬇	8	9	10	11	12	13	A1	B1	14		1	2	3	4	5	6	7			A1	B1
27	15	16	17	18	19	20	21			15	16	17	18	19	20	A2	B2	21		8	9	10	11	12	13	14		⬆	A2	B2
28	a1						a2									A3	B3			15	16	17	18	19	20	21		o,-2	A3	B3	1	2	3	4
29																A4	B4												A4	B4	8	9	10	11
30																															15	16	17	18
31				ci,3						cl,1,o,,ci,3
32	cl,1	➡		➡					o,,	=AHINSERT(A33#,AG3:AH6,1,,3)											cl>=clms(a)					cl=clms(a)			cl,7,o,1.,ci,omt. B to the right of a)
33	1	2	3	4	5	6	7		⬇	1	A1	B1	4	5	6	7				a									=AHINSERT(T34#,AG3:AH6,7,1)
34	8	9	10	11	12	13	14			8	A2	B2	11	12	13	14				1	2	3	4	5	6	7		o,1	1	2	3	4	5	6	7
35	15	16	17	18	19	20	21			15	A3	B3	18	19	20	21				8	9	10	11	12	13	14		⬇	8	9	10	11	12	13	14	A1	B1
36	a1	a2									A4	B4								15	16	17	18	19	20	21			15	16	17	18	19	20	21	A2	B2
37																																				A3	B3
38																																				A4	B4
39					ci,-3					cl,2,o,-1,ci,-3
40		cl,2	➡		⬅					=AHINSERT(A41#,AG3:AH6,2,-1,-3)																cl>clms(a)			cl,10,o,-1,ci,4 (ci no effect whatever value)
41	1	2	3	4	5	6	7		⬆			A1	B1							a									=AHINSERT(T42#,AG3:AH6,10,-1,4)
42	8	9	10	11	12	13	14		o,-1	1	2	A2	B2	3	4	5				1	2	3	4	5	6	7		⬆								A1	B1
43	15	16	17	18	19	20	21			8	9	A3	B3	10	11	12				8	9	10	11	12	13	14		o,-1	1	2	3	4	5	6	7	A2	B2
44	a1		a2							15	16	A4	B4	17	18	19				15	16	17	18	19	20	21			8	9	10	11	12	13	14	A3	B3
45																													15	16	17	18	19	20	21	A4	B4
46																													ci is dedicated only for indexing the
47																													2nd side of "a" area ,a2,and in this case
48																													we don't have one, whole a is present
49
50
AHINSERT post

Cell Formulas
Range	Formula
J9,AC41,J40,AC33,J32,AC25,J24,AC17,J16,AC10	J9	=FORMULATEXT(J10)
A10:G12,T42:Z44,A41:G43,T34:Z36,A33:G35,T26:Z28,A25:G27,T18:Z20,A17:G19,T11:Z13	A10	=SEQUENCE(3,7)
J10:R13	J10	=AHINSERT(A10#,AG3:AH6,2)
AC11:AK14	AC11	=AHINSERT(T11#,AG3:AH6)
J17:R21	J17	=AHINSERT(A17#,AG3:AH6,3,-2)
AC18:AH22	AC18	=AHINSERT(T18#,AG3:AH6,-2,1,4)
J25:R29	J25	=AHINSERT(A25#,AG3:AH6,6,1)
AC26:AH30	AC26	=AHINSERT(T26#,AG3:AH6,,-2,-4)
J33:P36	J33	=AHINSERT(A33#,AG3:AH6,1,,3)
AC34:AK38	AC34	=AHINSERT(T34#,AG3:AH6,7,1)
J41:P44	J41	=AHINSERT(A41#,AG3:AH6,2,-1,-3)
AC42:AK45	AC42	=AHINSERT(T42#,AG3:AH6,10,-1,4)
Dynamic array formulas.

 

[Xlambda]

AVINSERT Array Vertical Insert . Inserts an array into other array that splits in 2 parts. Calls APP2V .Arguments explained on minisheet.

=LAMBDA(a,b,[rw],[o],[ri],
    LET(r,ROWS(a),s,SEQUENCE(r),
      IFS(rw<1,APP2V(b,a,ri,-o),rw>=r,APP2V(a,b,,o),
        TRUE,APP2V(APP2V(FILTER(a,s<=rw),b,,o),FILTER(a,s>rw),ri,IF(o<0,-o,0)))
    )
)

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	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
1	AVINSERT splits array in 2 parts (a1,a2) after a row number,inserts other array b at an offset level,row index options for second part a2
2	rw: after row rw, insertion will take place												o: offset arg.								ri: row index only for 2nd part of array a,a2 (omitted,all a2)
3	if rw<1,array "b" on top of "a" like APP2V(b,a)												o=0 or omitted leveled with a								same row index functionality of APP2V										APP2V
4	if rw>=rows(a) array "a" on top of "b", like APP2V(a,b)												o>0, array b,to the right "o" clms								ri>0 all rws >=ri of a2 are selected
5													o<0, array b to the left abs(o) clms								ri<0 all rws of a2 <=abs(ri) are selected
6	b	A1	A2	A3	A4
7		B1	B2	B3	B4			rw,2,ri,omitted						rw,2,o,2								rw,2,o,-1
8															o,2	➡						⬅	o,-1
9	a1	1	8	15				1	8	15				1	8	15							1	8	15
10		2	9	16	rw,2			2	9	16				2	9	16							2	9	16
11	a2	3	10	17	⬇			A1	A2	A3	A4					A1	A2	A3	A4			A1	A2	A3	A4
12		4	11	18				B1	B2	B3	B4					B1	B2	B3	B4			B1	B2	B3	B4
13		5	12	19				3	10	17				3	10	17							3	10	17
14		6	13	20				4	11	18				4	11	18							4	11	18
15		7	14	21				5	12	19				5	12	19							5	12	19
16								6	13	20				6	13	20							6	13	20
17								7	14	21				7	14	21							7	14	21
18								=AVINSERT(B9:D15,B6:E7,2)						=AVINSERT(B9:D15,B6:E7,2,2)								=AVINSERT(B9:D15,B6:E7,2,-1)
19
20								rw,1,o,1,ri,3														rw,2,o,-1,ri,-4
21								o,1	➡													⬅	o,-1
22	a1	1	8	15	rw,1			1	8	15					a1	1	8	15					1	8	15
23	a2	2	9	16	⬇				A1	A2	A3	A4				2	9	16	rw,2				2	9	16
24		3	10	17					B1	B2	B3	B4			a2	3	10	17	⬇			A1	A2	A3	A4
25		4	11	18	⬇	ri,3		4	11	18						4	11	18				B1	B2	B3	B4
26		5	12	19				5	12	19						5	12	19					3	10	17
27		6	13	20				6	13	20						6	13	20	⬆	ri,-4			4	11	18
28		7	14	21				7	14	21						7	14	21					5	12	19
29								=AVINSERT(B22:D28,B6:E7,1,1,3)															6	13	20
30																						=AVINSERT(B22:D28,B6:E7,2,-1,-4)
31
32							all arg. omitted					rw<0							rw,-2,o,1,ri,4
33																			o,1	➡
34	a	1	8	15			A1	A2	A3	A4		a	1	8	15					A1	A2	A3	A4
35		2	9	16			B1	B2	B3	B4			2	9	16					B1	B2	B3	B4
36		3	10	17			1	8	15				3	10	17				4	11	18
37		4	11	18			2	9	16				4	11	18	⬇	ri,4		5	12	19
38		5	12	19			3	10	17				5	12	19				6	13	20
39		6	13	20			4	11	18				6	13	20				7	14	21
40		7	14	21			5	12	19				7	14	21				=AVINSERT(B22:D28,B6:E7,-2,1,4)
41							6	13	20
42							7	14	21										rw,-1,o,-1,ri,-4
43					=AVINSERT(B22:D28,B6:E7)														⬅	o,-1
44												a	1	8	15				A1	A2	A3	A4
45		rw>=rows(a)											2	9	16				B1	B2	B3	B4
46			rw,7,o,1,ri,2 (ri,no effect)										3	10	17					1	8	15
47			o,1	➡									4	11	18	⬆	ri,-4			2	9	16
48			1	8	15								5	12	19					3	10	17
49			2	9	16								6	13	20					4	11	18
50			3	10	17								7	14	21				=AVINSERT(P22:R28,B6:E7,-1,-1,-4)
51			4	11	18
52			5	12	19
53			6	13	20
54			7	14	21
55				A1	A2	A3	A4
56				B1	B2	B3	B4
57			=AVINSERT(B34:D40,B6:E7,7,1,2)
58			ri is dedicated only for indexing the
59			2nd part of "a" area ,a2,and in this case
60			we don't have one, whole "a" is present
61
AVINSERT post

Cell Formulas
Range	Formula
H9:K17	H9	=AVINSERT(B9:D15,B6:E7,2)
N9:S17	N9	=AVINSERT(B9:D15,B6:E7,2,2)
V9:Y17	V9	=AVINSERT(B9:D15,B6:E7,2,-1)
H18,N18,C57,V18	H18	=FORMULATEXT(H9)
H22:L28	H22	=AVINSERT(B22:D28,B6:E7,1,1,3)
V22:Y29	V22	=AVINSERT(B22:D28,B6:E7,2,-1,-4)
H29	H29	=FORMULATEXT(H22)
V30	V30	=FORMULATEXT(V22)
G34:J42	G34	=AVINSERT(B22:D28,B6:E7)
S34:W39	S34	=AVINSERT(B22:D28,B6:E7,-2,1,4)
S40,S50	S40	=FORMULATEXT(S34)
E43	E43	=FORMULATEXT(G34)
S44:V49	S44	=AVINSERT(P22:R28,B6:E7,-1,-1,-4)
C48:G56	C48	=AVINSERT(B34:D40,B6:E7,7,1,2)
Dynamic array formulas.