Information from multiple columns into one

Emma Pettit

I have a sheet that includes department and sub-department data, which is collected from a form. The sub departments all populate into different columns, so i have sub department columns (A, B, C, and D). For each row, there is only information in ONE of those columns because people only have one subdept.

I want to create one master subdept column that combines all of that information so I only have to look at 1 column instead of a bunch.

Currently in the master subdept column I have this formula: =[Division: A]@row + [Division: B]@row + [Division: C]@row + [Division: D]@row

However, because there is only content in either A, B, C, D, not all of them, I'm getting a response that includes a 0 in the master subdept column. So instead of grabbing the response in C (say, "communications"), it says 0Communications, or ones with nothing in any of the columns are returning a 0 rather than a blank cell.

Any ideas how to fix this so that it will populate just "Communications," and will leave the master subdept blank if there is no content in A, B, C, or D?

Andrée Starå

Hi @Emma Pettit

I hope you're well and safe!

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

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!

Paul Newcome

Try this...

=JOIN(COLLECT([Division: A]@row:[Division: D]@row, [Division: A]@row:[Division: D]@row, @cell <> ""))

Emma Pettit

This worked perfectly, thank you so much, Paul!

Paul Newcome

Happy to help. 👍️