SPLITBYGROUP

=SPLITBYGROUP(range,column)

range
the range to be split by group
column
the column to be used to split the range

SPLITBYGROUP splits a range by the values in a given column

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,929
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFG
1IDSplitColValueIDSplitColValue
21A931A93
32A42A4
43B9
54C973B9
65C89
76C774C97
87D895C89
96C77
10
117D89
Sheet5
Cell Formulas
RangeFormula
E2:G11E2=SPLITBYGROUP(A2:C8,2)
Dynamic array formulas.
 
Upvote 0
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
ABCDEFGHIJKLMNOPQRS
1
2ci,nr,omitted=> ci=1,nr=1ci,3;nr,2
3=SPLBYGRP(B4:F15)=SPLBYGRP(B4:F15,4,2)
4ar23b45ar23b45ar23b45
5aer43b54aer43b54aer43b54
6agh34b45agh34b45agh34b45
7bfg5b5bfg5b5
8cre54b54bfg5b5cre54b54
9cer43c23
10brt43c35cre54b54
11bgf4c56cer43c23cer43c23
12bgh6c76brt43c35
13bfg65a87brt43c35bgf4c56
14agr54a98bgf4c56bgh6c76
15aer54a23bgh6c76
16bfg65a87
17bfg65a87
18agr54a98agr54a98
19aer54a23aer54a23
20
21
22
23
Sheet3
Cell Formulas
RangeFormula
H3,N3H3=FORMULATEXT(H4)
H4:L20H4=SPLBYGRP(B4:F15)
N4:R21N4=SPLBYGRP(B4:F15,4,2)
Dynamic array formulas.

Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAAB
1
2simulating a large array
3nr rows10000calc time 5min46seccalc time: instant
4=INDEX(B6:F17,MOD(SEQUENCE(J3)-1,12)+1,SEQUENCE(,5))
5↓↓=SPLITBYGROUP(I6#,1)=SPLBYGRP(I6#)
6ar23b45ar23b45ar23b45ar23b45
7aer43b54aer43b54aer43b54aer43b54
8agh34b45agh34b45agh34b45agh34b45
9bfg5b5bfg5b5
10cre54b54cre54b54bfg5b5bfg5b5
11cer43c23cer43c23
12brt43c35brt43c35cre54b54cre54b54
13bgf4c56bgf4c56cer43c23cer43c23
14bgh6c76bgh6c76
15bfg65a87bfg65a87brt43c35brt43c35
16agr54a98agr54a98bgf4c56bgf4c56
17aer54a23aer54a23bgh6c76bgh6c76
18ar23b45bfg65a87bfg65a87
19aer43b54
20agh34b45agr54a98agr54a98
21bfg5b5aer54a23aer54a23
22cre54b54ar23b45ar23b45
23cer43c23aer43b54aer43b54
24brt43c35agh34b45agh34b45
25bgf4c56
26bgh6c76bfg5b5bfg5b5
27bfg65a87
28agr54a98cre54b54cre54b54
29aer54a23cer43c23cer43c23
30ar23b45
31aer43b54brt43c35brt43c35
32agh34b45bgf4c56bgf4c56
33bfg5b5bgh6c76bgh6c76
34cre54b54bfg65a87bfg65a87
35cer43c23
36brt43c35agr54a98agr54a98
37bgf4c56aer54a23aer54a23
38bgh6c76ar23b45ar23b45
39bfg65a87aer43b54aer43b54
40agr54a98agh34b45agh34b45
41aer54a23
42ar23b45bfg5b5bfg5b5
43aer43b54
44agh34b45cre54b54cre54b54
45bfg5b5cer43c23cer43c23
46cre54b54
47cer43c23brt43c35brt43c35
48brt43c35bgf4c56bgf4c56
49bgf4c56bgh6c76bgh6c76
50bgh6c76bfg65a87bfg65a87
51bfg65a87
52agr54a98agr54a98agr54a98
53aer54a23aer54a23aer54a23
54ar23b45ar23b45ar23b45
55aer43b54aer43b54aer43b54
56agh34b45agh34b45agh34b45
57bfg5b5
58cre54b54bfg5b5bfg5b5
59cer43c23
60brt43c35cre54b54cre54b54
61bgf4c56cer43c23cer43c23
62bgh6c76
Sheet4
Cell Formulas
RangeFormula
F4F4=FORMULATEXT(I6)
P5,W5P5=FORMULATEXT(P6)
I6:M10005I6=INDEX(B6:F17,MOD(SEQUENCE(J3)-1,12)+1,SEQUENCE(,5))
P6:T13338P6=SPLITBYGROUP(I6#,1)
W6:AA13339W6=SPLBYGRP(I6#)
Dynamic array formulas.
 

Forum statistics

Threads
1,216,503
Messages
6,131,022
Members
449,616
Latest member
PsychoCube

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top