JOIN(DISTINCT(COLLECT not separating names on list.

Try using CHAR(10) instead of ";" in your join.

Cheers,

Ramzi

https://community.smartsheet.com/discussion/comment/265856#Comment_265856

Thank you. This is the result:

As you can see, there no spaces between Jonah Kaplan, Mike Stobbe, and Chris Francescani.

Sarah, make sure your wrap is turned on for that column:

If that's not the issue, I notice the two sheets above don't match up but try adding another char(10).

So CHAR(10)+CHAR(10) for your join separator.

If that still doesn't work, please include actual screen shots where the values match up (the names) so I can see how they look in the lookup sheet.

Cheers,

Ramzi

https://community.smartsheet.com/discussion/comment/265929#Comment_265929

I apologize. I have a report pulling from my worksheet that I have populating a dashboard.

As you can see, some of these names still have no space even with CHAR(10)+CHAR(10) for my join separator.

Sarah, can you please include a screenshot of the sheet you are pulling the names from

This sheet - {2020 CSTE Media Tracking Range 1}

I need to see how the names are structured in the cells.

Cheers,

Ramzi

https://community.smartsheet.com/discussion/comment/266006#Comment_266006

Yes, here:

Sarah, one last favor to ask - can you show me the formula you are using I know you included it in the earlier emails, but can you please show the one you are using now? I am working on reproducing your issue and I need the exact one.

Cheers,

Ramzi

Well, this is very odd - adding DISTINCT negates the CHAR(10) or any chacrter like ";" for names that are duplicated. Must be a bug or by design. Either way, with a little creative formula magic, you can do the following. In my example I have both reference and result in one sheet so you can see it all in one place.

The green part would be your reference sheet and the orange is your results sheet. You will of course need to modify the formulas with the correct column names:

Column O is the Outlet list in your reference
Column P is the Contact in your reference
Add a helper column in your reference with the following formula to create a combined lookup value: =O@row + ">" + P@row
Add another help column that will display the first occurrence of the Contact within the list of Outlets: =IF(COUNTIFS(Ref@row:Ref$1, Ref@row) > 1, "", P@row)

Now in your main sheet use this formula in your Result column: =JOIN(COLLECT(Distinct:Distinct, O:O, Outlet@row), CHAR(10))

I hope this all makes sense and works for you.

Cheers,

Ramzi

https://community.smartsheet.com/discussion/comment/266053#Comment_266053

Hi Ramzi. This is my current formula:

=JOIN(DISTINCT(COLLECT({2020 CSTE Media Tracking Range 1}, {2020 CSTE Media Tracking Range 2}, Outlet@row), CHAR(10) + CHAR(10)))

I am not sure how to use a helper row.

Sarah, I sent you a solution earlier. Did you see it? Look above your last message.

Cheers,

Ramzi

https://community.smartsheet.com/discussion/comment/266115#Comment_266115

Hi Ramzi, I tried your suggestion and got this:

David Joyeuse

Hi @Sarah Zimmerman

If you are returning values that are in the Outcome column of the {2020 CSTE Media Tracking} Sheet, then it seems working as intended. This column seems to have more than one value within each cells.

COLLECT will return the value of one cell, treating it as one string of text. Then JOIN will add the separator between two collected cells.

Hope it helped!