SPLITBYGROUP

lrobbo314 · Feb 27, 2023

SPLITBYGROUP splits a range by the values in a given column.

The SPLITBYGROUP formula uses another formula, INSERTAT, which will be listed below as well.

I was inspired to write this because of a post from @jaeiow in this post.

SPLITBYGROUP

Excel Formula:

=LAMBDA(
    range,col,
        LET(
            d,range,
            r,ROWS(d),
            q,SEQUENCE(r-1,,r,-1),
            REDUCE(d,q,LAMBDA(s,c,IF(INDEX(s,c,col)<>INDEX(s,c-1,col),INSERTAT(s,c),s)))
        )
)

INSERTAT

Excel Formula:

=LAMBDA(
    range,current,
        LET(
            r,ROWS(range),
            c,COLUMNS(range),
            VSTACK(
                INDEX(range,SEQUENCE(current-1),SEQUENCE(,c)),
                VSTACK(
                    INDEX(" ",1,SEQUENCE(,c,1,0)),
                    INDEX(range,SEQUENCE(r-current+1,,current),SEQUENCE(,c))
                )
            )
        )
)

SPLITBYGROUP

A

B

C

D

E

F

G

1

ID

SplitCol

Value

ID

SplitCol

Value

2

1

A

93

1

A

93

3

2

A

4

2

A

4

3

B

9

5

4

C

97

3

B

9

6

5

C

89

7

6

C

77

4

C

97

8

7

D

89

5

C

89

9

6

C

77

10

11

7

D

89

Sheet5

Cell Formulas
Range	Formula
E2:G11	E2	=SPLITBYGROUP(A2:C8,2)
Dynamic array formulas.

Xlambda · Apr 2, 2023

Another iterative algorithm awareness example.
Alternative function, non-iterative, "in-bulk" dynamic calculation design, no REDUCE/VSTACK or any of the lambda helper functions. Only pattern calculations in few steps.
SPLBYGRP(ar,[ci],[nr])
ar: any array
[ci]: clm index ; if omitted ci=1
[nr]: nr. rows to be inserted: if omitted nr=1

Excel Formula:

=LAMBDA(ar, [ci], [nr],
    LET(
        v, IF(ar = "", "", ar),
        j, MAX(ci, 1),
        k, MAX(nr, 1),
        a, INDEX(v, , j),
        r, ROWS(a),
        s, SEQUENCE(r),
        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))), "")
    )
)

Apparently, it looks like there is no way that an ugly, long and with so many variables like mine, can be better than a simple and elegant design like yours.
Here are the results:

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

1

2

ci,nr,omitted=> ci=1,nr=1

ci,3;nr,2

3

=SPLBYGRP(B4:F15)

=SPLBYGRP(B4:F15,4,2)

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

11

b

gf

4

c

56

c

er

43

c

23

c

er

43

c

23

12

b

gh

6

c

76

b

rt

43

c

35

13

b

fg

65

a

87

b

rt

43

c

35

b

gf

4

c

56

14

a

gr

54

a

98

b

gf

4

c

56

b

gh

6

c

76

15

a

er

54

a

23

b

gh

6

c

76

16

b

fg

65

a

87

17

b

fg

65

a

87

18

a

gr

54

a

98

a

gr

54

a

98

19

a

er

54

a

23

a

er

54

a

23

20

21

22

23

Sheet3

Cell Formulas
Range	Formula
H3,N3	H3	=FORMULATEXT(H4)
H4:L20	H4	=SPLBYGRP(B4:F15)
N4:R21	N4	=SPLBYGRP(B4:F15,4,2)
Dynamic array formulas.

Book1

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

1

2

simulating a large array

3

nr rows

10000

calc time 5min46sec

calc time: instant

4

=INDEX(B6:F17,MOD(SEQUENCE(J3)-1,12)+1,SEQUENCE(,5))

5

↓↓

