INSRWS

INSRWS(a,[r],[g])
a
array
[r]
row's group size, if omitted r=1
[g]
gap size, if omitted g=1, if<0 gap inserted also before 1st row

Inserts one or more blank rows

Xlambda

Well-known Member
Joined
Mar 8, 2021
Messages
860
Office Version
  1. 365
Platform
  1. Windows
INSRWS Inserts blank Rows. Calls ZINS
- can insert 1 or more blank rows, called "gap", after each group of r rows. Gap size argument g
- if g<0 a nr. of abs(g) blank rows is also inserted before 1st row
Note: Could have embedded ZINS function with INSRWS but can be a useful function tool to solve other types of tasks, like inserting text patterns.
Excel Formula:
=LAMBDA(a, [r], [g],
    LET(
        x, MAX(1, r),
        y, IF(g, ABS(g), 1),
        q, ZINS(ROWS(a), x, y),
        s, IF(g < 0, VSTACK(SEQUENCE(y) ^ 0 - 1, q), q),
        b, INDEX(IF(a = "", "", a), s, SEQUENCE(, COLUMNS(a))),
        IF(s, b, "")
    )
)
ZINS(n,x,g) Zeros Insert, Inserts 1 or more zeros to a sequence of n elements
n: nr. of elements
x: element's group size
g: 0's group size
Excel Formula:
=LAMBDA(n, x, g,
    LET(
        d, IF(SEQUENCE(x) < x, ",", "," & REPT("0,", g)),
        --TEXTSPLIT(TEXTJOIN(d, , SEQUENCE(n)), , ",")
    )
)
Book3
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAG
1
2r,g,omit.r,2, g,omit.r,omit. ,g,2r,3,g,2r,5,g,3r,omit. ,g,-1r,4,g,-3
3a=INSRWS(B4#)=INSRWS(B4#,2)=INSRWS(B4#,,2)=INSRWS(B4#,3,2)=INSRWS(B4#,5,3)=INSRWS(B4#,,-1)=INSRWS(B4#,4,-3)
4123123123123123123  
5456456456456123
6789456789789
7101112789456101112456123
8131415789101112131415456
9161718101112789789
10192021101112131415789131415101112
11222324161718161718101112
12252627131415161718
13282930192021101112192021131415
14313233161718222324192021222324131415
15343536222324252627161718161718
16373839192021252627131415252627282930192021
17404142282930192021222324
18434445222324
19464748313233161718282930222324
20495051252627343536313233313233
21343536343536252627252627
22282930373839192021373839282930
23404142404142282930313233
24313233373839434445343536
25434445222324404142313233
26343536464748434445
27343536
28373839495051252627464748373839
29464748495051373839404142
30404142495051434445
31282930404142464748
32434445
33434445
34464748313233
35464748495051
36495051
37343536495051
38
39
40373839
41
42
43404142
44
45
46434445
47
48
49464748
50
51
52495051
53
Sheet1
Cell Formulas
RangeFormula
F3,J3,N3,R3,V3,Z3,AD3F3=FORMULATEXT(F4)
B4:D20B4=SEQUENCE(17,3)
F4:H36F4=INSRWS(B4#)
J4:L28J4=INSRWS(B4#,2)
N4:P52N4=INSRWS(B4#,,2)
R4:T30R4=INSRWS(B4#,3,2)
V4:X29V4=INSRWS(B4#,5,3)
Z4:AB37Z4=INSRWS(B4#,,-1)
AD4:AF35AD4=INSRWS(B4#,4,-3)
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B4:AF53Expression=B4<>""textNO
B4:AF52Expression="b4<>"""""textNO
B4:AF52Expression="B4<>"""""textNO
 
Upvote 0
Book3
ABCDEFGHIJKLMN
1ZINS Concept
2delimiter pattern3. j=textjoin(d,,q)
3for TEXTJOIN=TEXTJOIN(F13#,,C6#)
4q=seq(n)1. s=seq(x)1,2,3,0,0,4,5,6,0,0,7,8,9,0,0,10,11,12,0,0,13
5nr. elements (n)=SEQUENCE(A6)=SEQUENCE(A9)
613114. t=--textsplit(j,,",")n,13,x,3,g,2n,13,x,4,g,3
722=--TEXTSPLIT(H4,,",")=ZINS(13,3,2)=ZINS(13,4,3)
8elm's group size (x)33111
934222
105333
110's group size (g)62.d=if(s<x,",",","&rept("0,",g))004
1227=IF(F6#<A9,",",","&REPT("0,",A12))000
138,440
149,550
1510,0,0,665
1611006
1712007
1813778
19880
20990
21000
22009
23101010
24111111
25121212
26000
27000
2813130
2913
30
Sheet2
Cell Formulas
RangeFormula
H3,K7,M7,H7,F5,C5H3=FORMULATEXT(H4)
H4H4=TEXTJOIN(F13#,,C6#)
C6:C18C6=SEQUENCE(A6)
F6:F8F6=SEQUENCE(A9)
H8:H28H8=--TEXTSPLIT(H4,,",")
K8:K28K8=ZINS(13,3,2)
M8:M29M8=ZINS(13,4,3)
E12E12=FORMULATEXT(F13)
F13:F15F13=IF(F6#<A9,",",","&REPT("0,",A12))
Dynamic array formulas.
 
Book3
ABCDEFGHIJKLMNOP
1INSRWS Concept
2Note: when an argument is omitted, value asigned acts like a blank, a 0 or "" on the same time
3Knowing this, on most cases is needless to use ISOMITTED
4 =LAMBDA(a,[r],[g], LET( x, MAX(1, r), y, IF(g, ABS(g), 1), q, ZINS(ROWS(a), x, y), s, IF(g < 0, VSTACK(SEQUENCE(y) ^ 0 - 1, q), q), b, INDEX(IF(a = "", "", a), s, SEQUENCE(, COLUMNS(a))), IF(s, b, "") ) )
5
6 <= if r,omitted or 0, or <0 x=1, otherwise x=r
7 <= if g, omitted or 0, y=1, if<0 y=abs(g), if>0 y=g
8 <= ZINS creates index pattern, 0's for gaps location
9 <= only if g<0, stacks abs(g) nr. of 0's to the top of the index patten
10 <= index and returning empty strings and not 0's if initial array has blanks
11 <= placing blank rows that corespond to 0's distribution
12
13
14
15=INSRWS(E16:G27,3,2)=INSRWS(E16:G27,3,-2)
16blanksA03A03 
17empty strings456456
180's 99A03
1910110456
201314159
21161810110
22192021131415
23 23161810110
24252627131415
25029301618
26C32B192021
2734ABC3623
28252627192021
2923
30252627
3102930
32C32B
3334ABC3602930
34C32B
3534ABC36
36
Sheet3
Cell Formulas
RangeFormula
I15,M15I15=FORMULATEXT(I16)
I16:K33I16=INSRWS(E16:G27,3,2)
M16:O35M16=INSRWS(E16:G27,3,-2)
F18,E23F18=""
Dynamic array formulas.
 
ADVINS Advance Insert, inserts blank rows/columns, can add row/column headers, Calls INSRWS that calls ZINS. 100% cosmetics function, double transpose and headers stacking versatility.
Note: Initially chose as function name INSERT (identical to the one beneath). Function accepted by Name Manager as any function. When called, an error message pops: "That function isn't valid". Looks like a new built-in function is coming, and probably will be a text function. Will see.
ADVINS(ar,[rc],[g],[h])
ar: array
[rc]: rows/clms group size, orientation given by its sign
rc<0, column orientation, rc>0 rows orientation, if omitted rc=1
[g]: gap size, if omitted g=1, if<0 gap inserted also before 1st row or 1st clm
[h]:row/column headers array, if omitted no stacking
Excel Formula:
=LAMBDA(ar, [rc], [g], [h],
    LET(
        a, IF(ar = "", "", ar),
        x, IF(rc, ABS(rc), 1),
        f, rc < 0,
        b, IF(f, TRANSPOSE(a), a),
        c, INSRWS(b, x, g),
        d, IF(f, TRANSPOSE(c), c),
        IF(AND(h = ""), d, IF(f, HSTACK(h, d), VSTACK(h, d)))
    )
)
Book3 (version 1).xlsb.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKAL
1
2rc>0, rows orientation
3hABCD
4=ADVINS(B5:E16)=ADVINS(B5:E16,,-1)=ADVINS(B5:E16,,,B3:E3)=ADVINS(B5:E16,,-1,B3:E3)=ADVINS(B5:E16,3,2,B3:E3)=ADVINS(B5:E16,3,-2,B3:E3)
5ar12341234 ABCDABCDABCDABCD
65678123412341234
79101112567812345678
8131415165678567891011121234
917181920910111256785678
1021222324910111291011129101112
112526272813141516910111213141516
1229303132131415161314151617181920
133334353617181920131415162122232413141516
1437383940171819201718192017181920
1541424344212223241718192021222324
1645464748212223242122232425262728
17252627282122232429303132
1825262728252627283334353625262728
19293031322526272829303132
20293031322930313233343536
21333435362930313237383940
22333435363334353641424344
2337383940333435364546474837383940
24373839403738394041424344
25414243443738394045464748
264142434441424344
274546474841424344
284546474845464748
2945464748
30
Sheet4
Cell Formulas
RangeFormula
G4,L4,Q4,V4,AA4,AF4G4=FORMULATEXT(G5)
B5:E16B5=SEQUENCE(12,4)
G5:J27G5=ADVINS(B5:E16)
L5:O28L5=ADVINS(B5:E16,,-1)
Q5:T28Q5=ADVINS(B5:E16,,,B3:E3)
V5:Y29V5=ADVINS(B5:E16,,-1,B3:E3)
AA5:AD23AA5=ADVINS(B5:E16,3,2,B3:E3)
AF5:AI25AF5=ADVINS(B5:E16,3,-2,B3:E3)
Dynamic array formulas.
 
Book3 (version 1).xlsb.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAM
1
2rc<0 column orientation
3har=ADVINS(D4#,-3,-2,B4:B10)
4A123456789101112A123456789101112
5B131415161718192021222324B131415161718192021222324
6C252627282930313233343536C252627282930313233343536
7D373839404142434445464748D373839404142434445464748
8E495051525354555657585960E495051525354555657585960
9F616263646566676869707172F616263646566676869707172
10G737475767778798081828384G737475767778798081828384
11
12=ADVINS(D4#,-1)
13123456789101112
14131415161718192021222324
15252627282930313233343536
16373839404142434445464748
17495051525354555657585960
18616263646566676869707172
19737475767778798081828384
20
21=ADVINS(D4#,-1,-1)
22 123456789101112
23131415161718192021222324
24252627282930313233343536
25373839404142434445464748
26495051525354555657585960
27616263646566676869707172
28737475767778798081828384
29
30=ADVINS(D4#,-4,3,B4:B10)
31A123456789101112
32B131415161718192021222324
33C252627282930313233343536
34D373839404142434445464748
35E495051525354555657585960
36F616263646566676869707172
37G737475767778798081828384
38
39=ADVINS(D4#,-4,-3,B4:B10)
40A123456789101112
41B131415161718192021222324
42C252627282930313233343536
43D373839404142434445464748
44E495051525354555657585960
45F616263646566676869707172
46G737475767778798081828384
47
Sheet5
Cell Formulas
RangeFormula
R3,D39,D30,D21,D12R3=FORMULATEXT(R4)
D4:O10D4=SEQUENCE(7,12)
R4:AL10R4=ADVINS(D4#,-3,-2,B4:B10)
D13:Z19D13=ADVINS(D4#,-1)
D22:AA28D22=ADVINS(D4#,-1,-1)
D31:V37D31=ADVINS(D4#,-4,3,B4:B10)
D40:Y46D40=ADVINS(D4#,-4,-3,B4:B10)
Dynamic array formulas.
 
Introducing ARRINS Arrays Insert, "intercalates" rows or columns of 2 arrays. Examples will speak for themselves.
Arrays should be proportionate by nr. of elements and to share one dimension. Inserting parameters are calculated implicitly.
ARRINS(ar,br,[rc],[h]) Calls ADVINS
ar: array to receive the insert
br: array to be inserted
[rc]: rows/columns orientation, if omitted or 0, by rows, if 1 by columns
[h]: headers array
Excel Formula:
=LAMBDA(ar, br, [rc], [h],
    LET(
        a, IF(ar = "", "", ar),
        b, IF(br = "", "", br),
        o, IF(rc, -1, 1),
        k, o * COUNTA(a) / COUNTA(b),
        x, ADVINS(a, k),
        v, IFNA(IF(rc, HSTACK(x, ""), VSTACK(x, "")), ""),
        y, ADVINS(b, o * 1, -ABS(k)),
        z, IF(v = "", y, v),
        IF(AND(h = ""), z, IF(rc, HSTACK(h, z), VSTACK(h, z)))
    )
)
Book3 (version 1).xlsb.xlsx
ABCDEFGHIJKLMNOPQRSTU
1ARRINS by rows
2Wkdywk nr.Val
312=ARRINS(B4:D10,F4:H10)=ARRINS(J4#,F13:H19)=ARRINS(N4#,B23#,,B2:D2)
4Monw123Monw231Monw123Monw123Wkdywk nr.Val
5Tuew145Tuew257Monw231Monw231Monw123
6Wedw142Wedw264Tuew145Monw323Monw231
7Thuw176Thuw246Tuew257Tuew145Monw323
8Friw156Friw280Wedw142Tuew257Tot77
9Satw181Satw233Wedw264Tuew345Tuew145
10Sunw174Sunw237Thuw176Wedw142Tuew257
11Thuw246Wedw264Tuew345
123Friw156Wedw342Tot147
13Monw323Friw280Thuw176Wedw142
14Tuew345Satw181Thuw246Wedw264
15Wedw342Satw233Thuw376Wedw342
16Thuw376Sunw174Friw156Tot148
17Friw356Sunw237Friw280Thuw176
18Satw381Friw356Thuw246
19Sunw374Satw181Thuw376
20Satw233Tot198
21Totals/array's rowsSatw381Friw156
22=IFNA(HSTACK(REPT("Tot",SEQUENCE(7)^0),"",D4:D10+H4:H10+H13:H19),"")Sunw174Friw280
23Tot77Sunw237Friw356
24Tot147Sunw374Tot192
25Tot148Satw181
26Tot198Satw233
27Tot192Satw381
28Tot195Tot195
29Tot185Sunw174
30Sunw237
31Sunw374
32Tot185
33
Sheet6
Cell Formulas
RangeFormula
J3,N3,B22,R3J3=FORMULATEXT(J4)
J4:L17J4=ARRINS(B4:D10,F4:H10)
N4:P24N4=ARRINS(J4#,F13:H19)
R4:T32R4=ARRINS(N4#,B23#,,B2:D2)
B23:D29B23=IFNA(HSTACK(REPT("Tot",SEQUENCE(7)^0),"",D4:D10+H4:H10+H13:H19),"")
Dynamic array formulas.
 
Book3.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXY
1ARRINS by columns
2123
3A-10-8-6-41471010131619
4B-20241316192222252831
5C6810122528313434374043
6D141618203740434646495255
7E222426284952555858616467
8F303234366164677070737679
9G384042447376798282858891
10
11Calculating totals byrow for each array using iterations and second syntax of INDEX with area_num, single cell formula.
12=DROP(REDUCE(0,SEQUENCE(3),LAMBDA(v,i,HSTACK(v,BYROW(INDEX((C3#,G3#,K3#),SEQUENCE(7),SEQUENCE(,4),i),LAMBDA(x,SUM(x)))))),,1)
13-282258
14470106
1536118154
1668166202
17100214250
18132262298
19164310346
20
21=ARRINS(C3:N9,C13#,1,B3:B9)
22A-10-8-6-4-2814710221013161958
23B-20244131619227022252831106
24C681012362528313411834374043154
25D14161820683740434616646495255202
26E222426281004952555821458616467250
27F303234361326164677026270737679298
28G384042441647376798231082858891346
29
Sheet7
Cell Formulas
RangeFormula
C3:F9C3=SEQUENCE(7,4,-10,2)
G3:J9G3=SEQUENCE(7,4,,3)
K3:N9K3=SEQUENCE(7,4,10,3)
B12B12=FORMULATEXT(C13)
C13:E19C13=DROP(REDUCE(0,SEQUENCE(3),LAMBDA(v,i,HSTACK(v,BYROW(INDEX((C3#,G3#,K3#),SEQUENCE(7),SEQUENCE(,4),i),LAMBDA(x,SUM(x)))))),,1)
B21B21=FORMULATEXT(B22)
B22:Q28B22=ARRINS(C3:N9,C13#,1,B3:B9)
Dynamic array formulas.
 
Forgot to post alternative of ZINS function, identical functionality with the previous one, based on same concept I posted here on Suat's thread that inspired all this: INSERTBLANKAT
For large arrays, this version is better.
Excel Formula:
=LAMBDA(n, x, g, LET(t, TOCOL(IFNA(EXPAND(WRAPROWS(SEQUENCE(n), x), , g + x), 0)), TAKE(t, XMATCH(n, t))))
 
If arrays on same spreadsheet can be called by an iterative formula using INDEX area_num syntax, this cannot be done if arrays are on different spreadsheets. For that here we have
NARRINS N Arrays Insert function that can insert a sequence of min 2, max 5 arrays that can be located anywhere.
NARRINS(rc,h,a,b,[c],[d],[e])
rs: row/clm orientation
h: headers array
a,b,c,d,e: arrays to be inserted, when c,d,or e are omitted, function knows to calculate the nr. of arrays involved.
Excel Formula:
=LAMBDA(rc, h, a, b, [c], [d], [e],
    LET(
        x, LAMBDA(i, SWITCH(i, 1, a, 2, b, 3, c, 4, d, 5, e)),
        t, VSTACK(TYPE(c), TYPE(d), TYPE(e)),
        m, IFNA(XMATCH(1, t), 4) + 1,
        REDUCE(
            a,
            SEQUENCE(m - 1, , 2),
            LAMBDA(v, i, ARRINS(v, x(i), rc, IF(i = m, h, "")))
        )
    )
)
Book3.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAAB
1NARRINS by rows. All formulas single cell based only on initial arrays. 1,2,3,4, or 5Totals/array's rows
2Wkdywk nr.Val=IFNA(HSTACK(REPT("Tot",SEQUENCE(7)^0),"",D4:D10+H4:H10+L4:L10+P4:P10),"")
312345
4Monw123Monw231Monw323Monw460Tot137
5Tuew145Tuew257Tuew345Tuew470Tot217
6Wedw142Wedw264Wedw342Wedw433Tot181
7Thuw176Thuw246Thuw376Thuw444Tot242
8Friw156Friw280Friw356Friw478Tot270
9Satw181Satw233Satw381Satw412Tot207
10Sunw174Sunw237Sunw374Sunw443Tot228
11
12=NARRINS(,B2:D2,B4:D10,F4:H10,J4:L10)
13↓↓↓=NARRINS(,B2:D2,B4:D10,F4:H10,J4:L10,N4:P10)
14=NARRINS(,B2:D2,B4:D10,F4:H10)↓↓↓↓↓↓=NARRINS(,B2:D2,B4:D10,F4:H10,J4:L10,N4:P10,R4#)
15=NARRINS(,,B4:D10,F4:H10)Wkdywk nr.ValWkdywk nr.ValWkdywk nr.ValWkdywk nr.Val
16Monw123Monw123Monw123Monw123Monw123
17Monw231Monw231Monw231Monw231Monw231
18Tuew145Tuew145Monw323Monw323Monw323
19Tuew257Tuew257Tuew145Monw460Monw460
20Wedw142Wedw142Tuew257Tuew145Tot137
21Wedw264Wedw264Tuew345Tuew257Tuew145
22Thuw176Thuw176Wedw142Tuew345Tuew257
23Thuw246Thuw246Wedw264Tuew470Tuew345
24Friw156Friw156Wedw342Wedw142Tuew470
25Friw280Friw280Thuw176Wedw264Tot217
26Satw181Satw181Thuw246Wedw342Wedw142
27Satw233Satw233Thuw376Wedw433Wedw264
28Sunw174Sunw174Friw156Thuw176Wedw342
29Sunw237Sunw237Friw280Thuw246Wedw433
30Friw356Thuw376Tot181
31Satw181Thuw444Thuw176
32Satw233Friw156Thuw246
33Satw381Friw280Thuw376
34Sunw174Friw356Thuw444
35Sunw237Friw478Tot242
36Sunw374Satw181Friw156
37Satw233Friw280
38Satw381Friw356
39Satw412Friw478
40Sunw174Tot270
41Sunw237Satw181
42Sunw374Satw233
43Sunw443Satw381
44Satw412
45Tot207
46Sunw174
47Sunw237
48Sunw374
49Sunw443
50Tot228
51
Sheet8
Cell Formulas
RangeFormula
R2,N13R2=FORMULATEXT(R4)
R4:T10R4=IFNA(HSTACK(REPT("Tot",SEQUENCE(7)^0),"",D4:D10+H4:H10+L4:L10+P4:P10),"")
J12J12=FORMULATEXT(J15)
E14E14=FORMULATEXT(F15)
R14,B15R14=FORMULATEXT(R15)
F15:H29F15=NARRINS(,B2:D2,B4:D10,F4:H10)
J15:L36J15=NARRINS(,B2:D2,B4:D10,F4:H10,J4:L10)
N15:P43N15=NARRINS(,B2:D2,B4:D10,F4:H10,J4:L10,N4:P10)
R15:T50R15=NARRINS(,B2:D2,B4:D10,F4:H10,J4:L10,N4:P10,R4#)
B16:D29B16=NARRINS(,,B4:D10,F4:H10)
Dynamic array formulas.
 
Book3.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXY
1Concept NARRINS. Calls ARRINS
2
3 =LAMBDA(rc,h,a,b,[c],[d],[e], LET( x, LAMBDA(i, SWITCH(i, 1, a, 2, b, 3, c, 4, d, 5, e)), t, VSTACK(TYPE(c), TYPE(d), TYPE(e)), m, IFNA(XMATCH(1, t), 4) + 1, REDUCE( a, SEQUENCE(m - 1, , 2), LAMBDA(v,i, ARRINS(v, x(i), rc, IF(i = m, h, ""))) ) ) )
4
5 <= embedded lambda x(i) to index the arrays
6 <=TYPE array "t", if c,d,e are arrays => 64;64;64, if "e" omitted=> 64;64;1
7 <= based on what "t" returns we can calculate how many arrays were inputted "m"
8 <= iterative formula loops from "i"=2 to m, because accumulator's "v" first value=first array "a"
9
10 <= calls ARRINS(accumulator,x(i),rc,......),
11and only when reaches last index nr "m", "h" array is stacked or not
12depending if "h" headers argument is omitted or not
13
14
15
Sheet9
 

Forum statistics

Threads
1,223,575
Messages
6,173,148
Members
452,502
Latest member
PQCurious

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