Collect items in multiselect cell from one cell if item was not used by second cell
I have a cell (multi-select type) with all possible items listed. I have another cell, that is also multi-select that uses some or all items from cell one. I need to collect all items from cell one that was not used in cell 2 in cell 3.
I feel that It should be somehow Join and collect minus what was used. Please see picture. Any suggestions?
Answers
-
My suggestion would be 20 helper columns. 1 for each of the selections in List 1. These would be text/number type columns.
You would want to use this formula and update it for each of the different selections going across the columns.
=IF(CONTAINS("A", [List 2]@row), "", "A")
This will output a blank if List 2 has "A" in it. Repeat this for the rest of the letters going across those helper columns.
Then in the List 2 column, you would use
=JOIN(COLLECT([A Helper]@row:[T Helper]@row, [A Helper]@row:[T Helper]@row, @cell <> ""), ", ")
-
Paul, thank you for your response. I was trying to avoid creating so many help columns as I have 30 columns as list 1, and 30 columns as list 2. Creating 20 columns for each of those 30 is a bit too much. Especially, I may have to have more in the future.
-
I'm not sure I follow... You have 30 different Multi-select columns that you want to be able to do this for, or you have individual columns listing out the different options in the multi-select column?
Also... What is the reason for having the List 1 column when people are using the List 2 column to make their selections?
-
We have an inspection sheet. Each Column (list 2) represents series of items that pass the inspection. On my other sheet (list 1) have each column has all possible items for that one part of the inspection. My result column should show all items that did not pass the inspection from list 1. Example:
List 1:
Clmn1 = Title: Building List of items: Stairways, Windows, Doors, and etc (I can make it as multi select so it is all in one cell vs each row)
Clmn2 = Title: Parking List of items: Asphalt, Paint, curbs
Clmn.....
List 2:
Clmn1 = Title: Building Multiselect: Stairways, Windows, Doors, and etc (we choose from each multi-select which item passed)
Clmn2 = Title: Parking Multiselect: Asphalt, Paint, curbs (we choose from each multi-select which item passed)
Clmn....
For each column 2 I need a result column that will show which items did not pass inspection (on list 1 but not in list 2)
-
Ah. I didn't realize you were working on two different sheets. Where exactly is the result column going?
-
I have placed the result column in List 2 sheet, but it is not important and I can have it in a new sheet if it helps with the formula.
-
So basically you have Sheet 1 that lists all options per section with each section having its own column. Then you have Sheet 2 where you want to basically select all of the "Pass" options in a section and then have the "fail" options automatically populate in the second column for that section. You could have up to 20 options in a single section, and you have 30 sections.
Does all of that sound right? If so... You've definitely presented quite a challenge. Haha. This is going to take a little bit of time and testing. I will have to play around for a bit and get back to you with what I find.
-
This is correct and thank you so much. I tried so many things, but so far nothing gets me close to the solution.
-
So... I have been unable to find a direct solution to this. The only thing I can think of at this point is restructuring. Are you able to provide more details as to the overall workflow before and after this particular piece?
-
The workflow is like that:
Sheet 1 is used to set up Categories/Sections for the inspection with a list of all possible items for each category (each category has its own column) - could be just multi select with all items listed.
Sheet 2 has 2 columns designated for each category. The first column (lets call it A) is multi-select with all possible items from List 1. Column 2 (lets call it B - should be formula) of everything that was not selected from column 1.
The inspector is required to select from A all the items that failed the inspection for this category. List B will list everything that was not selected.
Last step: The last column in the sheet will be the acknowledgment/certification statement that everything that is listed in B was inspected and in good condition. This is to give the inspector an opportunity to correct list A when reviewing B and see something that should not be there.
At this time I have about 30 categories (A and Bs) but as we are improving our inspections, we may have to add more.
-
Ok. Would you be open to a bit of a restructure? Here is my thought...
If you have access to the underlying sheet(s) then you have access to a report. If you broke it out so that each category had its own sheet (which would allow us to parse out the selections as mentioned above), then you could create a report that only pulls the lists that the inspector will need to see from each of the sheets.
The inspector can then update the report which would flow back to the sheet, the sheet would run its formulas, and then finally the output would be back on the report for the inspector.
Does that sound like an option?
-
It will not work. Each row on Sheet 2 represents 1 property. And we have 275 of them. Also, to make it worse, the inspections are done on qtrly basis and we move a lot of rows around based on other triggers. Thank you for trying, I will submit suggestions to Smartsheet team for enhancement as we have a lot of this kind of scenario with various projects. In a meantime, we will hope that people do review what they select prior to marking the completion.
Help Article Resources
Categories
Check out the Formula Handbook template!
