Excel formulas for generating ; sep team membership

team members by scope and team.txt

Data

| scope | team | name    |
|-------|------|---------|
| N     | T1   | John    |
| R     | T2   | Bob     |
| N     | T3   | Sally   |
| R     | T4   | Zena    |
| N     | T1   | Larry   |
| R     | T6   | Harry   |
| N     | T7   | Albert  |
| R     | T4   | Kirsten |
| N     | T3   | Paul    |


-===================================================================================================================

Formula 1

=LET(
    nat_teams, SORT(
        UNIQUE(
            FILTER(
                Table1[[#All],[team]],
                Table1[[#All],[scope]] =
                    "N"
            )
        )
    ),
    reg_teams, SORT(
        UNIQUE(
            FILTER(
                Table1[[#All],[team]],
                Table1[[#All],[scope]] =
                    "R"
            )
        )
    ),
    both_teams, VSTACK(
        nat_teams,
        reg_teams
    ),
    nat, VSTACK(
        {"National"},
        MAKEARRAY(
            COUNTA(nat_teams) -
                1,
            1,
            LAMBDA(x, y, "")
        )
    ),
    reg, VSTACK(
        {"Regional"},
        MAKEARRAY(
            COUNTA(reg_teams) -
                1,
            1,
            LAMBDA(x, y, "")
        )
    ),
    scope, VSTACK(nat, reg),
    members, MAP(
        both_teams,
        LAMBDA(t,
            TEXTJOIN(
                "; ",
                TRUE,
                SORT(
                    CHOOSEROWS(
                        Table1[name],
                        FILTER(
                            ROW(
                                Table1[team]
                            ) -
                                ROW(
                                    Table1[[#Headers],[team]]
                                ),
                            Table1[team] =
                                t
                        )
                    )
                )
            )
        )
    ),
    output, HSTACK(
        scope,
        both_teams,
        members
    ),
    output
)

-===================================================================================================================

Formula 2

=LET(
    data, UNIQUE(
        SORT(
            CHOOSECOLS(
                Table1[[scope]:[team]],
                1,
                2
            ),
            {1, 2}
        )
    ),
    team, CHOOSECOLS(data, 2),
    members, BYROW(
        team,
        LAMBDA(r,
            TEXTJOIN(
                "; ",
                TRUE,
                UNIQUE(
                    SORT(
                        FILTER(
                            Table1[name],
                            Table1[team] =
                                r
                        )
                    )
                )
            )
        )
    ),
    scope, VSTACK(
        {"National"},
        MAKEARRAY(
            ROWS(
                FILTER(
                    data,
                    CHOOSECOLS(
                        data,
                        1
                    ) = "N"
                )
            ) - 1,
            1,
            LAMBDA(x, y, "")
        ),
        {"Regional"},
        MAKEARRAY(
            ROWS(
                FILTER(
                    data,
                    CHOOSECOLS(
                        data,
                        1
                    ) = "R"
                )
            ) - 1,
            1,
            LAMBDA(x, y, "")
        )
    ),
    HSTACK(scope, team, members)
)

-===================================================================================================================

Formula 3

=LET(
    data, UNIQUE(
        SORT(
            CHOOSECOLS(
                Table1[[scope]:[team]],
                1,
                2
            ),
            {1, 2}
        )
    ),
    ng, CHOOSECOLS(data, 1),
    team, CHOOSECOLS(data, 2),
    members, BYROW(
        team,
        LAMBDA(r,
            TEXTJOIN(
                "; ",
                TRUE,
                UNIQUE(
                    SORT(
                        FILTER(
                            Table1[name],
                            Table1[team] =
                                r
                        )
                    )
                )
            )
        )
    ),
    scope, MAP(
        SEQUENCE(COUNTA(ng)),
        LAMBDA(x,
            IF(
                SUMPRODUCT(
                    --(
                        INDEX(
                            ng,
                            x
                        ) =
                            INDEX(
                                ng,
                                SEQUENCE(
                                    x
                                )
                            )
                    )
                ) = 1,
                INDEX(ng, x),
                ""
            )
        )
    ),
    HSTACK(scope, team, members)
)

-===================================================================================================================