APP2H

[Xlambda]

APP2H APPends 2 arrays Horizontally, 2nd array column index and offset functionality. !! NEW !! MAKEARRAY

=LAMBDA(a1r,a2r,[i],[o],
    LET(a,IF(a1r="","",a1r),b,IF(a2r="","",a2r),c,ROWS(a),d,ROWS(b),e,COLUMNS(a),f,COLUMNS(b),j,IF(OR(ISOMITTED(i),i=0),1,MEDIAN(i,-f,f)),
      IFERROR(MAKEARRAY(MAX(IF(o<0,c-o,c),IF(o>0,d+o,d)),e+IF(j<0,-j,f-j+1),
        LAMBDA(r,c,LET(x,IF(o>=0,IF(c>e,IF(r>o,r-o,""),r),IF(c<=e,IF(r>-o,r+o,""),r)),y,IF(c>e,IF(j>0,c-e+j-1,c-e),c),IF(c<=e,INDEX(a,x,y),INDEX(b,x,y))))),"")
    )
)

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
1	"i" column index argument (for 2nd array, "a2r" (b in our examples))																if array arguments are omitted, or contant arrays
2	i omitted or 0 or 1, appends whole b to a																i,o,omitted				=APP2H(,C13:D15,2,1)
3	i>0, appends only columns indexes >=i of b (from i to the right, including i)																=APP2H(,C13:D15)						i,2,o,1
4	i<0, appends only column indexes <=abs(i) of b (from abs(i) to the left, including i)																	a	b			b
5	if abs(i)>colms(b) and i<0 i= -clms(b),so whole b is appended (from clms(b) to left)																	c
6	if abs(i)>colms(b) and i>0 i=clms(b),so only last column of b is appended (from clms(b) to right)																	e	f			f
7	"o" offset argument																i,,o,1				i,,o-1
8	o=0 arrays will be top aligned															=APP2H(C13:D15,{2;2},,1)					=APP2H(C13:D15,"T",,-1)
9	o>0 array b will offset "o" rows down																a	b					T
10	o<0 array b will offset "o" rows up																c		2		a	b
11																	e	f	2		c
12			a																		e	f
13			a	b				o,omitted									o, different values
14			c
15			e	f				i,omitted									i,omitted,o,-1
16		b						=APP2H(C13:D15,B17:E19)									=APP2H(C13:D15,B17:E19,,-1)
17		1	2	3	4			a	b	1	2	3	4						1	2	3	4
18		5	6		8			c		5	6		8				a	b	5	6		8
19		9	10	11	12			e	f	9	10	11	12				c		9	10	11	12
20																	e	f
21
22				i,3				i,3									i,3,o,1
23		b		→				=APP2H(C13:D15,B24:E26,3)									=APP2H(C13:D15,B24:E26,3,1)
24		1	2	3	4			a	b	3	4						a	b
25		5	6		8			c			8						c		3	4
26		9	10	11	12			e	f	11	12						e	f		8
27																			11	12
28
29				i,-3				i,-3									i-3,-2
30		b		←				=APP2H(C13:D15,B31:E33,-3)									=APP2H(C13:D15,B17:E19,-3,-2)
31		1	2	3	4			a	b	1	2	3							1	2	3
32		5	6		8			c		5	6								5	6
33		9	10	11	12			e	f	9	10	11					a	b	9	10	11
34																	c
35																	e	f
36
37		i>0 and i>clms(b)=> i=4						i,100									i,10,o,2
38		b			→			=APP2H(C13:D15,B39:E41,100)									=APP2H(C13:D15,B39:E41,10,2)
39		1	2	3	4			a	b	4							a	b
40		5	6		8			c		8							c
41		9	10	11	12			e	f	12							e	f	4
42																			8
43																			12
44
45		i<0,and abs(i)>clms(b)=> i=-4						i,-100									i,5,o,1
46		b			←			=APP2H(C13:D15,B47:E49,-100)									=APP2H(INDEX(H47#,SEQUENCE(3),SEQUENCE(,4)),H47#,5,1)
47		1	2	3	4			a	b	1	2	3	4				a	b	1	2
48		5	6		8			c		5	6		8				c		5	6	3	4
49		9	10	11	12			e	f	9	10	11	12				e	f	9	10		8
50																					11	12
51																	!! Offsetting same array !!
52
53
54													i,o,omitted
55			c				d						=APP2H(C56:E59,G56:J57)
56			a	b	c		1	2	3	4			a	b	c	1	2	3	4
57			d	e	f		5	6	7	8			d	e	f	5	6	7	8
58			g	h	i								g	h	i
59			j	k	l								j	k	l
60
61								i,3					i,2,o,1
62							d	→					=APP2H(C56:E59,G63:J64,2,1)
63							1	2	3	4			a	b	c
64							5	6	7	8			d	e	f	2	3	4
65													g	h	i	6	7	8
66													j	k	l
67
68									i, -3				i-3,o,2
69							d		←				=APP2H(C56:E59,G70:J71,-3,2)
70							1	2	3	4			a	b	c
71							5	6	7	8			d	e	f
72													g	h	i	1	2	3
73													j	k	l	5	6	7
74
75
76
APP2H post

Cell Formulas
Range	Formula
U2,M69,M62,M55,Q46,H46,Q38,H38,Q30,H30,Q23,H23,Q16,H16,U8,Q3	U2	=FORMULATEXT(U3)
U3:V6	U3	=APP2H(,C13:D15,2,1)
Q4:S6	Q4	=APP2H(,C13:D15)
P8	P8	=FORMULATEXT(Q9)
Q9:S11	Q9	=APP2H(C13:D15,{2;2},,1)
U9:W12	U9	=APP2H(C13:D15,"T",,-1)
H17:M19	H17	=APP2H(C13:D15,B17:E19)
Q17:V20	Q17	=APP2H(C13:D15,B17:E19,,-1)
H24:K26	H24	=APP2H(C13:D15,B24:E26,3)
Q24:T27	Q24	=APP2H(C13:D15,B24:E26,3,1)
H31:L33	H31	=APP2H(C13:D15,B31:E33,-3)
Q31:U35	Q31	=APP2H(C13:D15,B17:E19,-3,-2)
H39:J41	H39	=APP2H(C13:D15,B39:E41,100)
Q39:S43	Q39	=APP2H(C13:D15,B39:E41,10,2)
H47:M49	H47	=APP2H(C13:D15,B47:E49,-100)
Q47:V50	Q47	=APP2H(INDEX(H47#,SEQUENCE(3),SEQUENCE(,4)),H47#,5,1)
M56:S59	M56	=APP2H(C56:E59,G56:J57)
M63:R66	M63	=APP2H(C56:E59,G63:J64,2,1)
M70:R73	M70	=APP2H(C56:E59,G70:J71,-3,2)
Dynamic array formulas.

 

[Xlambda]

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
1	Corner arrays														All corners
2		a						b							b1			b2			b3			b4
3		1	2	3	4			A	B						A	B		E	F		i	J		M	N
4		5	6	7	8			C	D						C	D		G	H		K	L		O	P
5		9	10	11	12
6		13	14	15	16
7													=AFILL(AFILL(APP2H(O3:P4,APP2H(B3#,X3:Y4,,4),,2),R3:S4,1,2,7,8),U3:V4,7,8,1,2)
8																	A	B					E	F
9	=APP2H(B3#,H3:I4,,-ROWS(H3:I4))							=APP2H(B3#,H3:I4,,ROWS(B3#))									C	D					G	H
10					A	B		1	2	3	4								1	2	3	4
11					C	D		5	6	7	8								5	6	7	8
12	1	2	3	4				9	10	11	12								9	10	11	12
13	5	6	7	8				13	14	15	16								13	14	15	16
14	9	10	11	12								A	B				i	J					M	N
15	13	14	15	16								C	D				K	L					O	P
16
17
18	=APP2H(H3:I4,B3#,,ROWS(H3:I4))							=APP2H(H3:I4,B3#,,-ROWS(B3#))								=AFILL(AFILL(Q8#,AA21:AD24,3,6),AA27:AD30,,,3,6)
19	A	B								1	2	3	4				A	B	A1	A2	A3	A4	E	F
20	C	D								5	6	7	8				C	D	B1	B2	B3	B4	G	H			c1
21			1	2	3	4				9	10	11	12				11	12	1	2	3	4	21	22			11	12	21	22
22			5	6	7	8				13	14	15	16				13	14	5	6	7	8	23	24			13	14	23	24
23			9	10	11	12		A	B								15	16	9	10	11	12	25	26			15	16	25	26
24			13	14	15	16		C	D								17	18	13	14	15	16	27	28			17	18	27	28
25																	i	J	C1	C2	C3	C4	M	N
26																	K	L	C5	C6	C7	C8	O	P			c2
27																											A1	A2	A3	A4
28		other functions on minisheet																									B1	B2	B3	B4
29		AFILL															=APUZZLE(Q19#,AA33#,3,6,3,6)										C1	C2	C3	C4
30		APUZZLE															A	B	A1	A2	A3	A4	E	F			C5	C6	C7	C8
31																	C	D	B1	B2	B3	B4	G	H
32																	11	12	16	15	14	13	21	22			d
33																	13	14	12	11	10	9	23	24			16	15	14	13
34																	15	16	8	7	6	5	25	26			12	11	10	9
35																	17	18	4	3	2	1	27	28			8	7	6	5
36																	i	J	C1	C2	C3	C4	M	N			4	3	2	1
37																	K	L	C5	C6	C7	C8	O	P
38
APP2H post 2

Cell Formulas
Range	Formula
B3:E6	B3	=SEQUENCE(4,4)
M7	M7	=FORMULATEXT(Q8)
Q8:X15	Q8	=AFILL(AFILL(APP2H(O3:P4,APP2H(B3#,X3:Y4,,4),,2),R3:S4,1,2,7,8),U3:V4,7,8,1,2)
A9,H9,Q29,A18,H18	A9	=FORMULATEXT(A10)
A10:F15	A10	=APP2H(B3#,H3:I4,,-ROWS(H3:I4))
H10:M15	H10	=APP2H(B3#,H3:I4,,ROWS(B3#))
P18	P18	=FORMULATEXT(Q19)
A19:F24	A19	=APP2H(H3:I4,B3#,,ROWS(H3:I4))
H19:M24	H19	=APP2H(H3:I4,B3#,,-ROWS(B3#))
Q19:X26	Q19	=AFILL(AFILL(Q8#,AA21:AD24,3,6),AA27:AD30,,,3,6)
Q30:X37	Q30	=APUZZLE(Q19#,AA33#,3,6,3,6)
AA33:AD36	AA33	=SEQUENCE(4,4,16,-1)
Dynamic array formulas.

 

[Xlambda]

Introducing APPNHV(n,d) , the lambda that writes other lambda, a lambda for appending n arrays horizontally or vertically.
n: nr. arrays to be appended
d: direction, "h", horizontal; "v", vertical

=LAMBDA(n,d,
    IF(AND(d<>{"h","v"}),"check arg",
      LET(u,UPPER(d),a,"APP"&n&u&"=LAMBDA(a1r,a2r,",b,"[a"&SEQUENCE(,n-2,3)&"r],",
        c,CONCAT("APP2"&u&"(a"&SEQUENCE(,n-1)&"r,")&"a"&n&"r",e,REPT(")",n-1),
          CONCAT(a,b,"LET(x,",c,e,",FILTER(x,"&IF(d="v","BYROW","BYCOL")&"(--(x<>""""),LAMBDA(a,SUM(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	X	Y	Z	AA	AB	AC	AD	AE	AF
1	Task: If your routine requires appending n arrays, let's say 5 arrays, horizontally
2	step 1: call APPNHV(5,"h")
3	step 2: copy the cell and paste it as value to any other cell
4	step 3: define the new APP5H
5	Note: for simplicity did not include row/column index or offset arguments found on APP2H and APP2V
6		=APPNHV(5,"h")
7	1.)	APP5H=LAMBDA(a1r,a2r,[a3r],[a4r],[a5r],LET(x,APP2H(a1r,APP2H(a2r,APP2H(a3r,APP2H(a4r,a5r)))),FILTER(x,BYCOL(--(x<>""),LAMBDA(a,SUM(a))))))
8	2.) copy B7 and paste as value
9		APP5H=LAMBDA(a1r,a2r,[a3r],[a4r],[a5r],LET(x,APP2H(a1r,APP2H(a2r,APP2H(a3r,APP2H(a4r,a5r)))),FILTER(x,BYCOL(--(x<>""),LAMBDA(a,SUM(a))))))
10	3.) define APP5V in the name manager
11	Important note:
12	Due to how new function is created , the arguments after second array are optional ,
13	we can use the same created function for appending any nr. of arrays 2<nr<=n ,
14	simply by omitting arguments
15	Arrays can be anywere on the woorkbook
16
17	a1	1	1		a2	2	2	2		a3	3		a4	4	4	4	4		a5	5	5
18		1	1			2		2			3			4	4		4
19						2	2	2						4	4	4	4
20														4	4	4	4
21
22															ignore 3rd argument
23		=APP5H(B17:C18,F17:H19,K17:K18,N17:Q20,T17:U17)													=APP5H(B17:C18,F17:H19,,N17:Q20,T17:U17)
24		1	1	2	2	2	3	4	4	4	4	5	5		1	1	2	2	2	4	4	4	4	5	5
25		1	1	2		2	3	4	4		4				1	1	2		2	4	4		4
26				2	2	2		4	4	4	4						2	2	2	4	4	4	4
27								4	4	4	4									4	4	4	4
28
29		arg. omitted,only 3 arrays, dif.order
30		=APP5H(B17:C18,N17:Q20,F17:H19)
31		1	1	4	4	4	4	2	2	2
32		1	1	4	4		4	2		2
33				4	4	4	4	2	2	2
34				4	4	4	4
35
APPNH H post

Cell Formulas
Range	Formula
B6,B30,O23,B23	B6	=FORMULATEXT(B7)
B7	B7	=APPNHV(5,"h")
B24:M27	B24	=APP5H(B17:C18,F17:H19,K17:K18,N17:Q20,T17:U17)
O24:Y27	O24	=APP5H(B17:C18,F17:H19,,N17:Q20,T17:U17)
B31:J34	B31	=APP5H(B17:C18,N17:Q20,F17:H19)
Dynamic array formulas.