Assistance Required for Comparing Multi-Select Dropdown Columns

Bigbigrick

I am seeking assistance with comparing two multi-select dropdown columns in my Smartsheet. Specifically, I have two columns:

• Column A: Options selected are "a", "b", "c".
• Column B: Options selected are "b", "c".

I need a new column to identify the options that are present in Column A but not in Column B—in this case, option "a".

Could you please advise on the best approach to achieve this comparison within Smartsheet?

Thank you for your support.

jmyzk_cloudsmart_jp

Hi @Bigbigrick

Assuming you have the same list in columns A and B, you can use the following method to get "the options that are present in Column A but not in Column B."

1. Prepare an option sheet like the one at the bottom.

2. Use the following formula;

=IF(
    AND(
        HAS(A@row, INDEX({Options}, 1)),
        NOT(HAS(B@row, INDEX({Options}, 1)))
    ),
    INDEX({Options}, 1) + CHAR(10),
    ""
) +
IF(
    AND(
        HAS(A@row, INDEX({Options}, 2)),
        NOT(HAS(B@row, INDEX({Options}, 2)))
    ),
    INDEX({Options}, 2) + CHAR(10),
    ""
) +
IF(
    AND(
        HAS(A@row, INDEX({Options}, 3)),
        NOT(HAS(B@row, INDEX({Options}, 3)))
    ),
    INDEX({Options}, 3) + CHAR(10),
    ""
) +  and so on ..

Up to seven opiton case
=IF(AND(HAS(A@row, INDEX({Options}, 1)), NOT(HAS(B@row, INDEX({Options}, 1)))), INDEX({Options}, 1) + CHAR(10), "") + IF(AND(HAS(A@row, INDEX({Options}, 2)), NOT(HAS(B@row, INDEX({Options}, 2)))), INDEX({Options}, 2) + CHAR(10), "") + IF(AND(HAS(A@row, INDEX({Options}, 3)), NOT(HAS(B@row, INDEX({Options}, 3)))), INDEX({Options}, 3) + CHAR(10), "") + IF(AND(HAS(A@row, INDEX({Options}, 4)), NOT(HAS(B@row, INDEX({Options}, 4)))), INDEX({Options}, 4) + CHAR(10), "") + IF(AND(HAS(A@row, INDEX({Options}, 5)), NOT(HAS(B@row, INDEX({Options}, 5)))), INDEX({Options}, 5) + CHAR(10), "") + IF(AND(HAS(A@row, INDEX({Options}, 6)), NOT(HAS(B@row, INDEX({Options}, 6)))), INDEX({Options}, 6) + CHAR(10), "") + IF(AND(HAS(A@row, INDEX({Options}, 7)), NOT(HAS(B@row, INDEX({Options}, 7)))), INDEX({Options}, 7) + CHAR(10))

https://app.smartsheet.com/b/publish?EQBCT=5881873a76b742ed860e0670298d2a5b (You can test how this formula works in the editable published sheet with this link)

• HAS(A@row, INDEX({Options}, n)): This part checks if a specific option (from your {Options} list) is one of the selected items within the Multi-Select Dropdown in cell A@row. Smartsheet stores multiple selections in a single cell, separated by newline characters. The HAS function correctly identifies if that specific option string exists within the cell's text.

• NOT(HAS(B@row, INDEX({Options}, n))): Similarly, this checks if that same specific option is NOT one of the selected items in the Multi-Select Dropdown in cell B@row.

• The IF condition as a whole: Therefore, each IF statement identifies if a particular option is selected in the A@row Multi-Select Dropdown but not selected in the B@row Multi-Select Dropdown.

• INDEX({Options}, n) + CHAR(10): If the condition is true, the formula takes that specific option and appends a newline character (CHAR(10)). This prepares it to be correctly displayed as a separate item in another Multi-Select Dropdown or a text field where newline separation is expected.

• The final concatenated string: The + signs combine all the options that meet this "unique to A" criteria, separating each option by a newline.

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