Joining multiple select columns into one column

Options

Hi,

I have 2 columns that are multi-select and one column that a text column. I am looking for a formula to be able to get them all in one multi-select column.

I want multi-select column Missing 1 and multi-select column Missing 2 and text column ID problems to all combine in the multi-select column Missing.

I used the formula =[Missing 1]@row + char(10) + [Missing 2]@row + char(10) + [ID Problem]@row

But it seems that anytime one of the columns is blank I get #INVALID OPERATION error message.

I feel like there is an easy fix that I just can't seem to figure out. All help is appreciated.

Tags:

Best Answer

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Chananya

    I hope you're well and safe!

    Here are two ways to structure it.

    =[Missing 1]@row + CHAR(10) + [Missing 2]@row + CHAR(10) + [ID Problem]@row
    
    • If you could move the ID Problem column so it's after the Missing 2 column we could use the following formula instead:
    =JOIN([Missing 1]@row:[ID Problem]@row, CHAR(10))
    

    Would that work/help?

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Answers

  • Intern98
    Intern98 ✭✭✭
    Options

    Hi Chan,

    you can try

    =IFERROR(([Missing 1]@row + char(10) + [Missing 2]@row + char(10) + [ID Problem]@row),"X")

    The cell should return X if the formula is incomplete, you can replace X with '0' or leave it blank,

    ; =IFERROR(([Missing 1]@row + char(10) + [Missing 2]@row + char(10) + [ID Problem]@row),""),

    as required in your workflow.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Chananya

    I hope you're well and safe!

    Here are two ways to structure it.

    =[Missing 1]@row + CHAR(10) + [Missing 2]@row + CHAR(10) + [ID Problem]@row
    
    • If you could move the ID Problem column so it's after the Missing 2 column we could use the following formula instead:
    =JOIN([Missing 1]@row:[ID Problem]@row, CHAR(10))
    

    Would that work/help?

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!