SPLITBYGROUP

[lrobbo314]

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

=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

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

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

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