INSERTBLANKS

lrobbo314 · Feb 15, 2023

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

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

1

A

E

I

A

E

I

2

B

F

J

3

C

G

K

4

2

D

H

L

B

F

J

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 · Feb 15, 2023

what isint?

jdellasala · Feb 16, 2023

steven88 said:
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 · Feb 16, 2023

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

Excel Formula:

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

jaeiow · Feb 16, 2023

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 · Apr 2, 2023

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

Excel Formula:

=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

4

2

3

1

2

3

1

2

3

5

4

5

6

7

8

9

4

5

6

4

5

6

7

10

11

12

8

13

14

15

7

8

9

7

8

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 · Apr 2, 2023

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

Excel Formula:

=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 · Apr 2, 2023

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 · Apr 2, 2023

lrobbo314 · Apr 2, 2023

INSERTBLANKS

lrobbo314

Well-known Member

steven88

New Member

jdellasala

Well-known Member

lrobbo314

Well-known Member

jaeiow

Board Regular

Xlambda

Well-known Member

lrobbo314

Well-known Member

Xlambda

Well-known Member

lrobbo314

Well-known Member

lrobbo314

Well-known Member

Forum statistics

Share this page

INSERTBLANKS

Well-known Member

New Member

Well-known Member

Well-known Member

Board Regular

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock