Way to use grouping in reports with a multiselect column

Kimberly Loveless

Is there a way to group a report using a multiselect column?

I have spent a while going thru the community posts going back to when the grouping in reports was released and see that this is something that has been asked about repeatedly with no solution or real work around.

We have some sheets that require this functionality to get the report outs that we really need but it isn't feasible to have a bunch of helper columns just for this report. Is there a work around that doesn't require lots of helper columns but still lets each individual value in the multiselect column be its own group.

** Because of the way the data is collected restructuring the form is not an option**

MVP OPS

Hi @Kimberly Loveless

Depending on how consistent your data is that you are wanting to group you could create a single drop down column in your sheet that has the options of how you want to group your rows in your report. Then create a nested if statement using If, AND, and HAS formulas to assign the value for the grouping.

Hope this helps.

Paul Newcome

Unfortunately we are not able to group by multi-select dropdown or contact list type columns.

Are you needing to show line items, or are you wanting to group so that you can leverage the summary portion to generate counts/averages/etc.?

Kimberly Loveless

@Paul Newcome there are use cases for both.

Paul Newcome

To show the individual line items for informational purposes in a report, you would have to go with something along the lines of what @MVP OPS suggested where you have a helper column with a formula that outputs some kind of grouping value.

For the metrics, you would first create your separate metrics sheet and include a multi-select column. In any cell within this column you would use a JOIN function with a cross sheet reference to join the entire multi-select column from the source sheet into a single cell (use CHAR(10) as the delimiter). The way a multi-select column functions, this will essentially filter out the duplicates and provide you a single string that has one of each entry listed in it.

From there you can find a number of different parsing solutions here in the community to parse that single cell out into separate cells within the same column and then use your metrics based formulas with cross sheet references to pull in your counts/averages/etc..

Jake Wolfe

I think the ability to group a column with multiple entries really needs to become a function in SS reporting. i.e. group by "column 123 -> contains -> x" or something conditional like group by "column 123" + condition: "column 123" contains "x" kind of like the logic for automations