Workaround for Grouped Reports with multi-select dropdowns

Mic

Hello, I know that currently Grouping in Reports is available only for single-select dropdown lists but I want to share my process so maybe we can find a workaround!

The source file, is a sheet that picks data from a form. I just made an easy example:

Then, I need to create a grouped report that shows the "Task" column but I'm unable to do that because that column is a multi-select dropdown.

There are any workaround to solve this?

Thanks

Genevieve P.

Hi Mic,

How many multi-select values do you have in this column, and how are you looking to Group them?

We could convert what's displayed in your Multi-Select into a Text/Number column, which would create a single unique value to Group By.

However keep in mind with this formula we couldn't separate out the values any more than having a space between them ("Task 1 Task 2", versus "Task 1 / Task 2"...etc).

Then you can GROUP by this column and hide it in the Report to only show the Task column:

Would this work for you?

Cheers,

Genevieve

Frank S.

@Genevieve P

I saw you post above and I was wondering if there was a way to separate out each value, so group based upon a single value.

Genevieve P.

Hi @Frank.Smith

Can you explain your specific situation a little more, perhaps with screen captures of the sheet (but please block out sensitive data). How many multi-select values do you have?

There isn't a way to automatically parse out a multi-select cell into multiple other cells (to then Group in a Report). However if there are only a select few values you're looking for, we could use a helper column with a formula to search the multi-select cell and see if it HAS that specific value. Then you can use the helper column to Group by.

You would build out in the formula what the most important order is, so if both Task 1 and Task 2 are selected, you would tell your formula to only return Task 1 because it's the one you want to group by. Does that make sense?

Cheers,

Genevieve

Berto D

In the example above (in the "Group" column), is there a way to insert a comma between the words "Task 1" and "Task 2"?

Genevieve P.

Hi @Berto D

Yes! You can use the SUBSTITUTE Function to replace any instance of a hard return, CHAR(10), which what separates each value in a multi-select, with a comma.

E.g:

=SUBSTITUTE(Tasks@row, CHAR(10), ", ")

Cheers,

Genevieve

Berto D

You're always there for me, Genevieve! Thank you!

Genevieve P.

Haha no problem! Glad I could help 😊

Nel Cairns

So I believe I found a workaround to allowing for grouping by multiselect contact or dropdown fields:

Say you have two sheets with "Assigned to" of type Contact List as one of the column headers. - One is configured to allow for multiple entries, the other is not:

1. Create your report so that you only select the sheet where multiple entries are not allowed for the Assigned To field.
2. Go to "group" and you should see "Assigned to" in the list of available fields. Select it. and save your report.
3. Then add the second sheet (where multiple selections are allowed for "assigned to") as a source sheet in the report. When you refresh , you should get a message pop up:

Note: - Down side: the Group by will say "Multiple Contacts" instead of the actual name where data is displayed from a sheet where "multiple entries" are allowed for the field in question.

For groups where at least one row entry is from a sheet where multiple entries is not allowed,", the actual group by field name will show:

In the example below the "Test Group Report change to SS" sheet has "contact List - MS" set to allow Multiple selections. while "Test Group Reporting" sheet's "Contact List - MS" column is configured not to allow multiple selections.

If you expand your report and display the group by field, then you can see the entries for those groupsing that show "Multiple contacts".

Hope this helps others.