Need to concatenate multiple columns across multiple rows based on criteria in a single column

Nate H

Hey Smartsheet Community!

I'm hoping everyone isn't on Thanksgiving break yet. :) I've looked through the posts and found nothing I can adapt for my use case.

I have 4 columns ([Suggested ICD-10 Code], [Code Description], [Type of Finding], and [Support]) that I'd like to join with a "; " or ", " but I need to do it from multiple rows where the [ChartID] is the same and preferably break the join between rows with a line break.

I tried AI and it gave me the formula below but that gives me a #invalid operation error:

=JOIN(COLLECT([Suggested ICD-10 Code]:[Suggested ICD-10 Code], ChartID:ChartID, ChartID@row) + "; " + COLLECT([Code Description]:[Code Description], ChartID:ChartID, ChartID@row) + "; " + COLLECT(Support:Support, ChartID:ChartID, ChartID@row), ", ")

Thanks in advance for the help!

Carson Penticuff

I am having some trouble following exactly what you want to appear as a result of the join. Is this what you had in mind for a result, or am I misinterpreting your request?

Edit: Adding a possible solution below in case this is what you are looking for.

I can't come up with any way to do this without using a helper column. (There may be one, but I imagine it would be much more complex) If this works for you, the helper column can just be hidden.

Helper Column:

=[Suggested ICD-10 Code]@row + "; " + [Code Description]@row + "; " + Support@row + "; " + [Type of Finding]@row

Code Collector Column:

=JOIN(COLLECT([Code Collector Helper]:[Code Collector Helper], ChartID:ChartID, ChartID@row), CHAR(10))

Nate H

Thanks, @Carson Penticuff. That did work!

Carson Penticuff

Awesome, I'm glad it worked for you! 👍

Paul Newcome

@Carson Penticuff There is TECHNICALLY a way to get this result without a helper column but it doesn't scale very well at all and provides no flexibility. It would have to be capped at just a few rows due to formula length and isn't very easy to manage if updates need made.

The moral of the story… I strongly suggest just sticking with the helper column. Haha.