Dropdown multi-select columns list comparision

Kayla H.

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?

Matt Lynn#

@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))

Matt Lynn#

@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))