Join Collect
I'm using a formula which is returning the data I need but it is duplicating same results - is there a way to get just one of each result?
=JOIN(COLLECT({status}, {ref}, [Order ref]@row), ", ")
returned data:
I just need it to say Pack and Dispatch, Assembly - is this possible?
Thanks
Best Answers
-
You would wrap the COLLECT function in a DISTINCT function.
=JOIN(DISTINCT(COLLECT(...............)), ", ")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Hi @becxyz,
Try this:
=JOIN(DISTINCT(COLLECT({status}, {ref}, [Order ref]@row)), ", ")
Hope this helps, but if you're still having any problems then just post! 😊
Answers
-
You would wrap the COLLECT function in a DISTINCT function.
=JOIN(DISTINCT(COLLECT(...............)), ", ")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Hi @becxyz,
Try this:
=JOIN(DISTINCT(COLLECT({status}, {ref}, [Order ref]@row)), ", ")
Hope this helps, but if you're still having any problems then just post! 😊
-
Hi becxyz.,
Please try this formula to find distinct values.
=JOIN(DISTINCT(COLLECT({status}, {ref}, [Order ref]@row), ", "),", ")
You can modify your delimiter accordingly.
Thanks,
Kaveri Vipat
Senior Associate - Smartsheet Development, Ignatiuz Software
2023 Core Product Certified
Did this answer help you? Show some love by marking this answer as "Insightful💡" or "Awesome❤️" and "Vote Up⬆️"
-
Thank you all, that has worked! Is there any way to get a space, or comma, between the values returned?
-
Hi @becxyz
I hope you're well and safe!
Can you share an example of how you want it to look like?
I hope that helps!
Be safe, and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
So it would read "Pack and Dispatch, Assembly" rather than "Pack and DispatchAssembly"
-
@becxyz The formula syntax I provided should do that. If it is not, can you copy/paste the formula you are using?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@Paul Newcome my apologies, realised I had one set of parentheses in the wrong place! 🤦♀️all sorted now, thanks for your help.
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
I'm glad that it's now working as expected!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 306 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!