Determine the difference in Multi-Select drop down fields

Options

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

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    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.

Answers

  • Kleerfyre
    Kleerfyre ✭✭✭✭✭✭
    Options

    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.

    Jonathan Sanders, CSM

    "Change is always scary because it is unknown, but facing the unknown is what makes us stronger."

  • QUA
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    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
    Options

    Thanks Paul,

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

    Craig

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!