New summary function request: concatenate unique strings

[jeremy_dalletezze]

In the summary tool, we often use the summary tool to concatenate strings - we love this functionality.

However, we would also like to be able to concatenate just the unique values of strings. This could be done if we ran the preceding text field through the unique tool first, and then concatenate. But when we are doing this for multiple text variables and when we need to summarize other types of data at the same time, this becomes a very un-natural combination of joins & macros. 

Thanks for considering,

Jeremy

[MarqueeCrew]

Is order important?  Example:

A,B,C,A

A,A,B,C

I've got a formula that will dedup these as:

B,C,A

A,B,C

It works with words too:

Any, Bad, Character, Any

Any, Any, Bad, Character

Try this:

trim(regex_replace([field],"\b(\w+),(?=.*\b\1,?)",""))

[Philip]

Thanks for this code, MarqueeCrew! I overcame the order issue by sorting prior to the summarize. The code gave exactly what I needed.

[bb213]

Thank you @MarqueeCrew.  I needed this. 

[WholesaleAndy]

Thank you @MarqueeCrew for sharing. Hoping to see if you can explain or help expand this solution.

I am trying to create the same distinct list, but I am hoping to handle any characters (including whitespace) rather than just letters and numbers. For example, **Test**,**Test**,**Test**.

Is this feasible with Regex? thank you for reading!

[kenjaleamruta]

I would like to see this functionality to Summarize tool. Meanwhile, you can use Unique tool to filter unique combinations of the field you would like to group by and value to concat. Then use Summarize with group_by.

Unique variables ---> Summarize with a group by and concat

[WholesaleAndy]

@kenjaleamruta, yes, this is a solution for when you have 1, maybe 2 fields. As the number of fields in which you are hoping to do a unique concat on, the more complex it becomes.

[bb213]

Unfortunately, this solution does not work for ")".  In other words, "),),),)" will not become ")". Any way to tweak it?  

[MHaak]

What you could try is using the summarize tool twice to get the results you want.

First you group on the data you want to (unique) concatenate and in the summarize tool after that you can actually concatenate the value.

[bb213]

@WholesaleAndy maybe you've found an answer already, but this should work for you: 

trim(regex_replace([field],"\b(\D+),(?=.*\b\1,?)",""))