Details
The goal is to find all names for a given team and return them in a single cell as a comma‑separated list. This is basically a lookup problem, but the twist is that we want to return multiple matches for each team, and we want everything in one cell. A formula like this is useful in many scenarios, including:
- List products by category.
- List classes by student.
- List the staff by project.
What makes the problem unique is that we want to locate multiple matches (i.e., all members of a given team), but we the result to be delivered to a single cell formatted as a clean, readable list (i.e., “Jake, Tracy, Juan”) instead of a block names in different cells.
For convenience, the challenge worksheet contains two named ranges: name (B5:B16) and team (C5:C16). This makes the formulas easier to read and write.
Challenge #1 - List names for each team
Assuming that we already have a list of teams in column E, what formula in column F, when copied down, will return a list of names for each team as shown below?
Challenge #2 - List teams and names with one formula
Assuming that we don’t already have a list of names in column E, what formula entered in cell E5 will return a table that includes both Teams and Names in one step?
Details
A long-time reader sent me an interesting problem this past week. The goal is to end up with a text string like “MWF” for Monday, Wednesday, Friday. The problem is that the weekdays are input as yes/no abbreviations like “NYNYNYN” for “MWF”.
Challenge
What formula will translate the “N” and “Y” to weekday abbreviations as shown in the screenshot above?
Extra points for style and elegance, but workhorse solutions are fine, too :)
Assumptions
- All inputs are 7 characters and contain only “Y” or “N”
- Days are mapped Sunday through Saturday, SMTWTFS.
