Example use of SCAN with GROUPBY to solve gaps and islands problem in Excel

excel-gaps-islands.txt

=LET(
    test_data, {
        45232, 1;
        45233, 1;
        45234, 2;
        45235, 2;
        45236, 1;
        45237, 1;
        45238, 2;
        45239, 2;
        45240, 3;
        45241, 4
    },
    d, test_data,
    g, GROUPBY(
        SCAN(
            ,
            SEQUENCE(ROWS(d)),
            LAMBDA(a, b, IF(INDEX(d, b, 2) <> INDEX(d, b - 1, 2), a + 1, a))
        ),
        TAKE(d, , 1),
        HSTACK(MIN, MAX)
    ),
    VSTACK({"island_id", "MinDate", "MaxDate"}, DROP(g, 1))
)