AHINSERT

Xlambda · Aug 11, 2021

AHINSERT Array Horizontal Insert . Inserts an array into other array that splits in 2 parts. Calls APP2H , APP2V
AVINSERT will follow here as well, asap.

Excel Formula:

=LAMBDA(a,b,[cl],[o],[ci],
    LET(r,ROWS(a),c,COLUMNS(a),s,SEQUENCE(,c),
      IFS(cl<1,APP2H(b,a,ci,-o),cl>=c,APP2H(a,b,,o),
        TRUE,APP2H(APP2H(FILTER(a,s<=cl),b,,o),FILTER(a,s>cl),ci,IF(o<0,-o,0)))
    )
)

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

AG

AH

AI

AJ

AK

AL

AM

1

AHINSERT splits array in 2 parts (a1,a2) after a column numbers,inserts other array b at an offset level,column index options for second part a2

2

cl: after column cl, insertion will take place

o: offset arg.

ci: colmn index only for 2nd part of array a,a2

b

3

if cl<1,array b to the left of a like APP2H(b,a)

o=0 or omitted leveled with a

same column index functionality of APP2H

APP2H

A1

B1

4

if cl>=clms(a) array b to the right of a, like APP2H(a,b)

o>0, array b,lowered o rows

ci>0 all clms >=ci of a2 are selected

A2

B2

5

o<0, array b lifted abs(o) rows

ci<0 all clms of a2 <=abs(i) are selected

A3

B3

6

A4

B4

7

8

cl,2,o and ci omitted

cl out of range cl<1 or cl>=clms(a)

9

cl,2

➡

