Dropdown multi-select columns list comparision
![Kayla H.](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
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))
Certified Platinum Partner
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))
Certified Platinum Partner
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.3K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 85 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 302 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!