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.
