JOIN(DISTINCT(COLLECT not separating names on list.

Sarah Zimmerman

I've created a complicated cross-sheet formula with several different reports. What I'm trying to do now is have a sheet populate whatever information is found in Range 1 if it meets the conditions mentioned. This is the formula I used:

=JOIN(DISTINCT(COLLECT({2020 CSTE Media Tracking Range 1}, {2020 CSTE Media Tracking Range 2}, Outlet@row), "; "))

Even with "; ")) some of the names are not separated and it becomes confusing to read. This is the sheet it's pulling from and how the names are listed, for reference:

Ramzi K

@Sarah Zimmerman

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

Cheers,

Ramzi

Sarah Zimmerman

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.

Ramzi K

@Sarah Zimmerman

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

Sarah Zimmerman

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.

Ramzi K

@Sarah Zimmerman

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

Sarah Zimmerman

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

Yes, here:

Ramzi K

@Sarah Zimmerman

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

Ramzi K

@Sarah Zimmerman

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

Sarah Zimmerman

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.

Ramzi K

@Sarah Zimmerman

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

Cheers,

Ramzi

Sarah Zimmerman

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!

Ramzi K

@Sarah Zimmerman

Sarah, it appears that your reference column has multiple names in some cases that are not separated by anything. Is that accurate? If that's the case, the function I provided will return the entire contents of the cell. Like this example. If your reference cell is on the right, it has all the names already mashed together. Or am I missing something?