INSERTBLANKS

2

3

2

3

2

3

4

5

6

4

5

6

4

5

6

5

7

8

9

7

8

9

7

8

9

6

10

11

12

10

11

12

10

11

12

7

13

14

15

13

14

15

13

14

15

8

16

17

18

16

17

18

9

19

20

21

19

20

21

10

22

23

24

16

17

18

11

25

26

27

19

20

21

22

23

24

12

28

29

30

22

23

24

25

26

27

13

31

32

33

25

26

27

28

29

30

14

34

35

36

28

29

30

31

32

33

15

37

38

39

34

35

36

16

40

41

42

37

38

39

17

43

44

45

31

32

33

40

41

42

18

46

47

48

34

35

36

19

49

50

51

37

38

39

43

44

45

20

52

53

54

40

41

42

46

47

48

21

55

56

57

43

44

45

49

50

51

22

58

59

60

52

53

54

23

61

62

63

55

56

57

24

64

65

66

46

47

48

58

59

60

25

67

68

69

49

50

51

61

62

63

26

70

71

72

52

53

54

27

73

74

75

55

56

57

64

65

66

28

76

77

78

58

59

60

67

68

69

29

79

80

81

70

71

72

30

82

83

84

73

74

75

31

85

86

87

61

62

63

76

77

78

32

88

89

90

64

65

66

79

80

81

33

91

92

93

67

68

69

82

83

84

34

70

71

72

35

73

74

75

85

86

87

36

88

89

90

37

91

92

93

38

76

77

78

39

79

80

81

40

82

83

84

41

85

86

87

42

88

89

90

43

44

45

91

92

93

46

47

48

Sheet1