=AHINSERT(A10#,AG3:AH6,2)

cl omitted or c<1

all arg. omitted, (b to the left of a)

10

1

2

3

4

5

6

7

1

2

A1

B1

3

4

5

6

7

a

=AHINSERT(T11#,AG3:AH6)

11

8

9

10

11

12

13

14

8

9

A2

B2

10

11

12

13

14

1

2

3

4

5

6

7

A1

B1

1

2

3

4

5

6

7

12

15

16

17

18

19

20

21

15

16

A3

B3

17

18

19

20

21

8

9

10

11

12

13

14

A2

B2

8

9

10

11

12

13

14

13

a1

a2

A4

B4

15

16

17

18

19

20

21

A3

B3

15

16

17

18

19

20

21

14

b

A4

B4

15

cl,3,o,-2

16

cl,3

➡

=AHINSERT(A17#,AG3:AH6,3,-2)

ci,4

cl<1

cl,-2,o,1,ci,4

17

1

2

3

4

5

6

7

A1

B1

a

➡

=AHINSERT(T18#,AG3:AH6,-2,1,4)

18

8

9

10

11

12

13

14

⬆

A2

B2

1

2

3

4

5

6

7

o,1

4

5

6

7

19

15

16

17

18

19

20

21

o,-2

1

2

3

A3

B3

4

5

6

7

8

9

10

11

12

13

14

⬇

A1

B1

11

12

13

14

20

a1

a2

8

9

10

A4

B4

11

12

13

14

15

16

17

18

19

20

21

A2

B2

18

19

20

21

15

16

17

18

19

20

21

A3

B3

22

A4

B4

23

cl,6,o,1

24

cl,6

➡

=AHINSERT(A25#,AG3:AH6,6,1)

ci,-4

cl,,o,-2,ci,-4

25

1

2

3

4

5

6

7

o,1

1

2

3

4

5

6

7

a

⬅

=AHINSERT(T26#,AG3:AH6,,-2,-4)

26

8

9

10

11

12

13

14

⬇

8

9

10

11

12

13

A1

B1

14

1

2

3

4

5

6

7

A1

B1

27

15

16

17

18

19

20

21

15

16

17

18

19

20

A2

B2

21

8

9

10

11

12

13

14

⬆

A2

B2

28

a1

a2

A3

B3

15

16

17

18

19

20

21

o,-2

A3

B3

1

2

3

4

29

A4

B4

A4

B4

8

9

10

11

30

15

16

17

18

31

ci,3

cl,1,o,,ci,3

32

cl,1

➡

o,,

=AHINSERT(A33#,AG3:AH6,1,,3)

cl>=clms(a)

cl=clms(a)

cl,7,o,1.,ci,omt. B to the right of a)

33

1

2

3

4

5

6

7

⬇

1

A1

B1

4

5

6

7

a

=AHINSERT(T34#,AG3:AH6,7,1)

34

8

9

10

11

12

13

14

8

A2

B2

11

12

13

14

1

2

3

4

5

6

7

o,1

1

2

3

4

5

6

7

35

15

16

17

18

19

20

21

15

A3

B3

18

19

20

21

8

9

10

11

12

13

14

⬇

8

9

10

11

12

13

14

A1

B1

36

a1

a2

A4

B4

15

16

17

18

19

20

21

15

16

17

18

19

20

21

A2

B2

37

A3

B3

38

A4

B4

39

ci,-3

cl,2,o,-1,ci,-3

40

cl,2

➡

⬅

=AHINSERT(A41#,AG3:AH6,2,-1,-3)

cl>clms(a)

cl,10,o,-1,ci,4 (ci no effect whatever value)

41

1

2

3

4

5

6

7

⬆

A1

B1

a

=AHINSERT(T42#,AG3:AH6,10,-1,4)

42

8

9

10

11

12

13

14

o,-1

1

2

A2

B2

3

4

5

1

2

3

4

5

6

7

⬆

A1

B1

43

15

16

17

18

19

20

21

8

9

A3

B3

10

11

12

8

9

10

11

12

13

14

o,-1

1

2

3

4

5

6

7

A2

B2

44

a1

a2

15

16

A4

B4

17

18

19

15

16

17

18

19

20

21

8

9

10

11

12

13

14

A3

B3

45

15

16

17

18

19

20

21

A4

B4

46

ci is dedicated only for indexing the

47

2nd side of "a" area ,a2,and in this case

48

we don't have one, whole a is present

49

50

AHINSERT post

Cell Formulas
Range	Formula
J9,AC41,J40,AC33,J32,AC25,J24,AC17,J16,AC10	J9	=FORMULATEXT(J10)
A10:G12,T42:Z44,A41:G43,T34:Z36,A33:G35,T26:Z28,A25:G27,T18:Z20,A17:G19,T11:Z13	A10	=SEQUENCE(3,7)
J10:R13	J10	=AHINSERT(A10#,AG3:AH6,2)
AC11:AK14	AC11	=AHINSERT(T11#,AG3:AH6)
J17:R21	J17	=AHINSERT(A17#,AG3:AH6,3,-2)
AC18:AH22	AC18	=AHINSERT(T18#,AG3:AH6,-2,1,4)
J25:R29	J25	=AHINSERT(A25#,AG3:AH6,6,1)
AC26:AH30	AC26	=AHINSERT(T26#,AG3:AH6,,-2,-4)
J33:P36	J33	=AHINSERT(A33#,AG3:AH6,1,,3)
AC34:AK38	AC34	=AHINSERT(T34#,AG3:AH6,7,1)
J41:P44	J41	=AHINSERT(A41#,AG3:AH6,2,-1,-3)
AC42:AK45	AC42	=AHINSERT(T42#,AG3:AH6,10,-1,4)
Dynamic array formulas.

Xlambda · Aug 13, 2021

AVINSERT Array Vertical Insert . Inserts an array into other array that splits in 2 parts. Calls APP2V .Arguments explained on minisheet.

Excel Formula:

=LAMBDA(a,b,[rw],[o],[ri],
    LET(r,ROWS(a),s,SEQUENCE(r),
      IFS(rw<1,APP2V(b,a,ri,-o),rw>=r,APP2V(a,b,,o),
        TRUE,APP2V(APP2V(FILTER(a,s<=rw),b,,o),FILTER(a,s>rw),ri,IF(o<0,-o,0)))
    )
)

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

AG

1

AVINSERT splits array in 2 parts (a1,a2) after a row number,inserts other array b at an offset level,row index options for second part a2

2

rw: after row rw, insertion will take place

o: offset arg.

ri: row index only for 2nd part of array a,a2 (omitted,all a2)

3

if rw<1,array "b" on top of "a" like APP2V(b,a)

o=0 or omitted leveled with a

same row index functionality of APP2V

APP2V

4

if rw>=rows(a) array "a" on top of "b", like APP2V(a,b)

o>0, array b,to the right "o" clms

ri>0 all rws >=ri of a2 are selected

5

o<0, array b to the left abs(o) clms

ri<0 all rws of a2 <=abs(ri) are selected

6

b

A1

A2

A3

A4

7

B1

B2

B3

B4

rw,2,ri,omitted

rw,2,o,2

rw,2,o,-1

8

o,2

➡

⬅

o,-1

9

a1

1

8

15

1

8

15

1

8

15

1

8

15

10

2

9

16

rw,2

2

9

16

2

9

16

2

9

16

11

a2

3

10

17

⬇

A1

A2

A3

A4

A1

A2

A3

A4

A1

A2

A3

A4

12

4

11

18

B1

B2

B3

B4

B1

B2

B3

B4

B1

B2

B3

B4

13

5

12

19

3

10

17

3

10

17

3

10

17

14

6

13

20

4

11

18

4

11

18

4

11

18

15

7

14

21

5

12

19

5

12

19

5

12

19

16

6

13

20

6

13

20

6

13

20

17

7

14

21

7

14

21

7

14

21

18

=AVINSERT(B9:D15,B6:E7,2)

=AVINSERT(B9:D15,B6:E7,2,2)

=AVINSERT(B9:D15,B6:E7,2,-1)

19

20

rw,1,o,1,ri,3

rw,2,o,-1,ri,-4

21

o,1

➡

⬅

o,-1

22

a1

1

8

15

rw,1

1

8

15

a1

1

8

15

1

8

15

23

a2

2

9

16

⬇

A1

A2

A3

A4

2

9

16

rw,2

2

9

16

24

3

10

17

B1

B2

B3

B4

a2

3

10

17

⬇

A1

A2

A3

A4

25

4

11

18

⬇

ri,3

4

11

18

4

11

18

B1

B2

B3

B4

26

5

12

19

5

12

19

5

12

19

3

10

17

27

6

13

20

6

13

20

6

13

20

⬆

ri,-4

4

11

18

28

7

14

21

7

14

21

7

14

21

5

12

19

29

=AVINSERT(B22:D28,B6:E7,1,1,3)

6

13

20

30

=AVINSERT(B22:D28,B6:E7,2,-1,-4)

31

32

all arg. omitted

rw<0

rw,-2,o,1,ri,4

33

o,1

➡

34

a

1

8

15

A1

A2

A3

A4

a

1

8

15

A1

A2

A3

A4

35

2

9

16

B1

B2

B3

B4

2

9

16

B1

B2

B3

B4

36

3

10

17

1

8

15

3

10

17

4

11

18

37

4

11

18

2

9

16

4

11

18

⬇

ri,4

5

12

19

38

5

12

19

3

10

17

5

12

19

6

13

20

39

6

13

20

4

11

18

6

13

20

7

14

21

40

7

14

21

5

12

19

7

14

21

=AVINSERT(B22:D28,B6:E7,-2,1,4)

41

6

13

20

42

7

14

21

rw,-1,o,-1,ri,-4

43

=AVINSERT(B22:D28,B6:E7)

⬅

o,-1

44

a

1

8

15

A1

A2

A3

A4

45

rw>=rows(a)

2

9

16

B1

B2

B3

B4

46

rw,7,o,1,ri,2 (ri,no effect)

3

10

17

1

8

15

47

o,1

➡

4

11

18

⬆

ri,-4

2

9

16

48

1

8

15

5

12

19

3

10

17

49

2

9

16

6

13

20

4

11

18

50

3

10

17

7

14

21

=AVINSERT(P22:R28,B6:E7,-1,-1,-4)

51

4

11

18

52

5

12

19

53

6

13

20

54

7

14

21

55

A1

A2

A3

A4

56

B1

B2

B3

B4

57

=AVINSERT(B34:D40,B6:E7,7,1,2)

58

ri is dedicated only for indexing the

59

2nd part of "a" area ,a2,and in this case

60

we don't have one, whole "a" is present

61

AVINSERT post

Cell Formulas
Range	Formula
H9:K17	H9	=AVINSERT(B9:D15,B6:E7,2)
N9:S17	N9	=AVINSERT(B9:D15,B6:E7,2,2)
V9:Y17	V9	=AVINSERT(B9:D15,B6:E7,2,-1)
H18,N18,C57,V18	H18	=FORMULATEXT(H9)
H22:L28	H22	=AVINSERT(B22:D28,B6:E7,1,1,3)
V22:Y29	V22	=AVINSERT(B22:D28,B6:E7,2,-1,-4)
H29	H29	=FORMULATEXT(H22)
V30	V30	=FORMULATEXT(V22)
G34:J42	G34	=AVINSERT(B22:D28,B6:E7)
S34:W39	S34	=AVINSERT(B22:D28,B6:E7,-2,1,4)
S40,S50	S40	=FORMULATEXT(S34)
E43	E43	=FORMULATEXT(G34)
S44:V49	S44	=AVINSERT(P22:R28,B6:E7,-1,-1,-4)
C48:G56	C48	=AVINSERT(B34:D40,B6:E7,7,1,2)
Dynamic array formulas.

AHINSERT

Xlambda

Well-known Member

Xlambda

Well-known Member

Forum statistics

Share this page

AHINSERT

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