APP2V

Xlambda · Aug 8, 2021

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

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

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

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 · Aug 11, 2021

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

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 · Aug 22, 2021

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

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

=APP5V(B17:C18,B20:D21,B23:B24,B26:E28,B30:C30)

18

1

19

1

ignore 3rd argument

20

a2

2

=APP5V(B17:C18,B20:D21,,B26:E28,B30:C30)

21

2

1

22

3

1

arg. omitted,only 3 arrays, dif.order

23

a3

3

2

=APP5V(B26:E28,B17:C18,B20:D21)

24

3

4

2

4

25

4

26

a4

4

27

4

5

4

1

28

4

5

1

29

2

30

a5

5

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.

APP2V

Xlambda

Well-known Member

Xlambda

Well-known Member

Xlambda

Well-known Member

Forum statistics

Share this page

APP2V

Xlambda

Well-known Member

Xlambda

Well-known Member

Xlambda

Well-known Member

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock