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.

 

[steven88]

what isint?

 

[jdellasala]

what isint?

Other than people that can't find the apostrophe key, ISINT is referenced as VBA code, but not even as a function as far as I could find. However, replacing it with ISNUMBER seems to work.

 

[lrobbo314]

Sorry about that. I forgot to include it. It's another lambda function.

=LAMBDA(range,MAP(range,LAMBDA(x,x-INT(x)=0)))

 

[jaeiow]

I just need the insert blank at the change in values in one column only (by column index number). But, might be another ten nested functions to get there.

 

[Xlambda]

Hi, non-iterative alternative of same task. (I am on a kind of campaign to increase the level of awareness that iterative algorithm designs (lambda helper functions) can be quite slow sometimes and should be used only if there is no other non-iterative solution. Hopefully, I will open soon a kind of manifest called Lambda Performance Act (LPA) and I'd like anybody to contribute with ideas for the lambda community, especially you, if is possible )
INSRWS(a,n) a,array ; n, nr. rows to insert, does not call any other function

=LAMBDA(a, n, DROP(IFNA(INDEX(a, TOCOL(EXPAND(+SEQUENCE(ROWS(a)), , n + 1)), SEQUENCE(, COLUMNS(a))), ""), -n))

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2		a				calc time: 1min 36sec				calc time: instant
3		=SEQUENCE(10000,3)				=INSERTBLANKS(B4#,1)				=INSRWS(B4#,1)
4		1	2	3		1	2	3		1	2	3
5		4	5	6
6		7	8	9		4	5	6		4	5	6
7		10	11	12
8		13	14	15		7	8	9		7	8	9
9		16	17	18
10		19	20	21		10	11	12		10	11	12
11		22	23	24
12		25	26	27		13	14	15		13	14	15
13		28	29	30
14		31	32	33		16	17	18		16	17	18
15		34	35	36
16		37	38	39		19	20	21		19	20	21
17		40	41	42
18		43	44	45		22	23	24		22	23	24
19		46	47	48
20		49	50	51		25	26	27		25	26	27
21		52	53	54
22		55	56	57		28	29	30		28	29	30
23		58	59	60
24		61	62	63		31	32	33		31	32	33
25		64	65	66
26		67	68	69		34	35	36		34	35	36
27		70	71	72
28		73	74	75		37	38	39		37	38	39
29		76	77	78
30		79	80	81		40	41	42		40	41	42
31		82	83	84
32		85	86	87		43	44	45		43	44	45
33		88	89	90
34		91	92	93		46	47	48		46	47	48
35		94	95	96
36		97	98	99		49	50	51		49	50	51
37		100	101	102
38		103	104	105		52	53	54		52	53	54
39		106	107	108
Sheet1

Cell Formulas
Range	Formula
B3,F3,J3	B3	=FORMULATEXT(B4)
B4:D10003	B4	=SEQUENCE(10000,3)
F4:H20002	F4	=INSERTBLANKS(B4#,1)
J4:L20002	J4	=INSRWS(B4#,1)
Dynamic array formulas.

 

[lrobbo314]

Hi, non-iterative alternative of same task. (I am on a kind of campaign to increase the level of awareness that iterative algorithm designs (lambda helper functions) can be quite slow sometimes and should be used only if there is no other non-iterative solution. Hopefully, I will open soon a kind of manifest called Lambda Performance Act (LPA) and I'd like anybody to contribute with ideas for the lambda community, especially you, if is possible )
INSRWS(a,n) a,array ; n, nr. rows to insert, does not call any other function

=LAMBDA(a, n, DROP(IFNA(INDEX(a, TOCOL(EXPAND(+SEQUENCE(ROWS(a)), , n + 1)), SEQUENCE(, COLUMNS(a))), ""), -n))

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2		a				calc time: 1min 36sec				calc time: instant
3		=SEQUENCE(10000,3)				=INSERTBLANKS(B4#,1)				=INSRWS(B4#,1)
4		1	2	3		1	2	3		1	2	3
5		4	5	6
6		7	8	9		4	5	6		4	5	6
7		10	11	12
8		13	14	15		7	8	9		7	8	9
9		16	17	18
10		19	20	21		10	11	12		10	11	12
11		22	23	24
12		25	26	27		13	14	15		13	14	15
13		28	29	30
14		31	32	33		16	17	18		16	17	18
15		34	35	36
16		37	38	39		19	20	21		19	20	21
17		40	41	42
18		43	44	45		22	23	24		22	23	24
19		46	47	48
20		49	50	51		25	26	27		25	26	27
21		52	53	54
22		55	56	57		28	29	30		28	29	30
23		58	59	60
24		61	62	63		31	32	33		31	32	33
25		64	65	66
26		67	68	69		34	35	36		34	35	36
27		70	71	72
28		73	74	75		37	38	39		37	38	39
29		76	77	78
30		79	80	81		40	41	42		40	41	42
31		82	83	84
32		85	86	87		43	44	45		43	44	45
33		88	89	90
34		91	92	93		46	47	48		46	47	48
35		94	95	96
36		97	98	99		49	50	51		49	50	51
37		100	101	102
38		103	104	105		52	53	54		52	53	54
39		106	107	108
Sheet1

Cell Formulas
Range	Formula
B3,F3,J3	B3	=FORMULATEXT(B4)
B4:D10003	B4	=SEQUENCE(10000,3)
F4:H20002	F4	=INSERTBLANKS(B4#,1)
J4:L20002	J4	=INSRWS(B4#,1)
Dynamic array formulas.

That's crazy! Is it because MAKEARRAY is slow, or is it really because it's referencing a helper LAMBDA?

 

[Xlambda]

MAKEARRAY and all others, are triggering one 1D or 2D iterative algorithms ex: 2 nested loops, for every row, every column,...apply function...next row, next column.
To understand it better, using an explicit example:
Always MAKEARRAY(120,30,LAMBDA(r,c,r+c)) will be slower than =SEQUENCE(120)+SEQUENCE(,30)
It's an iterative calculation versus "in bulk" dynamic calculation. memory is allocated diffrently.
Hope that helps.

 

[lrobbo314]

**

 

[lrobbo314]

**