Unique records in column rows

[JDong]

Hi  All,

After concat this is the column value I am getting.

1	Apples, Oranges, Apples, Apples,Apples,Oranges
2	Kiwi,Banana,Banana,Banana,Kiwi

How can I now show only unique values like grouping.

Expected output

1 Apples, Oranges

2 Kiwi, Banana

Thanks

[RolandSchubert]

Hi @JDong ,

you could split the values to rows using the Text to Columns tool, select unique values only using the Unique tool and "re-concatenate" using the Summarize tool.

I've attached a sample workflow.

Let me know if it works for you.

Best,

Roland

[JDong]

Thanks Roland.

The actual data has 40 columns and not sure if I need to select all the columns in the unique field ?

Also some rows where no values are available the data is in the below formats and want to clean up the delimiters

Apples,

,,,,,,,,

,Apples,,Oranges

,Oranges,,,,,Apples

[JDong · Accepted solution]

I refer to this helpful post

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Remove-Duplicates-within-a-cell/td-p/6...

And the below regex works perfectly fine

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

The only issue is some values have commas only like the below

,,,,,,,,,,,,,,,,,,,,

,,

,,,,,

Is there anyway I can remove the commas only in rows where there is no text  like Apples, Oranges?

Thanks

[danilang]

Hi @JDong 

Since you mention that you get the duplicates after concatenation, you should probably remove them before you concatenate.  Your data before is probably this

Use a Unique tool to remove the duplicates before the summarize tool

Giving you

Dan

[JDong]

Thanks or something like this formula field also worked before the summarize tool

IF Trim([field])='' THEN Null()
ELSE [field] ENDIF