Need to concatenate multiple columns across multiple rows based on criteria in a single column
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!
Answers
-
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))
-
Thanks, @Carson Penticuff. That did work!
-
Awesome, I'm glad it worked for you! 👍
-
@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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 464 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!