APP2V

[Xlambda]

APP2V APPends 2 arrays Vertically, 2nd array row index and offset functionality. !! NEW !! MAKEARRAY

=LAMBDA(a1r,a2r,[i],[o],
    LET(a,IF(a1r="","",a1r),b,IF(a2r="","",a2r),e,ROWS(a),f,ROWS(b),c,COLUMNS(a),d,COLUMNS(b),j,IF(OR(ISOMITTED(i),i=0),1,MEDIAN(i,-f,f)),
      IFERROR(MAKEARRAY(e+IF(j<0,-j,f-j+1),MAX(IF(o<0,c-o,c),IF(o>0,d+o,d)),
        LAMBDA(r,c,LET(y,IF(o>=0,IF(r>e,IF(c>o,c-o,""),c),IF(r<=e,IF(c>-o,c+o,""),c)),x,IF(r>e,IF(j>0,r-e+j-1,r-e),r),IF(r<=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	Z
1	"i" row index argument (for 2nd array, "a2r" (b in our examples))
2	i omitted or 0 or 1, appends whole b to a
3	i>0, appends only row indexes >=i of b (from i to the bottom, including i)
4	i<0, appends only rows indexes <=abs(i) of b (from abs(i) to the top, including i)
5	if abs(i)>rows(b) and i<0 i= -rows(b),so whole b is appended (from rows(b) to the top)
6	if abs(i)>rows(b) and i>0 i=rows(b),so only last row of b is appended (from rows(b) down)
7	"o" offset argument
8	o=0 arrays will be left aligned																		if arrays arguments omitted
9	o>0 array b will offset "o" clms to the right																		i,o,omitted
10	o<0 array b will offset "o" clms to the left								b										=APP2V(,I11:L14)
11									1	2	3	4
12		a							2		4	5							1	2	3	4
13		a	b	c	d	e	f		3	4		6							2		4	5
14		g	h		i	j	k		4	5	6	4							3	4		6
15																			4	5	6	4
16									i,o,omitted										omitted array becomes a "" null string
17			b						=APP2V(B13:G14,C18:F21)
18			1	2	3	4			a	b	c	d	e	f					i,3,o,1
19			2		4	5			g	h		i	j	k					=APP2V(,I11:L14,3,1)
20			3	4		6			1	2	3	4
21			4	5	6	4			2		4	5								3	4		6
22									3	4		6								4	5	6	4
23									4	5	6	4
24																			if constant arrays
25									i,3,o,1										i,-3,o,-1
26			b						=APP2V(B13:G14,C27:F30,3,1)										=APP2V({2,2},I11:L14,-3,-1)
27			1	2	3	4			a	b	c	d	e	f						2	2
28			2		4	5	i,3		g	h		i	j	k					1	2	3	4
29		↓↓	3	4		6	↓↓			3	4		6						2		4	5
30			4	5	6	4				4	5	6	4						3	4		6
31
32									i,-3,o,2										i,,o,-1
33			b						=APP2V(B13:G14,C34:F37,-3,2)										=APP2V(I11:L14,"T",,-1)
34			1	2	3	4			a	b	c	d	e	f						1	2	3	4
35			2		4	5			g	h		i	j	k						2		4	5
36		↑↑	3	4		6	↑↑				1	2	3	4						3	4		6
37			4	5	6	4	i,-3				2		4	5						4	5	6	4
38											3	4		6					T
39																			i,,o,1
40									i,-2,o,-2										=APP2V("T",I11:L14,,1)
41			b						=APP2V(B13:G14,C42:F45,-2,-2)										T
42			1	2	3	4					a	b	c	d	e	f				1	2	3	4
43			2		4	5					g	h		i	j	k				2		4	5
44			3	4		6			1	2	3	4								3	4		6
45			4	5	6	4			2		4	5								4	5	6	4
46
APP2V post

Cell Formulas
Range	Formula
S10,I41,S40,S33,I33,S26,I26,S19,I17	S10	=FORMULATEXT(S11)
S11:V15	S11	=APP2V(,I11:L14)
I18:N23	I18	=APP2V(B13:G14,C18:F21)
S20:W22	S20	=APP2V(,I11:L14,3,1)
I27:N30	I27	=APP2V(B13:G14,C27:F30,3,1)
S27:V30	S27	=APP2V({2,2},I11:L14,-3,-1)
I34:N38	I34	=APP2V(B13:G14,C34:F37,-3,2)
S34:W38	S34	=APP2V(I11:L14,"T",,-1)
S41:W45	S41	=APP2V("T",I11:L14,,1)
I42:P45	I42	=APP2V(B13:G14,C42:F45,-2,-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
1	Arrays construction
2
3	a					b1			b2			b3			b4
4	1	2	3	4		A	B		E	F		i	J		M	N
5	5	6	7	8		C	D		G	H		K	L		O	P
6	9	10	11	12
7	13	14	15	16
8
9					=AHINSERT(APP2V(APP2H(F4:G5,I4:J5),APP2H(L4:M5,O4:P5)),A4#,2)
10							A	B	1	2	3	4	E	F
11							C	D	5	6	7	8	G	H
12	other functions						i	J	9	10	11	12	M	N
13	APP2H						K	L	13	14	15	16	O	P
14	AHINSERT
15
APP2V post 2

Cell Formulas
Range	Formula
A4:D7	A4	=SEQUENCE(4,4)
E9	E9	=FORMULATEXT(G10)
G10:N13	G10	=AHINSERT(APP2V(APP2H(F4:G5,I4:J5),APP2H(L4:M5,O4:P5)),A4#,2)
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
1	Task: If your routine requires appending n arrays, let's say 5 arrays, vertically
2	step 1: call APPNHV(5,"v")
3	step 2: copy the cell and paste it as value to any other cell
4	step 3: define the new APP5V
5	Note: for simplicity did not include row/column index or offset arguments found on APP2H and APP2V
6		=APPNHV(5,"v")
7	1.)	APP5V=LAMBDA(a1r,a2r,[a3r],[a4r],[a5r],LET(x,APP2V(a1r,APP2V(a2r,APP2V(a3r,APP2V(a4r,a5r)))),FILTER(x,BYROW(--(x<>""),LAMBDA(a,SUM(a))))))
8	2.) copy B7 and paste as value
9		APP5V=LAMBDA(a1r,a2r,[a3r],[a4r],[a5r],LET(x,APP2V(a1r,APP2V(a2r,APP2V(a3r,APP2V(a4r,a5r)))),FILTER(x,BYROW(--(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			=APP5V(B17:C18,B20:D21,B23:B24,B26:E28,B30:C30)
18		1	1				1	1
19							1	1					ignore 3rd argument
20	a2	2	2	2			2	2	2			=APP5V(B17:C18,B20:D21,,B26:E28,B30:C30)
21		2		2			2		2				1	1
22							3						1	1					arg. omitted,only 3 arrays, dif.order
23	a3	3					3						2	2	2			=APP5V(B26:E28,B17:C18,B20:D21)
24		3					4	4	4	4			2		2				4	4	4	4
25							4	4		4			4	4	4	4			4	4		4
26	a4	4	4	4	4		4	4	4	4			4	4		4			4	4	4	4
27		4	4		4		5	5					4	4	4	4			1	1
28		4	4	4	4								5	5					1	1
29																			2	2	2
30	a5	5	5																2		2
31
APPNHV V post

Cell Formulas
Range	Formula
B6	B6	=FORMULATEXT(B7)
B7	B7	=APPNHV(5,"v")
F17,R23,L20	F17	=FORMULATEXT(G18)
G18:J27	G18	=APP5V(B17:C18,B20:D21,B23:B24,B26:E28,B30:C30)
M21:P28	M21	=APP5V(B17:C18,B20:D21,,B26:E28,B30:C30)
S24:V30	S24	=APP5V(B26:E28,B17:C18,B20:D21)
Dynamic array formulas.