Populate a cell with all corresponding values in a multi-select drop down cell

Josh Robichaud

Hello,

I have a column in my sheet that is populated by a multi-select drop down in a form ("Violation" column below). Once submitted, the cells in this row will contain at least one, but typically multiple entries from the multi-select drop down. Once submitted, I would like two other cells to populate with the subcategory and category assigned to the each value in the submitted cell (named respectively below). Ideally I would like each subcategory and category column to contain all of the associated data (including duplicates) delimited by commas (as seen below).

The subcategory and category are found in a separate reference sheet assigned to each option available in the multi-select dropdown.

The issue is that the multi-select cell contains each of the selections. So a VLOOKUP against the cell only returns the first value. I have attempted to do a JOIN and COLLECT but I am unable to get the reference table properly integrated. Although not ideal from a database management perspective, I have tried to split the "Violation" column into individual cells and then run the subcategory and category off of that. Unfortunately, the multi-select column is space/return delimited so it will not separate correctly. Even if this did work, it would result in a ridiculous amount of extra cells to capture this information and would need to be structured to have a separate column for each entry, which could be anywhere from 1 to about 6.

Once properly configured, I will then run a COUNTIF(CONTAINS) to sum the total number of entries by violation, subcategory, and category in the data sheet, which will then populate a chart in a dashboard.

Therefore, I am looking for the formula I would need to insert into the Subcategory and Category columns of the Data Sheet to return the results as they appear in the Data Sheet clip above.

I come from an Access background, so in an ideal world this would be accomplished through a series of joined referenced tables, against a single entry with child rows, rolling up to a report. But given that the only efficient way to capture this data in SS is through the multi-select field (populating a single cell), I am looking for a way to work within those constraints.

My fall back option is to put the Subcategory and Category data with the Violation in the dropdown option. This will keep all of the data I need in a single cell that can be queried through COUNTIF(CONTAINS), but creates issues for data consistency through duplicate names. Nevermind the fact that the input form will look like a mess.

Any help is greatly appreciated.

Thank you.

Andrew Stewart

Hi Josh,

I suggest you forget the two formula columns in the data sheet, they do not add any value. The important part is your desired result sheet.

The violation total formula becomes COUNTIFS({Data Sheet Violation column reference}, CONTAINS(Violation@row,@cell)), as you suggested.

Now you have a simple subtotalling exercise because there is only one entry per cell. The formula for subcategory total is a sumifs of the violation total column with criteria a vlookup of the subcategory of the violation matches the subcategory. Same thing applies for the Category.

Personally, I would go with your fall back option anyway, I think that it would simplify data entry for users if there are many options to choose from and they do not know the exact list of all the violations, because as they start to type in the box the options will narrow down. EG: CAT-SUB-Violation format. We do this for selections for product type from a list of 100 or so, and it usually only takes one or two keystrokes to get the right option to click on visible, otherwise there is a lot of scrolling around.

Regards,

Andrew

Josh Robichaud

Andrew,

Thank you very much. I guess I was so focused on getting the data into the columns that I lost track of my end goal and missed your much more simple and elegant solution!

While I think I will fall back on changing the data entry form, could you provide the formula you are describing in your third paragraph for my own education? I am following the logic, but I am having trouble getting the nested VLOOKUP correct:

SUMIF(Violation:Violation, VLOOKUP(Subcategory Cell, Reference Table Range, 2), ViolationTotal:ViolationTotal)

I moved the subcategory column in the Reference table to the far left to accommodate the VLOOKUP, but I am still getting a #NO MATCH error.

Thank you again for your help with this.

Josh