=SPLITBYGROUP(I6#,1)

=SPLBYGRP(I6#)

6

a

r

23

b

45

a

r

23

b

45

a

r

23

b

45

a

r

23

b

45

7

a

er

43

b

54

a

er

43

b

54

a

er

43

b

54

a

er

43

b

54

8

a

gh

34

b

45

a

gh

34

b

45

a

gh

34

b

45

a

gh

34

b

45

9

b

fg

5

b

5

b

fg

5

b

5

10

c

re

54

b

54

c

re

54

b

54

b

fg

5

b

5

b

fg

5

b

5

11

c

er

43

c

23

c

er

43

c

23

12

b

rt

43

c

35

b

rt

43

c

35

c

re

54

b

54

c

re

54

b

54

13

b

gf

4

c

56

b

gf

4

c

56

c

er

43

c

23

c

er

43

c

23

14

b

gh

6

c

76

b

gh

6

c

76

15

b

fg

65

a

87

b

fg

65

a

87

b

rt

43

c

35

b

rt

43

c

35

16

a

gr

54

a

98

a

gr

54

a

98

b

gf

4

c

56

b

gf

4

c

56

17

a

er

54

a

23

a

er

54

a

23

b

gh

6

c

76

b

gh

6

c

76

18

a

r

23

b

45

b

fg

65

a

87

b

fg

65

a

87

19

a

er

43

b

54

20

a

gh

34

b

45

a

gr

54

a

98

a

gr

54

a

98

21

b

fg

5

b

5

a

er

54

a

23

a

er

54

a

23

22

c

re

54

b

54

a

r

23

b

45

a

r

23

b

45

23

c

er

43

c

23

a

er

43

b

54

a

er

43

b

54

24

b

rt

43

c

35

a

gh

34

b

45

a

gh

34

b

45

25

b

gf

4

c

56

26

b

gh

6

c

76

b

fg

5

b

5

b

fg

5

b

5

27

b

fg

65

a

87

28

a

gr

54

a

98

c

re

54

b

54

c

re

54

b

54

29

a

er

54

a

23

c

er

43

c

23

c

er

43

c

23

30

a

r

23

b

45

31

a

er

43

b

54

b

rt

43

c

35

b

rt

43

c

35

32

a

gh

34

b

45

b

gf

4

c

56

b

gf

4

c

56

33

b

fg

5

b

5

b

gh

6

c

76

b

gh

6

c

76

34

c

re

54

b

54

b

fg

65

a

87

b

fg

65

a

87

35

c

er

43

c

23

36

b

rt

43

c

35

a

gr

54

a

98

a

gr

54

a

98

37

b

gf

4

c

56

a

er

54

a

23

a

er

54

a

23

38

b

gh

6

c

76

a

r

23

b

45

a

r

23

b

45

39

b

fg

65

a

87

a

er

43

b

54

a

er

43

b

54

40

a

gr

54

a

98

a

gh

34

b

45

a

gh

34

b

45

41

a

er

54

a

23

42

a

r

23

b

45

b

fg

5

b

5

b

fg

5

b

5

43

a

er

43

b

54

44

a

gh

34

b

45

c

re

54

b

54

c

re

54

b

54

45

b

fg

5

b

5

c

er

43

c

23

c

er

43

c

23

46

c

re

54

b

54

47

c

er

43

c

23

b

rt

43

c

35

b

rt

43

c

35

48

b

rt

43

c

35

b

gf

4

c

56

b

gf

4

c

56

49

b

gf

4

c

56

b

gh

6

c

76

b

gh

6

c

76

50

b

gh

6

c

76

b

fg

65

a

87

b

fg

65

a

87

51

b

fg

65

a

87

52

a

gr

54

a

98

a

gr

54

a

98

a

gr

54

a

98

53

a

er

54

a

23

a

er

54

a

23

a

er

54

a

23

54

a

r

23

b

45

a

r

23

b

45

a

r

23

b

45

55

a

er

43

b

54

a

er

43

b

54

a

er

43

b

54

56

a

gh

34

b

45

a

gh

34

b

45

a

gh

34

b

45

57

b

fg

5

b

5

58

c

re

54

b

54

b

fg

5

b

5

b

fg

5

b

5

59

c

er

43

c

23

60

b

rt

43

c

35

c

re

54

b

54

c

re

54

b

54

61

b

gf

4

c

56

c

er

43

c

23

c

er

43

c

23

62

b

gh

6

c

76

Sheet4

Cell Formulas
Range	Formula
F4	F4	=FORMULATEXT(I6)
P5,W5	P5	=FORMULATEXT(P6)
I6:M10005	I6	=INDEX(B6:F17,MOD(SEQUENCE(J3)-1,12)+1,SEQUENCE(,5))
P6:T13338	P6	=SPLITBYGROUP(I6#,1)
W6:AA13339	W6	=SPLBYGRP(I6#)
Dynamic array formulas.

SPLITBYGROUP

lrobbo314

Well-known Member

Xlambda

Well-known Member

Forum statistics

Share this page

SPLITBYGROUP

lrobbo314

Well-known Member

Xlambda

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