Dropdown multi-select columns list comparision

Kayla H.
Kayla H.
edited 08/21/24 in Formulas and Functions

I have two columns that are dropdown multi-select. One column includes a list of all possible values (example: letters A-Z) while the second column includes a list of some letters (without duplicates (example: A, B, C). I would like to create a column that displays the missing letters (example: D-Z). How would I go about doing this?

Best Answer

  • Matt Lynn-PCG
    Matt Lynn-PCG ✭✭✭✭✭✭
    Answer ✓

    @Kayla H.

    Do you mean you want to evaluate the full column for any A, B, C etc. and in a single cell list which ones are missing from the entire column or are you only listing the results in one cell and need to evaluate that one cell?

    Two different situations:

    If you're only doing one cell you'd write a 26 step if statement. Something like :

    =IF(HAS(ABC@row, "A"), "", "A") + CHAR(10) + IF(HAS(ABC@row, "B"), "", "B")…. just duplicate each section from the + to the char(10) for each letter.

    If it's evaluating a full column you would do a join formula of the entire column, then evaluate that with the above if() statement series. The join would be something similar to:

    =JOIN(COLLECT(ABC:ABC, ABC:ABC, @cell <> ""), CHAR(10))

    Certified Platinum Partner

    2023 Partner of the Year

    PrimeConsulting.com

Answers

  • Matt Lynn-PCG
    Matt Lynn-PCG ✭✭✭✭✭✭
    Answer ✓

    @Kayla H.

    Do you mean you want to evaluate the full column for any A, B, C etc. and in a single cell list which ones are missing from the entire column or are you only listing the results in one cell and need to evaluate that one cell?

    Two different situations:

    If you're only doing one cell you'd write a 26 step if statement. Something like :

    =IF(HAS(ABC@row, "A"), "", "A") + CHAR(10) + IF(HAS(ABC@row, "B"), "", "B")…. just duplicate each section from the + to the char(10) for each letter.

    If it's evaluating a full column you would do a join formula of the entire column, then evaluate that with the above if() statement series. The join would be something similar to:

    =JOIN(COLLECT(ABC:ABC, ABC:ABC, @cell <> ""), CHAR(10))

    Certified Platinum Partner

    2023 Partner of the Year

    PrimeConsulting.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!