Cell Formulas
Range	Formula
F2,J2	F2	=FORMULATEXT(F3)
B3:D33	B3	=SEQUENCE(31,3)
F3:H47	F3	=INSBNK(B3#,5,2)
J3:L38	J3	=INSBNK(B3#,7,1)
Dynamic array formulas.

lrobbo314 · Apr 3, 2023

Xlambda said:
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
A B C D E F G H I J K L M
1
2 a =INSBNK(B3#,5,2) =INSBNK(B3#,7,1)
3 1 2 3 1 2 3 1 2 3
4 4 5 6 4 5 6 4 5 6
5 7 8 9 7 8 9 7 8 9
6 10 11 12 10 11 12 10 11 12
7 13 14 15 13 14 15 13 14 15
8 16 17 18 16 17 18
9 19 20 21 19 20 21
10 22 23 24 16 17 18
11 25 26 27 19 20 21 22 23 24
12 28 29 30 22 23 24 25 26 27
13 31 32 33 25 26 27 28 29 30
14 34 35 36 28 29 30 31 32 33
15 37 38 39 34 35 36
16 40 41 42 37 38 39
17 43 44 45 31 32 33 40 41 42
18 46 47 48 34 35 36
19 49 50 51 37 38 39 43 44 45
20 52 53 54 40 41 42 46 47 48
21 55 56 57 43 44 45 49 50 51
22 58 59 60 52 53 54
23 61 62 63 55 56 57
24 64 65 66 46 47 48 58 59 60
25 67 68 69 49 50 51 61 62 63
26 70 71 72 52 53 54
27 73 74 75 55 56 57 64 65 66
28 76 77 78 58 59 60 67 68 69
29 79 80 81 70 71 72
30 82 83 84 73 74 75
31 85 86 87 61 62 63 76 77 78
32 88 89 90 64 65 66 79 80 81
33 91 92 93 67 68 69 82 83 84
34 70 71 72
35 73 74 75 85 86 87
36 88 89 90
37 91 92 93
38 76 77 78
39 79 80 81
40 82 83 84
41 85 86 87
42 88 89 90
43
44
45 91 92 93
46
47
48
Sheet1
Cell Formulas
Range Formula
F2,J2 F2 =FORMULATEXT(F3)
B3:D33 B3 =SEQUENCE(31,3)
F3:H47 F3 =INSBNK(B3#,5,2)
J3:L38 J3 =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

A

B

C

D

E

F

G

H

I

J

K

L

1

Data

1 blank, omit 'blank_count' argument

3 blanks

2

2

3

2

3

2

3

4

5

6

4

5

6

4

5

6

4

7

8

9

7

8

9

7

8

9

5

10

11

12

10

11

12

10

11

12

6

13

14

15

13

14

15

7

16

17

18

8

19

20

21

16

17

18

9

22

23

24

19

20

21

13

14

15

10

25

26

27

22

23

24

16

17

18

11

28

29

30

25

26

27

19

20

21

12

31

32

33

28

29

30

22

23

24

13

34

35

36

14

37

38

39

31

32

33

15

40

41

42

34

35

36

16

43

44

45

37

38

39

25

26

27

17

40

41

42

28

29

30

18

43

44

45

31

32

33

19

34

35

36

20

21

22

23

37

38

39

24

40

41

42

25

43

44

45

Sheet2

Cell Formulas
Range	Formula
A2:C16	A2	=SEQUENCE(15,3)
E2:G18	E2	=XINSERTROWS(A2#,5)
J2:L25	J2	=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)
    )
)

Xlambda · Apr 3, 2023

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

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

1

2

ci,-3=>group size 3

ci,-5=>group size 5

3

=SPLBYGRP(B4:F15,-3,2)

=SPLBYGRP(B4:F15,-5)

4

a

r

23

b

45

r

23

b

45

r

23

b

45

5

a

er

43

b

54

a

er

43

b

54

a

er

43

b

54

6

a

gh

34

b

45

a

gh

34

b

45

a

gh

34

b

45

7

b

fg

5

b

5

b

fg

5

b

5

8

c

re

54

b

54

c

re

54

b

54

9

c

er

43

c

23

b

fg

5

b

5

10

b

rt

43

c

35

c

re

54

b

54

c

er

43

c

23

11

b

gf

4

c

56

c

er

43

c

23

b

rt

43

c

35

12

b

gh

6

c

76

b

gf

4

c

56

13

b

fg

65

a

87

b

gh

6

c

76

14

a

gr

54

a

98

b

rt

43

c

35

b

fg

65

a

87

15

a

er

54

a

23

b

gf

4

c

56

16

b

gh

6

c

76

a

gr

54

a

98

17

a

er

54

a

23

18

19

b

fg

65

a

87

20

a

gr

54

a

98

21

a

er

54

a

23

22

23

24

Sheet7

Cell Formulas
Range	Formula
H3,N3	H3	=FORMULATEXT(H4)
H4:L23	H4	=SPLBYGRP(B4:F15,-3,2)
N4:R18	N4	=SPLBYGRP(B4:F15,-5)
Dynamic array formulas.

lrobbo314 · Apr 3, 2023

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.

Xlambda · Apr 3, 2023

2. Column index functionality

Book1.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

1

2

ci,omitted=>cl index 1

ci,4=>cl index 4

3

=SPLBYGRP(B4:F15)

=SPLBYGRP(B4:F15,4)

4

a

r

23

b

45

r

23

b

45

=SPLBYGRP(B5:G16,MONTH(B5:B16))

r

23

b

45

5

a

er

43

b

54

a

er

43

b

54

a

er

43

b

54

6

a

gh

34

b

45

a

gh

34

b

45

a

gh

34

b

45

7

b

fg

5

b

5

b

fg

5

b

5

8

c

re

54

b

54

b

fg

5

b

5

c

re

54

b

54

9

c

er

43

c

23

10

b

rt

43

c

35

c

re

54

b

54

c

er

43

c

23

11

b

gf

4

c

56

c

er

43

c

23

b

rt

43

c

35

12

b

gh

6

c

76

b

gf

4

c

56

13

b

fg

65

a

87

b

rt

43

c

35

b

gh

6

c

76

14

a

gr

54

a

98

b

gf

4

c

56

15

a

er

54

a

23

b

gh

6

c

76

b

fg

65

a

87

16

b

fg

65

a

87

a

gr

54

a

98

17

a

er

54

a

23

18

a

gr

54

a

98

19

a

er

54

a

23

20

21

Sheet8

Cell Formulas
Range	Formula
H3,N3	H3	=FORMULATEXT(H4)
H4:L20	H4	=SPLBYGRP(B4:F15)
N4:R18	N4	=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

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

vector calculation

2

3

splitting by first clm, not relevant

splitting by month

splitting by year

4

=SPLBYGRP(B5:G16)

=SPLBYGRP(B5:G16,YEAR(B5:B16),2)

5

03-11-22

a

r

23

b

45

a

r

23

b

45

a

r

23

b

45

=SPLBYGRP(CHOOSECOLS(D5:I16,{4,6}),E5:E16,2)

a

r

23

b

45

6

07-11-22

a

er

43

b

54

07-11-22

a

er

43

b

54

07-11-22

a

er

43

b

54

7

12-12-22

a

gh

34

b

45

07-11-22

a

er

43

b

54

12-12-22

a

gh

34

b

45

8

17-12-22

b

fg

5

b

5

12-12-22

a

gh

34

b

45

17-12-22

b

fg

5

b

5

9

22-12-22

c

re

54

b

54

12-12-22

a

gh

34

b

45

17-12-22

b

fg

5

b

5

22-12-22

c

re

54

b

54

10

30-12-22

c

er

43

c

23

22-12-22

c

re

54

b

54

30-12-22

c

er

43

c

23

11

07-01-23

b

rt

43

c

35

17-12-22

b

fg

5

b

5

30-12-22

c

er

43

c

23

12

19-01-23

b

gf

4

c

56

13

12-02-23

b

gh

6

c

76

22-12-22

c

re

54

b

54

07-01-23

b

rt

43

c

35

07-01-23

b

rt

43

c

35

14

21-02-23

b

fg

65

a

87

19-01-23

b

gf

4

c

56

19-01-23

b

gf

4

c

56

15

07-03-23

a

gr

54

a

98

30-12-22

c

er

43

c

23

12-02-23

b

gh

6

c

76

16

19-03-23

a

er

54

a

23

12-02-23

b

gh

6

c

76

21-02-23

b

fg

65

a

87

17

07-01-23

b

rt

43

c

35

21-02-23

b

fg

65

a

87

07-03-23

a

gr

54

a

98

18

19-03-23

a

er

54

a

23

19

19-01-23

b

gf

4

c

56

07-03-23

a

gr

54

a

98

20

19-03-23

a

er

54

a

23

21

12-02-23

b

gh

6

c

76

22

23

21-02-23

b

fg

65

a

87

24

25

07-03-23

a

gr

54

a

98

26

27

19-03-23

a

er

54

a

23

28

29

Sheet5

Cell Formulas
Range	Formula
I4,P4,W4	I4	=FORMULATEXT(I5)
I5:N28	I5	=SPLBYGRP(B5:G16)
P5:U21	P5	=SPLBYGRP(B5:G16,MONTH(B5:B16))
W5:AB20	W5	=SPLBYGRP(B5:G16,YEAR(B5:B16),2)
Dynamic array formulas.

Book1.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

1

external vector

2

3

ext

splitting by external vector

reference vector not contained in main array

4

vector

=SPLBYGRP(D5:I16,B5:B16)

5

x

03-11-22

a

r

23

b

45

=ASUBTOTAL(Y3#,-10,"-2:-1")

a

r

23

b

45

23

45

6

x

07-11-22

a

er

43

b

54

07-11-22

a

er

43

b

54

43

54

7

y

12-12-22

a

gh

34

b

45

34

45

8

x

17-12-22

b

fg

5

b

5

12-12-22

a

gh

34

b

45

9

x

22-12-22

c

re

54

b

54

10

y

30-12-22

c

er

43

c

23

17-12-22

b

fg

5

b

5

11

y

07-01-23

b

rt

43

c

35

22-12-22

c

re

54

b

54

12

x

19-01-23

b

gf

4

c

56

13

x

12-02-23

b

gh

6

c

76

30-12-22

c

er

43

c

23

54

14

x

21-02-23

b

fg

65

a

87

07-01-23

b

rt

43

c

35

43

23

15

y

07-03-23

a

gr

54

a

98

16

x

19-03-23

a

er

54

a

23

19-01-23

b

gf

4

c

56

17

12-02-23

b

gh

6

c

76

43

35

18

21-02-23

b

fg

65

a

87

4

56

19

6

76

20

07-03-23

a

gr

54

a

98

65

87

21

22

19-03-23

a

er

54

a

23

54

98

24

54

23

25

26

27

Sheet6

Cell Formulas
Range	Formula
K4,R4	K4	=FORMULATEXT(K5)
K5:P23	K5	=SPLBYGRP(D5:I16,B5:B16)
R5:S26	R5	=SPLBYGRP(CHOOSECOLS(D5:I16,{4,6}),E5:E16,2)
Dynamic array formulas.

Xlambda · Apr 3, 2023

lrobbo314 said:
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

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

AM

AN

AO

1

2

3

gh

34

3

gh

45

56