How to remove duplicate items from drop down columns.

batomlinson1

As a basic outline, I have column 1, 2, and 3. Columns 1 and 2 list 4 identical options: A, B, C, D in a drop down menu.

Essentially, I would like column 3 to calculate/display the difference between columns 1 and 2, removing a duplicate. For example:

Column 1: A, B, C, and D are selected in the drop down

Column 2: B is selected as the item to remove from the list in Column 1

Column 3: I need a formula that takes the list from Column 1 (A, B, C, D) , removes the item indicated in Column 2 (B), to leave a list without the item indicated in Column 2 (A, C, D).

Simply put - I want Column 3 to display the result from Column 1 (C1) minus - Column 2 (C2) = to equal (C1-C2). This is all text data, not numerical so it is not simple math.

I hope that makes sense! There is a simple "+" calculation to combine text data, but no simple calculation to remove duplicate data.

Thank you!

jmyzk_cloudsmart_jp

Hi @batomlinson1

If it is OK to create a separate sheet with the options, here is a solution that uses the JOIN, COLLECT, HAS, and CHAR functions.

=JOIN(
  COLLECT(
    {Multi Dropdown List}, 
      {Multi Dropdown List}, 
        AND(
          HAS([Column2]@row, @cell), 
          NOT(HAS([Column3]@row, @cell))
          )
        ),
   CHAR(10))

The above formula references the {Multi Dropdown List} column range shown below, and first collects the range whose values are in Column 2 but not in Column 3, then joins the value with CHAR(10) to create a multiple-dropdown list cell value.

The JOIN text operation is possible because MULTI_PLICLIST is added to the TEXT_NUMBER column type in version 2. So, it is internally a TEXT_NUMBER but behaves as a MULTI_PICKLIST by setting the column to version 2 and applying the proper format, in this case, delimited by CHAR(10).

https://developers.smartsheet.com/api/smartsheet/openapi/columns/column

versionnumber

0: CONTACT_LIST, PICKLIST, or TEXT_NUMBER.

1: MULTI_CONTACT_LIST.

2: MULTI_PICKLIST.

Enum 0 1 2

Option Sheet

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

Look Up Sheet

{Multi Dropdown List} range