Formula for validating data between columns

Eric Larsen

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.

jmyzk_cloudsmart_jp

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.

https://app.smartsheet.com/b/publish?EQBCT=4ad47f6ada5344fcbff9f6b96a060434

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.