Formula with CHAR(10) and multiple references

hello,

I am working on a formula to combine all notes from multiple PMO discussion groups. I have it working but in my Master notes column if a discussion group left information blank, the CHAR(10) puts large blank space within the column. Each discussion group is grouped together within the below formula.

=JOIN(COLLECT([PMO RB]@row + CHAR(10) + [PMO Review Prelim Meeting Date]@row + CHAR(10) + [PMO Review Board Comments]@row + CHAR(10) + Scheduling@row + CHAR(10) + [Scheduling Review Date]@row + CHAR(10) + [Scheduling Committee Comments]@row + CHAR(10) + Clinical@row + CHAR(10) + [Clinical Discussion Group Date]@row + CHAR(10) + [Clinical Discussion Group Decision]@row + CHAR(10) + [Clinical Group Comments]@row + CHAR(10) + [BA name for comments]@row + CHAR(10) + [Business Discussion Group Date]@row + CHAR(10) + [Business Discussion Group Decision]@row + CHAR(10) + [Business Group Comments]@row + CHAR(10) + Analytics@row + CHAR(10) + [Analytics Discussion Group Date]@row + CHAR(10) + [Analytics Discussion Group Decision]@row + CHAR(10) + [Analytics Group Comments]@row + CHAR(10) + Security@row + CHAR(10) + [Security Discussion Group Date]@row + CHAR(10) + [Security Discussion Group Decision]@row + CHAR(10) + [Security Group Comments]@row + CHAR(10) + Technical@row + CHAR(10) + [Technical Discussion Group Date]@row + CHAR(10) + [Technical Discussion Group Decision]@row + CHAR(10) + [Technical Group Comments]@row + CHAR(10) + Enterprise@row + CHAR(10) + [Enterprise Steer Review Date]@row + CHAR(10) + [Enterprise Steer Decision Requested]@row + CHAR(10) + [Enterprise Comments]@row, [Security Group Comments]@row + [Business Group Comments]@row + [Analytics Group Comments]@row + [Technical Group Comments]@row + [Clinical Group Comments]@row + [Enterprise Comments]@row, NOT(ISBLANK(@cell))), "-")

Find more posts tagged with

Formulas

Accepted answers

All comments

Scott Peters

Hi @Erin Kim - I am showing this with just four of your columns. This assumes that all of your in-scope columns are next to each other:

=JOIN(COLLECT([PMO RB]@row:Scheduling@row, [PMO RB]@row:Scheduling@row, NOT(ISBLANK(@cell))), CHAR(10))

Deric

Use if statements with each one of your notes and include the char(10) in the if statement. Also, I don’t think you need to use join collect here.

=if([PMO RB]@row = "", "", [PMO RB]@row + CHAR(10)) + if[PMO Review Prelim Meeting Date]@row = "", "", [PMO Review Prelim Meeting Date]@row + CHAR(10)) + if([PMO Review Board Comments]@row = "", "", [PMO Review Board Comments]@row)

Repeat for every entry. Hope this helps!

Erin Kim

@Scott Peters My columns are not next to each other, they are sprinkled throughout my sheet :) Thanks for the insight!

WtaylorW

@Erin Kim What was the resolution you found for this? I am having the exact same issue and my initial formula looked very similar to yours.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.