JOIN(DISTINCT(COLLECT not separating names on list.
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:
Answers
-
Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)
Feel free to email me: ramzi@cedartreeconsulting.com
💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.
-
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
Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)
Feel free to email me: ramzi@cedartreeconsulting.com
💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.
-
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
Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)
Feel free to email me: ramzi@cedartreeconsulting.com
💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.
-
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
Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)
Feel free to email me: ramzi@cedartreeconsulting.com
💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.
-
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
Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)
Feel free to email me: ramzi@cedartreeconsulting.com
💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.
-
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
Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)
Feel free to email me: ramzi@cedartreeconsulting.com
💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.
-
Hi Ramzi, I tried your suggestion and got this:
-
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!
-
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?
Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)
Feel free to email me: ramzi@cedartreeconsulting.com
💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
