T_APPNAR

[Xlambda]

T_APPNAR tool formula, a LAMBDA that writes other explicit LAMBDA for appending N arrays horizontally of vertically.
If the arrays we need to append are in different spreadsheets or are created inside other formulas, we can not use recursion to ease our task , we have to construct explicit formulas that fits particular scenarios.
Ex: Formula for appending 6 arrays vertically , we call T_APPNAR(6,"v")=LAMBDA(a1r,a2r,a3r,a4r,a5r,a6r,APPEND2V(APPEND2V(APPEND2V(APPEND2V(APPEND2V(a1r,a2r,),a3r,),a4r,),a5r,),a6r,))
OBS: You need to have already defined APPEND2V and APPEND2H, then you can define like APPEND6V(a1r,a2r,a3r,a4r,a5r,a6r) and reuse it any time you need it.
Note: personal syntax T_, comes from tool, means that are formulas instruments used for helping us on solving bigger picture tasks, like to write other formulas, patterns transformations, interfaces to ease complex conditional formatting formulas etc.

=LAMBDA(na,hv,
     LET(x,OR(hv={"h","v"}),
        a,"=LAMBDA(",b,REPT("a"&SEQUENCE(,na)&"r,",1),
        c,REPT("APPEND2"&UPPER(hv)&"(",SEQUENCE(,na-1,1,0)),
       d,"a1r",e,","&REPT("a"&SEQUENCE(,na-1,2)&"r,)",1),f,")",
       IF(x,CONCAT(a,b,c,d,e,f),"''h''or''v''only")
    )
)

LAMBDA 5.0.xlsm
	A	B	C	D	E	F	G	H	I
