How to compare two multi-select drop list columns that display the differences?

Luis Salguero

In a Smartsheet sheet, there are the following:

The A field is a multi-select drop list column type. The A@row cell has the values 'Apple', 'Lime', 'Orange', and 'Pear'.

The B field is a multi-select drop list column type. The B@row cell has the value 'Apple'.

The B-A field is a multi-select drop list column type. 

What would be the column formula for the B-A@row cell that displays the missing values between the cells B1 and A1? Also, what would be the column formula for A-B@row?

Thank you so much!

Genevieve P.

Hi @Luis Salguero

There currently isn't a direct function that is able to subtract multiple values from another cell with multiple values in Smartsheet.

However, if your B column will only ever have a single value, we could actually use the SUBSTITUTE Function to subtract that single value from A:

=SUBSTITUTE(A@row, B@row, "")

But this won't work for subtracting A from B, or if the B column has multiple values (see the orange cells where it's incorrect):

Please submit your feature request to the Product team by creating an Idea Post in the Smartsheet Product Feedback and Ideas topic here in the Community. This will allow other users to vote on your enhancement idea!

Cheers,

Genevieve

Genevieve P.

Hi @Luis Salguero

There currently isn't a direct function that is able to subtract multiple values from another cell with multiple values in Smartsheet.

However, if your B column will only ever have a single value, we could actually use the SUBSTITUTE Function to subtract that single value from A:

=SUBSTITUTE(A@row, B@row, "")

But this won't work for subtracting A from B, or if the B column has multiple values (see the orange cells where it's incorrect):

Please submit your feature request to the Product team by creating an Idea Post in the Smartsheet Product Feedback and Ideas topic here in the Community. This will allow other users to vote on your enhancement idea!

Cheers,

Genevieve

Luis Salguero

Hi Genevieve,

I appreciate you looking into my question.

I will submit a request to the Product team by creating an Idea Post in the Smartsheet Product Feedback and Ideas topic.

Thank you so much!

Luis

Luis Salguero

Hi @Genevieve P. ,

I could only subtract multiple values from another cell with multiple values in Smartsheet by extracting them into individual columns.

I collapsed the columns for easy viewing purposes.

For column OA (e.g., Product Sold), the first value is in column A, the second in B, and the third in C, all the way to column AB, for 28 values.

For the second group, column OB (e.g., Product delivered), the first value went to column DA and the last to column EB.

In column AAA (e.g., missing products or OB - OA), the formula see if DA@row:EB@row has A@row. If it’s not there, show A@row, and concatenate with the rest.

The limit on how many values I can extract would be the 4,000-character limit in a cell.

Thank you,

Luis