Formula with CHAR(10) and multiple references

Erin Kim

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))), "-")

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.