Dropdown multi-select columns list comparision
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
-
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))
Answers
-
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))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.1K Get Help
- 414 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 141 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 299 Events
- 38 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!