APP2V

APP2V(a1r,a2r,[i],[o])
a1r
array
a2r
array
[i]
optional,2nd array row index, i<0, selects 2nd array row 1 to row i, i>0, selects 2nd array from i to rows(a2r)
[o]
offset argument, o<0, moves 2nd array "o" clms to the left, o>0, moves 2nd array "o" clms to the right

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

Xlambda

Well-known Member
Joined
Mar 8, 2021
Messages
837
Office Version
  1. 365
Platform
  1. Windows
APP2V APPends 2 arrays Vertically, 2nd array row index and offset functionality. !! NEW !! MAKEARRAY
Excel Formula:
=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
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1"i" row index argument (for 2nd array, "a2r" (b in our examples))
2i omitted or 0 or 1, appends whole b to a
3i>0, appends only row indexes >=i of b (from i to the bottom, including i)
4i<0, appends only rows indexes <=abs(i) of b (from abs(i) to the top, including i)
5if abs(i)>rows(b) and i<0 i= -rows(b),so whole b is appended (from rows(b) to the top)
6if 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
8o=0 arrays will be left alignedif arrays arguments omitted
9o>0 array b will offset "o" clms to the righti,o,omitted
10o<0 array b will offset "o" clms to the leftb=APP2V(,I11:L14)
111234 
12a2451234
13abcdef346245
14ghijk4564346
154564
16i,o,omittedomitted array becomes a "" null string
17b=APP2V(B13:G14,C18:F21)
181234abcdefi,3,o,1
19245ghijk=APP2V(,I11:L14,3,1)
203461234 
214564245346
223464564
234564
24if constant arrays
25i,3,o,1i,-3,o,-1
26b=APP2V(B13:G14,C27:F30,3,1)=APP2V({2,2},I11:L14,-3,-1)
271234abcdef 22
28245i,3ghijk1234
29↓↓346↓↓346245
3045644564346
31
32i,-3,o,2i,,o,-1
33b=APP2V(B13:G14,C34:F37,-3,2)=APP2V(I11:L14,"T",,-1)
341234abcdef 1234
35245ghijk245
36↑↑346↑↑1234346
374564i,-32454564
38346T
39i,,o,1
40i,-2,o,-2=APP2V("T",I11:L14,,1)
41b=APP2V(B13:G14,C42:F45,-2,-2)T
421234 abcdef1234
43245ghijk245
443461234346
4545642454564
46
APP2V post
Cell Formulas
RangeFormula
S10,I41,S40,S33,I33,S26,I26,S19,I17S10=FORMULATEXT(S11)
S11:V15S11=APP2V(,I11:L14)
I18:N23I18=APP2V(B13:G14,C18:F21)
S20:W22S20=APP2V(,I11:L14,3,1)
I27:N30I27=APP2V(B13:G14,C27:F30,3,1)
S27:V30S27=APP2V({2,2},I11:L14,-3,-1)
I34:N38I34=APP2V(B13:G14,C34:F37,-3,2)
S34:W38S34=APP2V(I11:L14,"T",,-1)
S41:W45S41=APP2V("T",I11:L14,,1)
I42:P45I42=APP2V(B13:G14,C42:F45,-2,-2)
Dynamic array formulas.
 
Upvote 0
LAMBDA 1.1.1.xlsx
ABCDEFGHIJKLMNOPQR
1Arrays construction
2
3ab1b2b3b4
41234ABEFiJMN
55678CDGHKLOP
69101112
713141516
8
9=AHINSERT(APP2V(APP2H(F4:G5,I4:J5),APP2H(L4:M5,O4:P5)),A4#,2)
10AB1234EF
11CD5678GH
12other functionsiJ9101112MN
13APP2HKL13141516OP
14AHINSERT
15
APP2V post 2
Cell Formulas
RangeFormula
A4:D7A4=SEQUENCE(4,4)
E9E9=FORMULATEXT(G10)
G10:N13G10=AHINSERT(APP2V(APP2H(F4:G5,I4:J5),APP2H(L4:M5,O4:P5)),A4#,2)
Dynamic array formulas.
 
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
Excel Formula:
=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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1Task: If your routine requires appending n arrays, let's say 5 arrays, vertically
2step 1: call APPNHV(5,"v")
3step 2: copy the cell and paste it as value to any other cell
4step 3: define the new APP5V
5Note: for simplicity did not include row/column index or offset arguments found on APP2H and APP2V
6=APPNHV(5,"v")
71.) 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))))))
82.) copy B7 and paste as value
9APP5V=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))))))
103.) define APP5V in the name manager
11Important note:
12Due to how new function is created , the arguments after second array are optional ,
13we can use the same created function for appending any nr. of arrays 2<nr<=n ,
14simply by omitting arguments
15Arrays can be anywere on the woorkbook
16
17a111=APP5V(B17:C18,B20:D21,B23:B24,B26:E28,B30:C30)
181111
1911ignore 3rd argument
20a2222222=APP5V(B17:C18,B20:D21,,B26:E28,B30:C30)
21222211
22311arg. omitted,only 3 arrays, dif.order
23a333222=APP5V(B26:E28,B17:C18,B20:D21)
2434444224444
254444444444
26a4444444444444444
2744455444411
2844445511
29222
30a55522
31
APPNHV V post
Cell Formulas
RangeFormula
B6B6=FORMULATEXT(B7)
B7B7=APPNHV(5,"v")
F17,R23,L20F17=FORMULATEXT(G18)
G18:J27G18=APP5V(B17:C18,B20:D21,B23:B24,B26:E28,B30:C30)
M21:P28M21=APP5V(B17:C18,B20:D21,,B26:E28,B30:C30)
S24:V30S24=APP5V(B26:E28,B17:C18,B20:D21)
Dynamic array formulas.
 

Forum statistics

Threads
1,216,505
Messages
6,131,026
Members
449,616
Latest member
PsychoCube

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top