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
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!