Join(distinct(collect))) Multi-select column

Robert Meisch

I'm wanting to create a list of Markets for a selection of sites in a multi-select column.

Based on other community entries, I'm using:

=JOIN(DISTINCT(COLLECT({Site Market Range}, {Site list Range}, CONTAINS(Site@row, @cell)), ", "))

This works when there is 1 selection in the multi-select field but as soon as I add more than one site it goes blank as if it doesn't match anything.

I'm guessing the Site@row is look at the cell as 1 block of text and since it doesn't match anything in my list of single entries I am out of luck. Is this true?

I have 70-ish sites some creating all combinations of them would be near impossible.

Thanks.

Robert Meisch

Mgr Dep Ops & Smartsheet Success Team

Sysco

Genevieve P.

Hi @Robert Meisch

You are correct: the formula sees the cell with multi-selections as one full string and is looking for the full match in your reference table. There currently isn't a way for a formula in Smartsheet to read a multi-select cell and parse it out, searching for individual items in a single table. Your reference table would need to have all possible combinations of selections and the output you want for it to match up the value in your "Site" column with the value in your reference sheet.

Please submit your feedback to the Product team through this form, here!

In this instance, it looks like you only have four values to output: North, South, East, and West, is that correct?

If so, what about setting up 4 separate Change Cell Workflows? Each workflow would look for all the selections that could be selected for a Market. Then the output would be that it updates a multi-select cell with that value (without replacing the other values that may be populated in that cell):

See: Change the Value of a Cell in an Automated Workflow

Cheers,

Genevieve

Paul Newcome

Are you able to provide screenshots for reference?

Robert Meisch

Sorry, Juggling solution builds.

Yep

Here is the Lookup table:

And here is the formula sheet:

So, ultimately, I would like all the Markets of selected Sites to show up in the Market.

Thanks for your help.

Genevieve P.

Hi @Robert Meisch

You are correct: the formula sees the cell with multi-selections as one full string and is looking for the full match in your reference table. There currently isn't a way for a formula in Smartsheet to read a multi-select cell and parse it out, searching for individual items in a single table. Your reference table would need to have all possible combinations of selections and the output you want for it to match up the value in your "Site" column with the value in your reference sheet.

Please submit your feedback to the Product team through this form, here!

In this instance, it looks like you only have four values to output: North, South, East, and West, is that correct?

If so, what about setting up 4 separate Change Cell Workflows? Each workflow would look for all the selections that could be selected for a Market. Then the output would be that it updates a multi-select cell with that value (without replacing the other values that may be populated in that cell):

See: Change the Value of a Cell in an Automated Workflow

Cheers,

Genevieve

Robert Meisch

@Genevieve P. Thanks! I think I can make that work.

I will also submit the feedback too.

I appreciate your help.

Robert

Paul Newcome

Have you tried this...

=JOIN(DISTINCT(COLLECT({Site Market Range}, {Site list Range}, HAS(Site@row, @cell)), ", "))

I used this to pull from the TableB column based on matches in the TableA column with the data in the Multi-select column:

SheenaTuer

Hi @Paul Newcome I tried the solution you posted above but I am having trouble making it work.

Can you help me correct it please so I can apply it to my other sheet?

=JOIN(DISTINCT(COLLECT([Column5]:[Column5], Multi:Multi, HAS(Multi:Multi, A@row)), ", "))

I followed the same example you posted.

SheenaTuer

I was able to figure it out, but I used this formula instead:

=JOIN(DISTINCT(COLLECT([Column5]:[Column5], Multi:Multi, CONTAINS(A@row, @cell))), ", ")

Thank you!

Paul Newcome

Glad you were able to get it sorted. The problem in your first one was that the second range should have been A:A and the range inside of the HAS function should have been Multi@row.