I find I often want to have a column that is meant to be one or more choices from a fixed set. E.g. a Codebase field in a dev roadmap spreadsheet, which might have choices like {DB, API, JS, CSS, etc.}. A given project could use one or more options.
The natural way to enter that is just as a comma-separated list:
Project Codebase
Foo JS, CSS
Bar CSS, API, DB
Baz JS
However, that makes filtering and pivoting awkward because each combo is treated as a separate entry. Is there any way around that other than splitting each option out into its own boolean column?
Best Answer
Are there a fixed number of comma-separated lists that would be valid? If so, you could us the Data > Validation option to set up so that the lists are always in the same order, so reporting is more meaningful.
Alternatively, or perhaps even as well as that, you could use additional helper columns to calculate Boolean values based on presence of a given string in the chosen value, using the FIND function.
See an example here: https://docs.google.com/spreadsheets/d/1hhm18rM0_n2YE1n0YT8-ty8o17O7s3kY54GPrSdCsTs/edit?usp=sharing