Merge multiple columns into one?

Greyson Harness
edited 04/21/21 in Formulas and Functions

Hello! I am trying to put together a form through which my staff can request the use of volunteers. To make this form as easy as possible, I've included a question about which department is requesting. Based on logic, that will take the requester to a dropdown with their most common volunteer requests. In other words, every department has its own drop down menu; to do this, I duplicated my "what do you need volunteers for?" question and changed the drop down menu for each department.

The issues I'm running into is that the sheet represents each question as its own column. I would like to merge all columns of "what do you need volunteers for?" The way I have the logic set up, each row will only have one of the 8 different "what do you need volunteers for?" columns filled. Is there a way to merge two columns into one or have one column take the data contained within another column and copy it? In Google Sheets or Excel I'd use a query function. Does anything like that exist in Smartsheet?

Best Answer

  • MCorbin
    MCorbin Overachievers Alumni
    Answer ✓

    You can use Join

    =JOIN([Dept 1 Volunteers]@row:[Dept 4 Volunteers]@row, ", ")

    In this case, you're getting an extra comma where the columns are blank. You could just put spaces between values.

    If it's a multi-select column, you'll get all the contents of the column before the comma (they're not separated).

    Or - to eliminate the extra commas

    =IF(ISBLANK([Dept 1 Volunteers]@row), "", [Dept 1 Volunteers]@row + ", ") + IF(ISBLANK([Dept 2 Volunteers]@row), "", [Dept 2 Volunteers]@row + ", ") + IF(ISBLANK([Dept 3 Volunteers]@row), "", [Dept 3 Volunteers]@row + ", ") + [Dept 4 Volunteers]@row


    Where do I sign up for "Cat Socializing?" 😻

Answers

  • MCorbin
    MCorbin Overachievers Alumni

    If it's on a form, you can use the Form Logic to display the 2nd column based on what's entered in the first column:


  • Greyson Harness
    edited 04/22/21

    @MCorbin,

    Thank you for your response! I don't think I was very clear with my question. The way you are suggesting to use the form is how I have it set up currently. But, when I go to the sheet connected to the form, the various dropdowns from Dept 1 Volunteers, Dept 2 Volunteers, etc. all have their own columns. I'd like to merge those columns.

    I'm including a photo to hopefully better represent what I am trying to do. The cells highlighted in yellow are the separate columns I'm receiving for different departments. I'd like to take those two columns and merge them into one column, the cells highlighted in red.

  • MCorbin
    MCorbin Overachievers Alumni
    Answer ✓

    You can use Join

    =JOIN([Dept 1 Volunteers]@row:[Dept 4 Volunteers]@row, ", ")

    In this case, you're getting an extra comma where the columns are blank. You could just put spaces between values.

    If it's a multi-select column, you'll get all the contents of the column before the comma (they're not separated).

    Or - to eliminate the extra commas

    =IF(ISBLANK([Dept 1 Volunteers]@row), "", [Dept 1 Volunteers]@row + ", ") + IF(ISBLANK([Dept 2 Volunteers]@row), "", [Dept 2 Volunteers]@row + ", ") + IF(ISBLANK([Dept 3 Volunteers]@row), "", [Dept 3 Volunteers]@row + ", ") + [Dept 4 Volunteers]@row


    Where do I sign up for "Cat Socializing?" 😻

  • @MCorbin that works perfectly! Thank you very much for your help

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!