Determine the difference in Multi-Select drop down fields

QUA

I have two fields that have multi select drop down fields. Field 1 contains options A, B, and C. Field 2 has options A, C. In a third field, I want to determine what is missing in Field 2. The Answer should be: B. How do I do that?

Craig

Paul Newcome

You would have to have all possible selections listed out in a column somewhere first.

Then you could use this:

=JOIN(COLLECT([All Possible Selections]:[All Possible Selections], [All Possible Selections]:[All Possible Selections], NOT(HAS([Selected Options]@row, @cell))), CHAR(10))

Just update the ranges to match whatever column name you have the selections in or as cross sheet references if you are using a different sheet for the list.

Kleerfyre

A formula could do this. Will the three options always be the only options? Something like this could work:

=IF([Field 2] = "A,C", "B", IF([Field 2] = "A,B", "C", IF([Field 2] = "B,C", "A")))

If Field 2 could be just A, B, or C, you would need to add those as well.

QUA

Thanks for looking. For this case, I don't think that will work for what I need. Either field could have 10 or more options selected. In the simple example below (expanded a bit from the initial question), I only show 3 values in each of Field 1 and Field 2. I need to be able to determine what was in field 1 that is not in Field 2. In this example the answer ([In Field 1 but not Field 2]) should still include only B. In reality there could be several items in the answer column or none at all.

Paul Newcome

You would have to have all possible selections listed out in a column somewhere first.

Then you could use this:

=JOIN(COLLECT([All Possible Selections]:[All Possible Selections], [All Possible Selections]:[All Possible Selections], NOT(HAS([Selected Options]@row, @cell))), CHAR(10))

Just update the ranges to match whatever column name you have the selections in or as cross sheet references if you are using a different sheet for the list.

QUA

Thanks Paul,

I discovered the same solution after I posted and yes it works.

Craig

Paul Newcome

Happy to help. 👍️