Text
List team members by group in alphabetical order (TEXTJOIN, SORT & FILTER)
Roll up vertical employee-to-squad assignments into sorted, comma-separated roster strings per team with graceful empty-team fallbacks.
Turn a flat two-column assignment table into alphabetical squad rosters!
Filter assignments using a literal team label, sort the matching names, then join them. This example requires nonempty names, team labels and query so the roster and count describe the same rows.
The count is assignment rows, not distinct people. Duplicate names and duplicate assignments remain; use stable person identifiers and a deliberate deduplication policy when counting people. Alphabetical ordering follows the spreadsheet engine and is not a universal multilingual collation guarantee.
Team matching here is case-insensitive and treats * and ? literally. No trimming is performed. No matching assignments means No members; it does not prove that a hiring requisition is open.
| Target Squad (D) | Matching Rows in B2:B7 | Assignment rows | Alphabetical Roster Output |
|---|---|---|---|
| Core | Maya, Zoe, Ethan | 3 | "Ethan, Maya, Zoe" |
| Growth | Liam, Aria | 2 | "Aria, Liam" |
| Infra | Noah | 1 | "Noah" |
| Legal | 0 matches | 0 | No members |
Alphabetical comma-separated member list for team D2
SORT alphabetizes filtered names automatically before TEXTJOIN concatenates them.
=IF(OR(COUNTBLANK(A2:B7)>0,COUNTBLANK(D2)>0),"Invalid inputs",TEXTJOIN(", ",TRUE,SORT(FILTER($A$2:$A$7,$B$2:$B$7=D2,"No members"))))Matching assignment count
The same literal equality as FILTER keeps the count consistent with the roster, including team names containing wildcard characters.
=IF(OR(COUNTBLANK(A2:B7)>0,COUNTBLANK(D2)>0),"Invalid inputs",SUMPRODUCT(--($B$2:$B$7=D2)))Try the example
Use the original values or change them to test your understanding.