Join Collect

becxyz

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

Paul Newcome

You would wrap the COLLECT function in a DISTINCT function.

=JOIN(DISTINCT(COLLECT(...............)), ", ")

Nick Korna

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! 😊

Paul Newcome

You would wrap the COLLECT function in a DISTINCT function.

=JOIN(DISTINCT(COLLECT(...............)), ", ")

Nick Korna

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! 😊

Kaveri Vipat

Hi becxyz., 

Please try this formula to find distinct values. 

=JOIN(DISTINCT(COLLECT({status}, {ref}, [Order ref]@row), ", "),", ") 

You can modify your delimiter accordingly. 

becxyz

Thank you all, that has worked! Is there any way to get a space, or comma, between the values returned?

Andrée Starå

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!

becxyz

So it would read "Pack and Dispatch, Assembly" rather than "Pack and DispatchAssembly"

Paul Newcome

@becxyz The formula syntax I provided should do that. If it is not, can you copy/paste the formula you are using?

becxyz

@Paul Newcome my apologies, realised I had one set of parentheses in the wrong place! 🤦‍♀️all sorted now, thanks for your help.

Paul Newcome

Happy to help. 👍️

Andrée Starå

https://community.smartsheet.com/discussion/comment/429671#Comment_429671

I'm glad that it's now working as expected!