AHINSERT

AHINSERT(a,b,[cl],[o],[ci])
a
array
b
array to be inserted
[cl]
after this column nr of a, the insertion will take place
[o]
offset argument o=0 or omitted, a and b leveled, o>0, b lowered o rows, o<0, b lifted abs(o) rows
[ci]
clm index argument of 2nd part of a (same as in APP2H)

AHINSERT Inserts an array into other array. AVINSERT will follow, same post

Xlambda

Well-known Member
Joined
Mar 8, 2021
Messages
837
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAM
1AHINSERT 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
2cl: after column cl, insertion will take placeo: offset arg.ci: colmn index only for 2nd part of array a,a2b
3if cl<1,array b to the left of a like APP2H(b,a)o=0 or omitted leveled with asame column index functionality of APP2HAPP2HA1B1
4if cl>=clms(a) array b to the right of a, like APP2H(a,b)o>0, array b,lowered o rowsci>0 all clms >=ci of a2 are selectedA2B2
5o<0, array b lifted abs(o) rowsci<0 all clms of a2 <=abs(i) are selectedA3B3
6A4B4
7
8cl,2,o and ci omittedcl out of range cl<1 or cl>=clms(a)
9cl,2=AHINSERT(A10#,AG3:AH6,2)cl omitted or c<1all arg. omitted, (b to the left of a)
10123456712A1B134567a=AHINSERT(T11#,AG3:AH6)
1189101112131489A2B210111213141234567A1B11234567
12151617181920211516A3B31718192021891011121314A2B2891011121314
13a1a2A4B415161718192021A3B315161718192021
14bA4B4
15cl,3,o,-2
16cl,3=AHINSERT(A17#,AG3:AH6,3,-2)ci,4cl<1cl,-2,o,1,ci,4
171234567 A1B1a=AHINSERT(T18#,AG3:AH6,-2,1,4)
18891011121314A2B21234567o,1 4567
1915161718192021o,-2123A3B34567891011121314A1B111121314
20a1a28910A4B41112131415161718192021A2B218192021
2115161718192021A3B3
22A4B4
23cl,6,o,1
24cl,6=AHINSERT(A25#,AG3:AH6,6,1)ci,-4cl,,o,-2,ci,-4
251234567o,11234567a=AHINSERT(T26#,AG3:AH6,,-2,-4)
268910111213148910111213A1B1141234567A1B1
2715161718192021151617181920A2B221891011121314A2B2
28a1a2A3B315161718192021o,-2A3B31234
29A4B4A4B4891011
3015161718
31ci,3cl,1,o,,ci,3
32cl,1o,,=AHINSERT(A33#,AG3:AH6,1,,3)cl>=clms(a)cl=clms(a)cl,7,o,1.,ci,omt. B to the right of a)
3312345671A1B14567a=AHINSERT(T34#,AG3:AH6,7,1)
348910111213148A2B2111213141234567o,11234567
351516171819202115A3B318192021891011121314891011121314A1B1
36a1a2A4B41516171819202115161718192021A2B2
37A3B3
38A4B4
39ci,-3cl,2,o,-1,ci,-3
40cl,2=AHINSERT(A41#,AG3:AH6,2,-1,-3)cl>clms(a)cl,10,o,-1,ci,4 (ci no effect whatever value)
411234567 A1B1a=AHINSERT(T42#,AG3:AH6,10,-1,4)
42891011121314o,-112A2B23451234567 A1B1
431516171819202189A3B3101112891011121314o,-11234567A2B2
44a1a21516A4B417181915161718192021891011121314A3B3
4515161718192021A4B4
46ci is dedicated only for indexing the
472nd side of "a" area ,a2,and in this case
48we don't have one, whole a is present
49
50
AHINSERT post
Cell Formulas
RangeFormula
J9,AC41,J40,AC33,J32,AC25,J24,AC17,J16,AC10J9=FORMULATEXT(J10)
A10:G12,T42:Z44,A41:G43,T34:Z36,A33:G35,T26:Z28,A25:G27,T18:Z20,A17:G19,T11:Z13A10=SEQUENCE(3,7)
J10:R13J10=AHINSERT(A10#,AG3:AH6,2)
AC11:AK14AC11=AHINSERT(T11#,AG3:AH6)
J17:R21J17=AHINSERT(A17#,AG3:AH6,3,-2)
AC18:AH22AC18=AHINSERT(T18#,AG3:AH6,-2,1,4)
J25:R29J25=AHINSERT(A25#,AG3:AH6,6,1)
AC26:AH30AC26=AHINSERT(T26#,AG3:AH6,,-2,-4)
J33:P36J33=AHINSERT(A33#,AG3:AH6,1,,3)
AC34:AK38AC34=AHINSERT(T34#,AG3:AH6,7,1)
J41:P44J41=AHINSERT(A41#,AG3:AH6,2,-1,-3)
AC42:AK45AC42=AHINSERT(T42#,AG3:AH6,10,-1,4)
Dynamic array formulas.
 
Upvote 0
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAG
1AVINSERT 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
2rw: after row rw, insertion will take placeo: offset arg.ri: row index only for 2nd part of array a,a2 (omitted,all a2)
3if rw<1,array "b" on top of "a" like APP2V(b,a)o=0 or omitted leveled with asame row index functionality of APP2VAPP2V
4if rw>=rows(a) array "a" on top of "b", like APP2V(a,b)o>0, array b,to the right "o" clmsri>0 all rws >=ri of a2 are selected
5o<0, array b to the left abs(o) clmsri<0 all rws of a2 <=abs(ri) are selected
6bA1A2A3A4
7B1B2B3B4rw,2,ri,omittedrw,2,o,2rw,2,o,-1
8o,2o,-1
9a1181518151815 1815
102916rw,2291629162916
11a231017A1A2A3A4A1A2A3A4A1A2A3A4
1241118B1B2B3B4B1B2B3B4B1B2B3B4
1351219310173101731017
1461320411184111841118
1571421512195121951219
16613206132061320
17714217142171421
18=AVINSERT(B9:D15,B6:E7,2)=AVINSERT(B9:D15,B6:E7,2,2)=AVINSERT(B9:D15,B6:E7,2,-1)
19
20rw,1,o,1,ri,3rw,2,o,-1,ri,-4
21o,1o,-1
22a11815rw,11815a11815 1815
23a22916A1A2A3A42916rw,22916
2431017B1B2B3B4a231017A1A2A3A4
2541118ri,34111841118B1B2B3B4
2651219512195121931017
27613206132061320ri,-441118
2871421714217142151219
29=AVINSERT(B22:D28,B6:E7,1,1,3)61320
30=AVINSERT(B22:D28,B6:E7,2,-1,-4)
31
32all arg. omittedrw<0rw,-2,o,1,ri,4
33o,1
34a1815A1A2A3A4a1815 A1A2A3A4
352916B1B2B3B42916B1B2B3B4
363101718153101741118
3741118291641118ri,451219
3851219310175121961320
3961320411186132071421
40714215121971421=AVINSERT(B22:D28,B6:E7,-2,1,4)
4161320
4271421rw,-1,o,-1,ri,-4
43=AVINSERT(B22:D28,B6:E7)o,-1
44a1815A1A2A3A4
45rw>=rows(a)2916B1B2B3B4
46rw,7,o,1,ri,2 (ri,no effect)310171815
47o,141118ri,-42916
4818155121931017
4929166132041118
503101771421=AVINSERT(P22:R28,B6:E7,-1,-1,-4)
5141118
5251219
5361320
5471421
55A1A2A3A4
56B1B2B3B4
57=AVINSERT(B34:D40,B6:E7,7,1,2)
58ri is dedicated only for indexing the
592nd part of "a" area ,a2,and in this case
60we don't have one, whole "a" is present
61
AVINSERT post
Cell Formulas
RangeFormula
H9:K17H9=AVINSERT(B9:D15,B6:E7,2)
N9:S17N9=AVINSERT(B9:D15,B6:E7,2,2)
V9:Y17V9=AVINSERT(B9:D15,B6:E7,2,-1)
H18,N18,C57,V18H18=FORMULATEXT(H9)
H22:L28H22=AVINSERT(B22:D28,B6:E7,1,1,3)
V22:Y29V22=AVINSERT(B22:D28,B6:E7,2,-1,-4)
H29H29=FORMULATEXT(H22)
V30V30=FORMULATEXT(V22)
G34:J42G34=AVINSERT(B22:D28,B6:E7)
S34:W39S34=AVINSERT(B22:D28,B6:E7,-2,1,4)
S40,S50S40=FORMULATEXT(S34)
E43E43=FORMULATEXT(G34)
S44:V49S44=AVINSERT(P22:R28,B6:E7,-1,-1,-4)
C48:G56C48=AVINSERT(B34:D40,B6:E7,7,1,2)
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