INSERTBLANKS

=INSERTBLANKS(range,integer)

array
a range of cells to insert blanks into
space_count
how many blank rows to insert between values

INSERTBLANKS will insert blank rows between the values of a given range

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,929
Office Version
  1. 365
Platform
  1. Windows
INSERTBLANKS will insert blank rows between the values of a given range.

Excel Formula:
=LAMBDA(
    array,space_count,
    LET(
        acnt,ROWS(array),
        total,acnt+((acnt-1)*space_count),
        s,(SEQUENCE(total)-1)/(space_count+1)+1,
            MAKEARRAY(
                total,
                COLUMNS(array),
                LAMBDA(
                    r,c,
                    IF(ISINT(INDEX(s,r)),
                        INDEX(array,INDEX(s,r),c),
                        ""
                    )
                )
            )
    )
)

LAMBDA
ABCDEFGHIJKLMNO
11111AEIAEI
222BFJ
333CGK
442DHLBFJ
55
6
732CGK
8
9
104DHL
11
12
1353
Sheet1
Cell Formulas
RangeFormula
A1:A5A1=SEQUENCE(5)
C1:C13C1=INSERTBLANKS(A1#,2)
E1:E3E1=SEQUENCE(3)
G1:G13G1=INSERTBLANKS(E1#,5)
M1:O10M1=INSERTBLANKS(I1:K4,2)
Dynamic array formulas.
 
Upvote 0
I saw a post some minutes ago with some function to insert nr. of blanks after a group size, but the post disappeared, is "blank". 😊
Anyhow, I have a first fast draft that does the job.
INSBNK(a,g,k) a: array , g: group size , k: nr. of blnk rows
Excel Formula:
=LAMBDA(a, g, k,
    LET(
        r, ROWS(a),
        n, ROUNDUP(r / g, 0),
        s, SEQUENCE(n, g),
        e, TOCOL(EXPAND(IF(s > r, 0, s), , g + k, -1)),
        IFERROR(INDEX(a, FILTER(e, e), SEQUENCE(, COLUMNS(a))), "")
    )
)
Book1
ABCDEFGHIJKLM
1
2a=INSBNK(B3#,5,2)=INSBNK(B3#,7,1)
3123123123
4456456456
5789789789
6101112101112101112
7131415131415131415
8161718161718
9192021192021
10222324161718
11252627192021222324
12282930222324252627
13313233252627282930
14343536282930313233
15373839343536
16404142373839
17434445313233404142
18464748343536
19495051373839434445
20525354404142464748
21555657434445495051
22585960525354
23616263555657
24646566464748585960
25676869495051616263
26707172525354
27737475555657646566
28767778585960676869
29798081707172
30828384737475
31858687616263767778
32888990646566798081
33919293676869828384
34707172
35737475858687
36888990
37919293
38767778
39798081
40828384
41858687
42888990
43
44
45919293
46
47
48
Sheet1
Cell Formulas
RangeFormula
F2,J2F2=FORMULATEXT(F3)
B3:D33B3=SEQUENCE(31,3)
F3:H47F3=INSBNK(B3#,5,2)
J3:L38J3=INSBNK(B3#,7,1)
Dynamic array formulas.
 
I saw a post some minutes ago with some function to insert nr. of blanks after a group size, but the post disappeared, is "blank". 😊
Anyhow, I have a first fast draft that does the job.
INSBNK(a,g,k) a: array , g: group size , k: nr. of blnk rows
Excel Formula:
=LAMBDA(a, g, k,
    LET(
        r, ROWS(a),
        n, ROUNDUP(r / g, 0),
        s, SEQUENCE(n, g),
        e, TOCOL(EXPAND(IF(s > r, 0, s), , g + k, -1)),
        IFERROR(INDEX(a, FILTER(e, e), SEQUENCE(, COLUMNS(a))), "")
    )
)
Book1
ABCDEFGHIJKLM
1
2a=INSBNK(B3#,5,2)=INSBNK(B3#,7,1)
3123123123
4456456456
5789789789
6101112101112101112
7131415131415131415
8161718161718
9192021192021
10222324161718
11252627192021222324
12282930222324252627
13313233252627282930
14343536282930313233
15373839343536
16404142373839
17434445313233404142
18464748343536
19495051373839434445
20525354404142464748
21555657434445495051
22585960525354
23616263555657
24646566464748585960
25676869495051616263
26707172525354
27737475555657646566
28767778585960676869
29798081707172
30828384737475
31858687616263767778
32888990646566798081
33919293676869828384
34707172
35737475858687
36888990
37919293
38767778
39798081
40828384
41858687
42888990
43
44
45919293
46
47
48
Sheet1
Cell Formulas
RangeFormula
F2,J2F2=FORMULATEXT(F3)
B3:D33B3=SEQUENCE(31,3)
F3:H47F3=INSBNK(B3#,5,2)
J3:L38J3=INSBNK(B3#,7,1)
Dynamic array formulas.

That disappearing post was mine. After I posted it, I realized that there was an error. I tried to adopt your method of writing the formula. Curious to know what you think.

XINSERTROWS.xlsm
ABCDEFGHIJKL
1Data1 blank, omit 'blank_count' argument3 blanks
2123123123
3456456456
4789789789
5101112101112101112
6131415131415
7161718
8192021161718
9222324192021131415
10252627222324161718
11282930252627192021
12313233282930222324
13343536
14373839313233
15404142343536
16434445373839252627
17404142282930
18434445313233
19343536
20
21
22
23373839
24404142
25434445
Sheet2
Cell Formulas
RangeFormula
A2:C16A2=SEQUENCE(15,3)
E2:G18E2=XINSERTROWS(A2#,5)
J2:L25J2=XINSERTROWS(A2#,4,3)
Dynamic array formulas.


Excel Formula:
=LAMBDA(range,group_size,[blank_count],
    LET(
        bc,IF(ISOMITTED(blank_count),1,blank_count),
        ro,ROWS(range),
        d,IFNA(INDEX(range,TOCOL(EXPAND(SEQUENCE(ro,group_size),,group_size+bc)),SEQUENCE(,COLUMNS(range))),""),
        TAKE(d,((ROUNDUP(ro/group_size,0)-1)*bc)+ro)
    )
)
 
Super cool, but I think we don't need it anymore. 😊
Adding a simple tiny IF expression, " IF(TYPE(j)=64,j,IF(j<0,QUOTIENT(s-1,-j)+1,INDEX(v,,j))) " we can make the already hyper fast SPLBYGRP function do what INSBNK does and much more, without adding any more arguments. One function for all scenarios.

SPLBYGRP(ar,[ci],[nr]) Spill By Group
ar: array
[ci]: 3 options in one argument (column vector when ci is a vector, column index when ci>0, or group size when ci<0 )
- if omitted=> 1 => clm index 1
- if integer >0 => clm index
- if integer <0 => constant group size= abs(ci) rows
- ci, as clm vector or vector calculation, => the refrence vector for grouping (same row size as main array)
[nr]: nr. rows to be inserted: if omitted nr=1
Excel Formula:
=LAMBDA(ar, [ci], [nr],
    LET(
        v, IF(ar = "", "", ar),
        j, IF(ISOMITTED(ci), 1, ci),
        k, MAX(nr, 1),
        r, ROWS(v),
        s, SEQUENCE(r),
        a, IF(TYPE(j) = 64, j, IF(j < 0, QUOTIENT(s - 1, -j) + 1, INDEX(v, , j))),
        w, (VSTACK("", DROP(a, -1)) <> a) * s,
        x, FILTER(w, w),
        y, VSTACK(x, r + 1),
        z, DROP(y, 1) - DROP(y, -1),
        n, z + k,
        q, SEQUENCE(, MAX(n)),
        b, (q <= z) * q,
        c, (q <= n) * (q > z),
        i, IF(c, -1, IF(b, x - 1 + b, 0)),
        t, TOCOL(i),
        IFERROR(INDEX(v, FILTER(t, t), SEQUENCE(, COLUMNS(v))), "")
    )
)
1. Insertblank functionality
Book1.xlsx
ABCDEFGHIJKLMNOPQRS
1
2ci,-3=>group size 3ci,-5=>group size 5
3=SPLBYGRP(B4:F15,-3,2)=SPLBYGRP(B4:F15,-5)
4ar23b45ar23b45ar23b45
5aer43b54aer43b54aer43b54
6agh34b45agh34b45agh34b45
7bfg5b5bfg5b5
8cre54b54cre54b54
9cer43c23bfg5b5
10brt43c35cre54b54cer43c23
11bgf4c56cer43c23brt43c35
12bgh6c76bgf4c56
13bfg65a87bgh6c76
14agr54a98brt43c35bfg65a87
15aer54a23bgf4c56
16bgh6c76agr54a98
17aer54a23
18
19bfg65a87
20agr54a98
21aer54a23
22
23
24
Sheet7
Cell Formulas
RangeFormula
H3,N3H3=FORMULATEXT(H4)
H4:L23H4=SPLBYGRP(B4:F15,-3,2)
N4:R18N4=SPLBYGRP(B4:F15,-5)
Dynamic array formulas.
 
Nice! I dig it.

One thing that I was obsessing over when I was working on it was getting it to not have those extra blank rows at the end.

Otherwise, your first draft from post #11 would have been sufficient.
 
2. Column index functionality
Book1.xlsx
ABCDEFGHIJKLMNOPQRS
1
2ci,omitted=>cl index 1ci,4=>cl index 4
3=SPLBYGRP(B4:F15)=SPLBYGRP(B4:F15,4)
4ar23b45ar23b45ar23b45
5aer43b54aer43b54aer43b54
6agh34b45agh34b45agh34b45
7bfg5b5bfg5b5
8cre54b54bfg5b5cre54b54
9cer43c23
10brt43c35cre54b54cer43c23
11bgf4c56cer43c23brt43c35
12bgh6c76bgf4c56
13bfg65a87brt43c35bgh6c76
14agr54a98bgf4c56
15aer54a23bgh6c76bfg65a87
16bfg65a87agr54a98
17aer54a23
18agr54a98
19aer54a23
20
21
Sheet8
Cell Formulas
RangeFormula
H3,N3H3=FORMULATEXT(H4)
H4:L20H4=SPLBYGRP(B4:F15)
N4:R18N4=SPLBYGRP(B4:F15,4)
Dynamic array formulas.


3. Column vector functionality, vector calculation (useful for dates calculations), external vector (when not a column of main array)
Book1.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1vector calculation
2
3splitting by first clm, not relevantsplitting by monthsplitting by year
4=SPLBYGRP(B5:G16)=SPLBYGRP(B5:G16,MONTH(B5:B16))=SPLBYGRP(B5:G16,YEAR(B5:B16),2)
503-11-22ar23b4503-11-22ar23b4503-11-22ar23b4503-11-22ar23b45
607-11-22aer43b5407-11-22aer43b5407-11-22aer43b54
712-12-22agh34b4507-11-22aer43b5412-12-22agh34b45
817-12-22bfg5b512-12-22agh34b4517-12-22bfg5b5
922-12-22cre54b5412-12-22agh34b4517-12-22bfg5b522-12-22cre54b54
1030-12-22cer43c2322-12-22cre54b5430-12-22cer43c23
1107-01-23brt43c3517-12-22bfg5b530-12-22cer43c23
1219-01-23bgf4c56
1312-02-23bgh6c7622-12-22cre54b5407-01-23brt43c3507-01-23brt43c35
1421-02-23bfg65a8719-01-23bgf4c5619-01-23bgf4c56
1507-03-23agr54a9830-12-22cer43c2312-02-23bgh6c76
1619-03-23aer54a2312-02-23bgh6c7621-02-23bfg65a87
1707-01-23brt43c3521-02-23bfg65a8707-03-23agr54a98
1819-03-23aer54a23
1919-01-23bgf4c5607-03-23agr54a98
2019-03-23aer54a23
2112-02-23bgh6c76
22
2321-02-23bfg65a87
24
2507-03-23agr54a98
26
2719-03-23aer54a23
28
29
Sheet5
Cell Formulas
RangeFormula
I4,P4,W4I4=FORMULATEXT(I5)
I5:N28I5=SPLBYGRP(B5:G16)
P5:U21P5=SPLBYGRP(B5:G16,MONTH(B5:B16))
W5:AB20W5=SPLBYGRP(B5:G16,YEAR(B5:B16),2)
Dynamic array formulas.


Book1.xlsx
ABCDEFGHIJKLMNOPQRSTUVW
1external vector
2
3extsplitting by external vectorreference vector not contained in main array
4vector=SPLBYGRP(D5:I16,B5:B16)=SPLBYGRP(CHOOSECOLS(D5:I16,{4,6}),E5:E16,2)
5x03-11-22ar23b4503-11-22ar23b452345
6x07-11-22aer43b5407-11-22aer43b544354
7y12-12-22agh34b453445
8x17-12-22bfg5b512-12-22agh34b45
9x22-12-22cre54b54
10y30-12-22cer43c2317-12-22bfg5b555
11y07-01-23brt43c3522-12-22cre54b54
12x19-01-23bgf4c56
13x12-02-23bgh6c7630-12-22cer43c235454
14x21-02-23bfg65a8707-01-23brt43c354323
15y07-03-23agr54a98
16x19-03-23aer54a2319-01-23bgf4c56
1712-02-23bgh6c764335
1821-02-23bfg65a87456
19676
2007-03-23agr54a986587
21
2219-03-23aer54a23
235498
245423
25
26
27
Sheet6
Cell Formulas
RangeFormula
K4,R4K4=FORMULATEXT(K5)
K5:P23K5=SPLBYGRP(D5:I16,B5:B16)
R5:S26R5=SPLBYGRP(CHOOSECOLS(D5:I16,{4,6}),E5:E16,2)
Dynamic array formulas.
 
Nice! I dig it.

One thing that I was obsessing over when I was working on it was getting it to not have those extra blank rows at the end.

Otherwise, your first draft from post #11 would have been sufficient.
😂 Actually, that is the most important part, have kept it on purpose because I have already a lambda helper function ASUBTOTAL that will fill these blanks with calculations like SUBTOTAL does.
And subtotals also need the last row. 😉
That is the main purpose of grouping, otherwise why keep those blanks empty? 😂 They need a higher purpose. To get rid of them is extremely easy with a DROP(x,,-nr)
There are no details that were not taken into consideration. Wish to have more time to post stuff I have done already.
This was funny. lol
Here is a glimpse of ASUBTOTAL:
19Marzo23.xlsx
XYZAAABACADAEAFAGAHAIAJAKALAMANAO
1
2=ASUBTOTAL(Y3#,-10,"-2:-1")
3agh343gh4556agh343gh4556
4bfg6566fg878bfg6566fg878
5agr5467gr9878agr5467gr9878
6brt436rt3545brt436rt3545
7agh343gh4556agh343gh4556
8aer543rt2345aer543rt2345
9cre544tr5443cre544tr5443
10aer4356er5445aer4356er5445
11aer4356er5445aer4356er5445
12cer435er2334cer435er2334
13cre544tr5443Total518455
14agh343gh4556cre544tr5443
15agh343gh4556agh343gh4556
16cre544tr5443agh343gh4556
17bfg6566fg878cre544tr5443
18agr5467gr9878bfg6566fg878
19aer4356er5445agr5467gr9878
20brt436rt3545aer4356er5445
21brt436rt3545brt436rt3545
22bgf43gf5656brt436rt3545
23ar2334r4523bgf43gf5656
24bgf43gf5656Total563475
25ar2334r4523ar2334r4523
26agh343gh4556bgf43gf5656
27aer543rt2345ar2334r4523
28aer4356er5445agh343gh4556
29agh343gh4556aer543rt2345
30bgf43gf5656aer4356er5445
31agh343gh4556agh343gh4556
32cre544tr5443bgf43gf5656
33bfg54fg543agh343gh4556
34ar2334r4523cre544tr5443
35bgh65gh7667Total468459
36bgf43gf5656bfg54fg543
37aer543rt2345ar2334r4523
38aer4356er5445bgh65gh7667
39brt436rt3545bgf43gf5656
40agr5467gr9878aer543rt2345
41cre544tr5443aer4356er5445
42agr5467gr9878brt436rt3545
43agr5467gr9878
44cre544tr5443
45agr5467gr9878
46Total544523
47Grand Total20931912
48
ASUBTOTAL speed
Cell Formulas
RangeFormula
AH2AH2=FORMULATEXT(AH3)
Y3:AE42Y3=INDEX(Q3:W100002,SEQUENCE(W1),SEQUENCE(,7))
AH3:AN47AH3=ASUBTOTAL(Y3#,-10,"-2:-1")
Dynamic array formulas.
 
can you share lambda asubtotal please
Yes steven88, very soon, it is a package of functions, not only ASUBTOTAL a package that tells a "story". And you know that I like to present them in a certain manner that requires a lot of editing, and this is what is time consuming, and time is what I do not have...Soon, very soon.
 

Forum statistics

Threads
1,216,503
Messages
6,131,022
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