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

Options
Luis Salguero
Luis Salguero ✭✭✭
edited 03/13/23 in Formulas and Functions

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!

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Luis Salguero
    Options

    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
    Luis Salguero ✭✭✭
    edited 04/11/23
    Options

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!