Merge multiple columns into one?
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
-
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
-
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:
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!