1	a1 - 1	a1 - 2		a2 - 1	a2 - 2		a4 - 1	a4 - 2
2	a1 - 3	a1 - 4		a2 - 3	a2 - 4		a4 - 3	a4 - 4
3	a1 - 5	a1 - 6		a2 - 5	a2 - 6		a4 - 5	a4 - 6
4	a1 - 7	a1 - 8		a2 - 7	a2 - 8		a4 - 7	a4 - 8
5	a1 - 9	a1 - 10		a2 - 9	a2 - 10
6	a1 - 11	a1 - 12		a2 - 11	a2 - 12		a5 - 1	a5 - 2
7	a1 - 13	a1 - 14					a5 - 3	a5 - 4
8	a1 - 15	a1 - 16		a3 - 1	a3 - 2		a5 - 5	a5 - 6
9	a1 - 17	a1 - 18		a3 - 3	a3 - 4		a5 - 7	a5 - 8
10	a1 - 19	a1 - 20		a3 - 5	a3 - 6
11	a1 - 21	a1 - 22		a3 - 7	a3 - 8		a6 - 1	a6 - 2
12	a1 - 23	a1 - 24		a3 - 9	a3 - 10		a6 - 3	a6 - 4
13				a3 - 11	a3 - 12		a6 - 5	a6 - 6
14							a6 - 7	a6 - 8
15	ex: append the arrays above all toghether,considering that are not in the same spreadsheet or are results of other array calculations
16	that live inside a formula (we can not use APPENDNHV)
17
18	get to a cell, call =T_APPNAR(3,"v"), the formula shows the string result =LAMBDA(a1r,a2r,a3r,APPEND2V(APPEND2V(a1r,a2r,),a3r,)) , copy the cell, paste in other cell as value, hit the F2, or click the cursor inside the cell, hit Enter, we will get a #CALC! error. Then, from the formula bar, copy the whole formula, hit escape, go to name manager , define APPEND3V, and thats it.Follow the same steps for APPEND3H. Now we can use them.
19
20	=T_APPNAR(3,"v")				=T_APPNAR(3,"h")
21	=LAMBDA(a1r,a2r,a3r,APPEND2V(APPEND2V(a1r,a2r,),a3r,))				=LAMBDA(a1r,a2r,a3r,APPEND2H(APPEND2H(a1r,a2r,),a3r,))
22
23	=LAMBDA(a1r,a2r,a3r,APPEND2V(APPEND2V(a1r,a2r,),a3r,))				=LAMBDA(a1r,a2r,a3r,APPEND2H(APPEND2H(a1r,a2r,),a3r,))
24	#CALC!				#CALC!
25
26	=APPEND3H(A1#,APPEND2V(D1#,D8#,),APPEND3V(G1#,G6#,G11#))
27	a1 - 1	a1 - 2	a2 - 1	a2 - 2	a4 - 1	a4 - 2
28	a1 - 3	a1 - 4	a2 - 3	a2 - 4	a4 - 3	a4 - 4
29	a1 - 5	a1 - 6	a2 - 5	a2 - 6	a4 - 5	a4 - 6
30	a1 - 7	a1 - 8	a2 - 7	a2 - 8	a4 - 7	a4 - 8
31	a1 - 9	a1 - 10	a2 - 9	a2 - 10	a5 - 1	a5 - 2
32	a1 - 11	a1 - 12	a2 - 11	a2 - 12	a5 - 3	a5 - 4
33	a1 - 13	a1 - 14	a3 - 1	a3 - 2	a5 - 5	a5 - 6
34	a1 - 15	a1 - 16	a3 - 3	a3 - 4	a5 - 7	a5 - 8
35	a1 - 17	a1 - 18	a3 - 5	a3 - 6	a6 - 1	a6 - 2
36	a1 - 19	a1 - 20	a3 - 7	a3 - 8	a6 - 3	a6 - 4
37	a1 - 21	a1 - 22	a3 - 9	a3 - 10	a6 - 5	a6 - 6
38	a1 - 23	a1 - 24	a3 - 11	a3 - 12	a6 - 7	a6 - 8
39
40	OBS: Both APPEND2V and ..2H need to have the trailing "," , the ..3V or ..3H don't need them
41	NOTE: If you use this pattern extensivly it can be save as a LAMBDA also, especially for whatever pattern suits you nneds
42
T_APPNAR post

Cell Formulas
Range	Formula
A1:B12	A1	="a1 - "&SEQUENCE(12,2)
D1:E6	D1	="a2 - "&SEQUENCE(6,2)
G1:H4	G1	="a4 - "&SEQUENCE(4,2)
G6:H9	G6	="a5 - "&SEQUENCE(4,2)
D8:E13	D8	="a3 - "&SEQUENCE(6,2)
G11:H14	G11	="a6 - "&SEQUENCE(4,2)
A20,A26,A23,E23,E20	A20	=FORMULATEXT(A21)
A21	A21	=T_APPNAR(3,"v")
E21	E21	=T_APPNAR(3,"h")
A24	A24	=LAMBDA(a1r,a2r,a3r,APPEND2V(APPEND2V(a1r,a2r,),a3r,))
E24	E24	=LAMBDA(a1r,a2r,a3r,APPEND2H(APPEND2H(a1r,a2r,),a3r,))
A27:F38	A27	=APPEND3H(A1#,APPEND2V(D1#,D8#,),APPEND3V(G1#,G6#,G11#))
Dynamic array formulas.

 

[tboulden]

This is neat! Though I think it might get cumbersome if you need more and more arguments, you have to keep up with more and more functions.

Since your APPEND#X always have a blank i parameter, I modified your APPEND2V and APPEND2H like the below. These will allow you to provide a chain of single parameters, followed by a termination parameter; this is a trick I learned from @Lori_M.

This may not be better in practice; it may be just as hard to deal with APPENDV(arr1)(arr2)(arr3)(arr4)...(arrX)(\) as with APPENDXV(arr1,arr2,arr3,...,arrX). But I think its an interesting concept!

APPENDV

=LAMBDA(a,
     LAMBDA(b,
         IF(
             TYPE(b)=16,a,
             LET(
                 ra,ROWS(a),
                 rb,ROWS(b),
                 s,SEQUENCE(ra+rb),
                 sca,SEQUENCE(,COLUMNS(a)),
                 scb,SEQUENCE(,COLUMNS(b)),
                 combined,IF(s<=ra,INDEX(a,s,sca),INDEX(b,s-ra,scb)),
                 APPENDV(combined)
             )
         )
     )
 )

APPENDH

=LAMBDA(a,
     LAMBDA(b,
         IF(
             TYPE(b)=16,a,
             LET(
                 ca,COLUMNS(a),
                 cb,COLUMNS(b),
                 s,SEQUENCE(,ca+cb),
                 sra,SEQUENCE(ROWS(a)),
                 srb,SEQUENCE(ROWS(b)),
                 combined,IF(s<=ca,INDEX(a,sra,s),INDEX(b,srb,s-ca)),
                 APPENDH(combined)
             )
         )
     )
 )

LAMBDAS_Xlambda.xlsx
	A	B	C	D	E	F	G	H
1	a1 - 1	a1 - 2		a2 - 1	a2 - 2		a4 - 1	a4 - 2
2	a1 - 3	a1 - 4		a2 - 3	a2 - 4		a4 - 3	a4 - 4
3	a1 - 5	a1 - 6		a2 - 5	a2 - 6		a4 - 5	a4 - 6
4	a1 - 7	a1 - 8		a2 - 7	a2 - 8
5	a1 - 9	a1 - 10		a2 - 9	a2 - 10		a5 - 1	a5 - 2
6	a1 - 11	a1 - 12					a5 - 3	a5 - 4
7	a1 - 13	a1 - 14		a3 - 1	a3 - 2		a5 - 5	a5 - 6
8	a1 - 15	a1 - 16		a3 - 3	a3 - 4
9	a1 - 17	a1 - 18		a3 - 5	a3 - 6		a6 - 1	a6 - 2
10	a1 - 19	a1 - 20		a3 - 7	a3 - 8		a6 - 3	a6 - 4
11				a3 - 9	a3 - 10		a6 - 5	a6 - 6
12							a6 - 7	a6 - 8
13
14	=APPENDH(A1#)(B1#)(\)			=APPENDV(D1#)(D7#)(\)			=APPENDV(G1#)(G5#)(G9#)(\)
15	a1 - 1	a1 - 2		a2 - 1	a2 - 2		a4 - 1	a4 - 2
16	a1 - 3	a1 - 4		a2 - 3	a2 - 4		a4 - 3	a4 - 4
17	a1 - 5	a1 - 6		a2 - 5	a2 - 6		a4 - 5	a4 - 6
18	a1 - 7	a1 - 8		a2 - 7	a2 - 8		a5 - 1	a5 - 2
19	a1 - 9	a1 - 10		a2 - 9	a2 - 10		a5 - 3	a5 - 4
20	a1 - 11	a1 - 12		a3 - 1	a3 - 2		a5 - 5	a5 - 6
21	a1 - 13	a1 - 14		a3 - 3	a3 - 4		a6 - 1	a6 - 2
22	a1 - 15	a1 - 16		a3 - 5	a3 - 6		a6 - 3	a6 - 4
23	a1 - 17	a1 - 18		a3 - 7	a3 - 8		a6 - 5	a6 - 6
24	a1 - 19	a1 - 20		a3 - 9	a3 - 10		a6 - 7	a6 - 8
25
26	=APPENDH(A15#)(D15#)(G15#)(\)
27	a1 - 1	a1 - 2	a2 - 1	a2 - 2	a4 - 1	a4 - 2
28	a1 - 3	a1 - 4	a2 - 3	a2 - 4	a4 - 3	a4 - 4
29	a1 - 5	a1 - 6	a2 - 5	a2 - 6	a4 - 5	a4 - 6
30	a1 - 7	a1 - 8	a2 - 7	a2 - 8	a5 - 1	a5 - 2
31	a1 - 9	a1 - 10	a2 - 9	a2 - 10	a5 - 3	a5 - 4
32	a1 - 11	a1 - 12	a3 - 1	a3 - 2	a5 - 5	a5 - 6
33	a1 - 13	a1 - 14	a3 - 3	a3 - 4	a6 - 1	a6 - 2
34	a1 - 15	a1 - 16	a3 - 5	a3 - 6	a6 - 3	a6 - 4
35	a1 - 17	a1 - 18	a3 - 7	a3 - 8	a6 - 5	a6 - 6
36	a1 - 19	a1 - 20	a3 - 9	a3 - 10	a6 - 7	a6 - 8
APPENDH_APPENDV

Cell Formulas
Range	Formula
A1:A10	A1	="a1 - "&SEQUENCE(10,1,1,2)
B1:B10	B1	="a1 - "&SEQUENCE(10,1,2,2)
D1:E5	D1	="a2 - "&SEQUENCE(5,2)
G1:H3	G1	="a4 - "&SEQUENCE(3,2)
G5:H7	G5	="a5 - "&SEQUENCE(3,2)
D7:E11	D7	="a3 - "&SEQUENCE(5,2)
G9:H12	G9	="a6 - "&SEQUENCE(4,2)
A14,A26,G14,D14	A14	=FORMULATEXT(A15)
A15:B24	A15	=APPENDH(A1#)(B1#)(\)
D15:E24	D15	=APPENDV(D1#)(D7#)(\)
G15:H24	G15	=APPENDV(G1#)(G5#)(G9#)(\)
A27:F36	A27	=APPENDH(A15#)(D15#)(G15#)(\)
Dynamic array formulas.

 

[Xlambda]

Hi, cool trick!! Thanks for sharing!
? i is the most important argument in the formula. when is ignored acts like appending when it has value acts like replacing. I use it a lot in complex single cell formulas. Adds headers , replacing sections of arrays, constructing unusual patterns with ease, and so on. But for bulk appending arrays as they are your trick is super cool. ✌✌?

 

[tboulden]

Hi, cool trick!! Thanks for sharing!
? i is the most important argument in the formula. when is ignored acts like appending when it has value acts like replacing. I use it a lot in complex single cell formulas. Adds headers , replacing sections of arrays, constructing unusual patterns with ease, and so on. But for bulk appending arrays as they are your trick is super cool. ✌✌?

You're output has been quite prolific, glad I can contribute back a little! I think could probably update to re-include i parameter, but it was late and I was sleepy! Perhaps I try again later!

 

[Xlambda]

Definitely can be done easily, but I think will be carried for the whole bunch.
Also , apart from the versatility of flexible i I can change directions in the same formula fh(fv(fh(a1,a2,2),a3,3),a4,2).
(a1)(a2)(a3)(a4) ..(\) has like 4 in a line, mine has 3 concentric ( ( ( ) ) ) . indeed I have to write little bit more, versatility has to come with some cost?
We have to create one looking like this (a1,v,2)(a2,h,3)(a3,v,2) ,and with recursion, and only 3 rows long formula.?.
Regarding being prolific, I am not prolific at all. Manny ideas and no time. Started a while ago, a study for kind of 3D arrays with recursion or polinomial goal seek solving , still in pending .? Can not alocate to much time to this , only part of my former outdoor spare time. But I am very happy that I found this forum. Actually Mike Girvin pointed it out to me, and here I am. See you around.✌✌??