Formula for validating data between columns
I have two columns that have the same dropdown data. I'd like to be able to make it so that when you check the data in one, it removes it from the other (basically one is things you have, the other is things you need). Is there an easy way to create column formulas to validate between the two?
So in the example above, when I have Bananas, it removes it from the Need column.
Answers

Hi @Eric Larsen
First, we need to get each element of the Have and Need. And compare the elements.
But handling multiple dropdown lists in Smartsheet is cumbersome Text manipulation.
Below is a demo solution that uses Text functions like the SUBSTITUTE, LEFT, and FIND functions to get each element of the multiple dropdown list and functions like the CONTAINS to get the Validate list.
Formulas
Validate =JOIN(DISTINCT([Validate 1]@row:[Validate 4]@row), CHAR(10))
Need No. =COUNTM(Need@row)
Need 1 =IF([Need No.]@row = 1, Need@row, LEFT(Need@row, FIND(CHAR(10), Need@row)))
Need 2 =IF([Need No.]@row = 2, [Need List 1]@row, LEFT([Need List 1]@row, FIND(CHAR(10), [Need List 1]@row)))
Need 3 =IF([Need No.]@row = 3, [Need List 2]@row, LEFT([Need List 2]@row, FIND(CHAR(10), [Need List 2]@row)))
Need 4 =IF([Need No.]@row = 4, [Need List 3]@row, LEFT([Need List 3]@row, FIND(CHAR(10), [Need List 3]@row)))
Need List 1 =SUBSTITUTE(Need@row, [Need 1]@row, "")
Need List 2 =IF(ISTEXT([Need 2]@row), SUBSTITUTE([Need List 1]@row, [Need 2]@row, ""))
Need List 3 =IF(ISTEXT([Need 3]@row), SUBSTITUTE([Need List 2]@row, [Need 3]@row, ""))
Validate 1 =IF(ISTEXT([Need 1]@row), IF(NOT(CONTAINS([Need 1]@row, Have@row)), [Need 1]@row))
Validate 2 =IF(ISTEXT([Need 2]@row), IF(NOT(CONTAINS([Need 2]@row, Have@row)), [Need 2]@row))
Validate 3 =IF(ISTEXT([Need 3]@row), IF(NOT(CONTAINS([Need 3]@row, Have@row)), [Need 3]@row))
Validate 4 =IF(ISTEXT([Need 4]@row), IF(NOT(CONTAINS([Need 4]@row, Have@row)), [Need 4]@row))It would be straightforward if we could use Python.
set_a = {"bananas", "apples", "pears"}
set_b = {"oranges", "pears", "apples"}
# Elements in set_a but not in set_b
result = set_a  set_b
print(result) # Output will be {'bananas'}Integrating Python into Smartsheet, like Microsoft did with Excel, could be a great idea.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.4K Get Help
 364 Global Discussions
 200 Industry Talk
 429 Announcements
 4.4K Ideas & Feature Requests
 137 Brandfolder
 129 Just for fun
 128 Community Job Board
 446 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!