INSERTBLANKS

[lrobbo314]

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

=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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	1		1		1		1		A	E	I		A	E	I
2	2				2				B	F	J
3	3				3				C	G	K
4	4		2						D	H	L		B	F	J
5	5
6
7			3				2						C	G	K
8
9
10			4										D	H	L
11
12
13			5				3
Sheet1

Cell Formulas
Range	Formula
A1:A5	A1	=SEQUENCE(5)
C1:C13	C1	=INSERTBLANKS(A1#,2)
E1:E3	E1	=SEQUENCE(3)
G1:G13	G1	=INSERTBLANKS(E1#,5)
M1:O10	M1	=INSERTBLANKS(I1:K4,2)
Dynamic array formulas.

 

[Xlambda]

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

=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.

 

[lrobbo314]

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

=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	1	2	3		1	2	3			1	2	3
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.

=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]

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

=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		a	r	23	b	45		a	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]

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]

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		a	r	23	b	45		a	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,MONTH(B5:B16))							=SPLBYGRP(B5:G16,YEAR(B5:B16),2)
5		03-11-22	a	r	23	b	45		03-11-22	a	r	23	b	45		03-11-22	a	r	23	b	45		03-11-22	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)							=SPLBYGRP(CHOOSECOLS(D5:I16,{4,6}),E5:E16,2)
5		x		03-11-22	a	r	23	b	45		03-11-22	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		5	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	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
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]

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											=ASUBTOTAL(Y3#,-10,"-2:-1")
3		a	gh	34	3	gh	45	56			a	gh	34	3	gh	45	56
4		b	fg	65	66	fg	87	8			b	fg	65	66	fg	87	8
5		a	gr	54	67	gr	98	78			a	gr	54	67	gr	98	78
6		b	rt	43	6	rt	35	45			b	rt	43	6	rt	35	45
7		a	gh	34	3	gh	45	56			a	gh	34	3	gh	45	56
8		a	er	54	3	rt	23	45			a	er	54	3	rt	23	45
9		c	re	54	4	tr	54	43			c	re	54	4	tr	54	43
10		a	er	43	56	er	54	45			a	er	43	56	er	54	45
11		a	er	43	56	er	54	45			a	er	43	56	er	54	45
12		c	er	43	5	er	23	34			c	er	43	5	er	23	34
13		c	re	54	4	tr	54	43			Total					518	455
14		a	gh	34	3	gh	45	56			c	re	54	4	tr	54	43
15		a	gh	34	3	gh	45	56			a	gh	34	3	gh	45	56
16		c	re	54	4	tr	54	43			a	gh	34	3	gh	45	56
17		b	fg	65	66	fg	87	8			c	re	54	4	tr	54	43
18		a	gr	54	67	gr	98	78			b	fg	65	66	fg	87	8
19		a	er	43	56	er	54	45			a	gr	54	67	gr	98	78
20		b	rt	43	6	rt	35	45			a	er	43	56	er	54	45
21		b	rt	43	6	rt	35	45			b	rt	43	6	rt	35	45
22		b	gf	4	3	gf	56	56			b	rt	43	6	rt	35	45
23		a	r	23	34	r	45	23			b	gf	4	3	gf	56	56
24		b	gf	4	3	gf	56	56			Total					563	475
25		a	r	23	34	r	45	23			a	r	23	34	r	45	23
26		a	gh	34	3	gh	45	56			b	gf	4	3	gf	56	56
27		a	er	54	3	rt	23	45			a	r	23	34	r	45	23
28		a	er	43	56	er	54	45			a	gh	34	3	gh	45	56
29		a	gh	34	3	gh	45	56			a	er	54	3	rt	23	45
30		b	gf	4	3	gf	56	56			a	er	43	56	er	54	45
31		a	gh	34	3	gh	45	56			a	gh	34	3	gh	45	56
32		c	re	54	4	tr	54	43			b	gf	4	3	gf	56	56
33		b	fg	5	4	fg	5	43			a	gh	34	3	gh	45	56
34		a	r	23	34	r	45	23			c	re	54	4	tr	54	43
35		b	gh	6	5	gh	76	67			Total					468	459
36		b	gf	4	3	gf	56	56			b	fg	5	4	fg	5	43
37		a	er	54	3	rt	23	45			a	r	23	34	r	45	23
38		a	er	43	56	er	54	45			b	gh	6	5	gh	76	67
39		b	rt	43	6	rt	35	45			b	gf	4	3	gf	56	56
40		a	gr	54	67	gr	98	78			a	er	54	3	rt	23	45
41		c	re	54	4	tr	54	43			a	er	43	56	er	54	45
42		a	gr	54	67	gr	98	78			b	rt	43	6	rt	35	45
43											a	gr	54	67	gr	98	78
44											c	re	54	4	tr	54	43
45											a	gr	54	67	gr	98	78
46											Total					544	523
47											Grand Total					2093	1912
48
ASUBTOTAL speed

Cell Formulas
Range	Formula
AH2	AH2	=FORMULATEXT(AH3)
Y3:AE42	Y3	=INDEX(Q3:W100002,SEQUENCE(W1),SEQUENCE(,7))
AH3:AN47	AH3	=ASUBTOTAL(Y3#,-10,"-2:-1")
Dynamic array formulas.

 

[steven88]

can you share lambda asubtotal please

 

[Xlambda]

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.