JOIN COLLECT and Multi Select

System · 04/27/22

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

Darrin Kiessling · 04/26/22

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. · 05/03/22

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 withallvalues 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 · 11/11/22

@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. · 11/14/22

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} columnhavethat one selection among others:

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

Cheers,

Genevieve

Conv_Eng · 01/31/23

Hi@Genevieve P.

我有一个类似的问题加入/胶原ect 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. · 01/31/23

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 toenter inmulti-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 explaininghowit isn't working? Are you getting an error or an incorrect result?

Cheers!

Genevieve

Conv_Eng · 01/31/23

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. · 01/31/23

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

JOIN COLLECT and Multi Select

Answers

Help Article Resources

Categories