Grouping by a multi-select column

How to group by a multi-select column so that each group is based on unique items instead of unique combinations of items

Updated over a week ago

<a href="mailto:Coda_Symbol-Tomato_Soup@4x.png" target="_blank" rel="nofollow noopener noreferrer">Coda_Symbol-Tomato_Soup@4x.png</a>

When you group a table by a select list column, and you also have "allow multiple selections" enabled on that column, you'll notice that each group is defined by unique combinations of items, rather than each unique item.

For example, let's say you have a doc for organizing your home movie collection. You can find the doc used for the example here (it's view only, but you can create a copy by clicking on the title of the doc and selecting "copy doc").

You've added multiple tags on each movie:

You then want to see all the movies for each tag. When you group by Tags, each group is based on a unique combination of tags, not the individual tags themselves:

You'll notice that the drama tag is repeated twice in combination with another tag. This is useful for a lot of scenarios, but in your case you'd like to see each tag as a separate group. 

To do this, you'll want to follow 2 steps:

Step 1: Convert your select list  column type into a relation column type, and create a new Movie Tags table:

Navigate to the column type icon on the Tags column, click Select list options, and then Convert to table.


Step 2: Add a FILTER  formula to a new column in the Tags table to see all the movies that contain a given tag.

To break down this formula, We're filtering the Movies table with Contains() to see where each Tag value exists in that table. From there, we're using the dot operator to pull out the text title of the movie from the Movie column, and to clean up the list, I added BulletedList() at the end.

And there you have it! You can now see each tag on a new line, with all the associated movies.

Looking for more general info on grouping other types of columns? Check out this article.

Did this answer your question?