JOIN COLLECT and Multi Select

System

This discussion was created from comments split from: Return multiple cell data with a VLOOKUP or INDEX MATCH.

Darrin Kiessling

Trying something similar to collect, join, and populate full state names (eg, Alaska, Arizona) in a target sheet from a reference sheet based on multi-selected state abbreviations (AK, AZ) in the target sheet:

=IFERROR([HQ Code]@row + CHAR(10) + JOIN(COLLECT({XHQ Code}, {XState}, [HQ Code]@row), " "), ", ")

It must be something obvious, but darned if I know how to fix it. Any hope for me?

Genevieve P.

Hi @Darrin Kiessling

Currently Smartsheet formulas cannot look into a multi-select cell and parse out the individual values to then search for each separate value in a reference column. The formula will look for a matching cell with all values matched in the reference sheet, to then bring back a corresponding cell.

You could set up your reference sheet to have every single possible combination in a multi-select column and then the equivalent text values in a second column, however if you have a large number of states to populate then this could be difficult.

An alternative would be to use multiple IF statements to search for each possibility and return the individual state name written out:

=IF(HAS([HQ Code]@row, "AK"), "Alaska" + CHAR(10)) + IF(HAS([HQ Code]@row, "AZ"), "Arizona" + CHAR(10)) + IF(HAS(.... etc.

Cheers,

Genevieve

gwson

@Genevieve P. I saw this reply from several months ago, do you know if there is progress for the formulas to parse out individual values from a multi-select cell? Specifically looking for this to work.

=JOIN(Collect({fullname},{strengths},strength@row))

{strengths} is a multi-select with up to 30 items

Genevieve P.

Hi @gwson

There isn't a way to parse out multiple values in one cell into multiple rows, or into individual matching values, no. However for your formula, it sounds like you need the opposite.

If {strengths} is multi-select but strength@row is a single value, you can use the HAS function to see if the cells in the {strengths} column have that one selection among others:

=JOIN(Collect({fullname},{strengths}, HAS(@cell, strength@row)), ", ")

Cheers,

Genevieve

Conv_Eng

Hi @Genevieve P.

I'm having a similar problem getting the Join/Collect function to work using a multi-select cell. I tried using the HAS() function you described, but it doesn't seem to be working. I'm actually using two sheets in my application. I am comparing 'Sheet 1' and 'Sheet 2' to see if two criteria are met (Project Name and Revision). If these two criteria are met, my goal is to collect every row in the 'enter design comment(s)' column in 'Sheet 1' and join them into one cell in the appropriate 'design comment(s)' column in 'Sheet 2'. I've included the formula I tried using (without success below). I tested the overall functionality of the formula by trying entries that aren't included in the drop-down multi-select option and it worked. I just can't seem to get it to work for the multi-select portion. Hope my description below makes sense.

=JOIN(COLLECT({Design Comments}, {Project Name}, HAS(@cell, [Project Name Description]@row), {Revision}, =[Revision Log]@row), " | ") - This formula is in the 'Design Comment(s)' cell of 'Sheet 2'

Sheet 1.pdf

Sheet 2.pdf

Notes:

{Design Comments} - Referenced from 'Sheet 1'

{Project Name} - Referenced from 'Sheet 1'

HAS(@cell, [Project Name Description]@row) - Referenced from 'Sheet 2'

{Revision} - Referenced from 'Sheet 1'

=[Revision Log]@row) - Referenced from 'Sheet 2'

Genevieve P.

Hi @Conv_Eng

Your formula is structured correctly! However based on your screen captures I can't see any multi-select columns.

Is the {Project Name} column from Sheet 1 mulit-select? If so, your formula is perfect!

=JOIN(COLLECT({Design Comments}, {Project Name}, HAS(@cell, [Project Name Description]@row), {Revision}, [Revision Log]@row), " | ")

If you were meaning that you want to enter in multi-select values, then instead of using " | " as a separator in your JOIN function you can use CHAR(10) like so:

=JOIN(COLLECT({Design Comments}, {Project Name}, HAS(@cell, [Project Name Description]@row), {Revision}, [Revision Log]@row), CHAR(10))

If this isn't what you meant, would you mind explaining how it isn't working? Are you getting an error or an incorrect result?

Cheers!

Genevieve

Conv_Eng

Hi @Genevieve P.

The {Project Name} column from Sheet 1 is indeed the multi-select. I was able to get it to work! The project name description from sheet 2 did not exactly match the options from the multi-select field because I inadvertently added a space somewhere along the way. Thanks for the help. It works perfectly now!

Genevieve P.

Amazing, I'm so glad to hear it works for you now! 🙂

lem_g

Update: I actually used a different formula listed in this thread that I didn't initially try and it worked!

The formula that worked:

=JOIN(COLLECT({Task ID Range}, {Standards Range}, HAS(@cell, Document@row)), " | ")

Hi! I believe I have a similar situation to this but cannot get multiple values to pull into one cell. I am working on a project for policy updates where every month we get a list of documents (called Standards) that will be updated, and I want to compare the update list (Monthly Standard Updates sheet) to the master list of affected tasks that use these standards (Task Matrix sheet). 

Monthly Standard Updates.pdf

Task Matrix.pdf

I would like the output to go in the Task ID Standard Match Column (Monthly Standard Updates sheet).

I need the Document column (Monthly Standard Updates sheet) to be compared to the Standards column (Task Matrix sheet), which is a multi-select dropdown, and if there is a match, to input the Task IDs (Task Matrix sheet), into the Task ID Standard Match Column (Monthly Standard Updates sheet) and there can be more than one affected task. 

So far, this is the only formula that has worked for me is below, but it only pulls the first value, and I cannot figure out how to use the JOIN formula in conjunction to this formula to give me the results I would like.

=IF(CONTAINS(Document@row, {Standards Range}), INDEX(COLLECT({Task ID Range}, {Standards Range}, CONTAINS(Document@row, @cell)), 1))

Any help will be greatly appreciated! Thank you 🙂

Genevieve P.

Hey @lem_g

Thanks for posting your solution! 🙂