Is there any way to get good data from multi-select columns/cells in Pivot?

I've been working to use Pivot & Data Mesh to replace most, if not all, of my complex "summary sheets" where I have tons of cross-referencing formulas to pluck and calculate all the information I need from source sheets to trend KPIs. I love that pivot tables are dynamic, and update at my chosen cadence. For example, it will add in a row or column with any NEW values added to the list I'm looking at; as opposed to one of the Admins having to remember "oh if I add a new value and we have any charts on any dashboards that look at this list, I have to go find the widget data source and add this value manually" etc.

A source of frustration has been that if I have a dropdown list column where multi-select is allowed, I end up with unique rows or columns for every combination the user selected. In my summary sheets with formulas, I was able to use CONTAINS or HAS to accurately count. With the Pivot table I can't.

Here I'm showing an existing summary sheet I have which looks for and counts using HAS:

Versus here using Pivot, the one "Doc Updates (Ref Docs, WIs)" category has tons of rows, one for each unique combination (and is not only not what I need, but I can't figure out a way to get what I need from it!):

I'm positive I'm not the first person to run into this and I wanted to see what the community has done to overcome this limitation of Pivot App functionality.

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

When it comes to multi-select columns, I use sheets with formulas and cross sheet references to build out my charts. You can use formulas to pull in all of the unique entries and even leave space for more to allow others to add new unique entries without having to go to the metrics sheet(s) and update there as well. There are a number of solutions for this already out here in the Community, but the basic idea is that you bring the list of unique values into the metrics sheet, filter out duplicates, then parse the list down a column. Using an IFERROR will leave additional rows blank that will then be filled in as new unique values are added. You can use a report pulling from the metrics sheet to only show rows that aren't blank to make sure your chart stays clean as well.

Paris.TechProjSpec

I believe that summarizes what I'm already doing.

To clarify, in the example I provided above, if a new "Task Category" value was added to the dropdown list in the source sheet, how would the Primary column get updated to include the new Task Category value? I feel confident in my ability to leverage formulas and I do understand what you suggested above, but it doesn't solve the issue with the sheet having a static, manually populated list to reflect the dropdown list values. I hope I'm making sense here. Thanks for your input!

Paul Newcome

That's what I am saying. You can use a formula to grab the unique entries from a column and output them into their own cells going down another column (removing duplicates.

The formula typically used for that will throw an error in situations where (for example) there are only 10 unique entries but the output formula is dragged down 15 rows. It will throw an error on those last 5. You can use an IFERROR to just make those blank, and when an 11th unique entry is added to the source data, it will automatically be pulled into the 11th row populated by the output formula. Basically using automated placeholders.

In the screenshot below, the List column is the one with the distinct list pulling from the People column. The formula in the List column is a column formula, but it leaves rows 13+ blank until a 13th unique name is added to the People column.

This same theory can be applied to a multi-select column with only a little bit more creativity. The specifics of how depend on the specifics of what you have.

But this allows for "placeholders" in your metrics sheet(s) so that you don't have to keep remembering to update them every time a new unique entry is made.

Paris.TechProjSpec

Thank you for explaining further! I think I'm honing in on what you're recommending, however I'm still struggling with the multi-select aspect.

My current formula: =IFERROR(INDEX(DISTINCT({Task Category - Complete DC Admin/Srvc Onboard}), [Row Number]@row), "")

But this is returning combinations of values as unique items in the index list (which makes sense, so "Task Value 1 Task Value 2" if both selected in a cell, are returning in my list) and I can't figure out the creativity needed to make the new formula column identical to the list of values in the source dropdown list column.

For instance, in your example above, if Easter Bunny and Tooth Fairy were both in a cell, "Easter Bunny Tooth Fairy" would pull into the list but that is the same as the Pivot output I'm trying to get around.

Further suggestions?

Paul Newcome

You will need to browse the various parsing solutions here in the Community. There are a number that deal specifically with multi-select columns. As indicated in my last comment, the same THEORY would apply to multi-select columns, but the exact solution depends on some of your specifics / variables.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.