Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Check out our powerful new search update! You can read more here. Please let us know if you have any feedback by creating a topic here.
SOLVED

How to remove duplicated in the same cell

Highlighted
Asteroid

How can I remove the repeated information in a same cell?

You can see the information were separated by ","  but some of them are repeated

Foe example,  in the last line, the size description column has "ASST" and "XS" repeated twice.

How can I remove one of them and keep the unique information. 

Thank you. 

 

 

 

STYLEColor detailCOLOR_DESCSIZE_DESCReceiving DateReceiver
GBD2018BLACK-TRUE BLACKBLACKS6,L10-12,M7/8,XL1416,XS4-5,ASST2018-01-02Grace
GBD2018BLUE,BLUE-ENAMEL BLUE,BLUE-ETHEAREAL BLUE,BLUE-MALIBU BLUEBLUES6,L10-12,M7/8,XL1416,XS4-5,ASST,S6,L10-12,M7/8,XL1416,XS4-5,ASST,S6,L10-12,M7/8,XL1416,XS4-5,ASST,S6,L10-12,M7/8,XL1416,XS4-5,ASST2018-01-02,2018-01-02,2018-01-02Grace, Grace
GBD2018GREEN-MINT LEAFGREENS6,L10-12,M7/8,XL1416,XS4-5,ASST2018-01-02Grace
GBD2018NAVY-NAVY BLAZERNAVYS6,L10-12,M7/8,XL1416,XS4-5,ASST2018-01-02Grace
GBD2018ORANGE-SHOCKING ORGORANGES6,L10-12,M7/8,XL1416,XS4-5,ASST2017-11-17Grace
GBD2018RED-POPPY REDREDS6,L10-12,M7/8,XL1416,XS4-5,ASST2018-01-02Grace
GBD2018YELLOW-SAFETY YELLOWYELLOWS6,L10-12,M7/8,XL1416,XS4-5,ASST2017-11-17Grace
GBD2014NAVY-BLUE INDIGO,NAVY-BLUE INDIGONAVYS,L,M,XL,XS,ASST2017-12-15Hope
GBD2014BLACK-BLACK SOOT,BLACK-BLACK SOOTBLACKS,L,M,XL,XS,ASST2017-12-15Hope
GBD2014GRAY-LIGHT GREY MIX,GRAY-GREY MIX BC15,GRAY-GREY MIX BC15,GRAY-LIGHT GREY MIXGRAYS,L,M,XL,XS,S/P,L/G,M/M,XL/TG,XS,ASST,ASST2017-12-15Hope,Hope 
Highlighted
Alteryx Partner

How about using the Text to Columns tool (split field to rows), then unique tool, then crosstab with concatenation to get back to original format?

 

Unique in Cell.PNGUnique in Cell - Output.PNG

Highlighted
Asteroid

@john_miller9

Hi Thank you for your quick reply .... I thought about this methods, but then realized that I can't use it because it is not just 1 column has the duplicates, there are some other cells in different columns had the duplicates. 

Highlighted
Alteryx Partner

How many fields are there in your file?  5, 10, 50?  If it's just a few, you could just repeat and then join back.  Or if it's many, a macro could be built out of the workflow.  Here's all of the data you provided, cleaned up.

 

Unique in Cell v2.PNG

Highlighted
Asteroid

I had more than 20 columns had this duplicated problem, I did one by one!

It works!! thank you.


@john_miller9wrote:

How many fields are there in your file?  5, 10, 50?  If it's just a few, you could just repeat and then join back.  Or if it's many, a macro could be built out of the workflow.  Here's all of the data you provided, cleaned up.

 

Unique in Cell v2.PNG


 

Highlighted
Meteor

Hello john_miller9,

 

Thanks for sharing this workflow. It saved me lots of work today. I also had a lot of fields to clean up, so I used a slightly different method by transposing all values first and then feeding those values to the text to column tool as you did.

 

Can't post the workflow for some reason. Sorry!

 

Meteor

I had a slight variation on the theme that I thought worth sharing.  Same basic requirements except that I had rows which needed to be grouped before applying the dedup.  Essentially my data looked like

 

StudyName1  Column1, Column2, Column3

StudyName1  Column1, Column2, Column3

StudyName2  Column1, Column2, Column3

StudyName2 Column1, Column2, Column3

 

where I want to dedup within the group of StudyName1, 2, 3, etc.

 

This workflow worked well for me instead of separating column by column

Capture.PNG

 

1. For the Multirow tool I used the formula: "IIF([Row-1:Study]!=[Study], [Row-1:ID]+1, [Row-1:ID])" and for this one I created a new field "ID" and did NOT check Group By.  This created a new field "ID" which incremented every time the Study name changed. 

2. Then I transposed with key fields being the new ID column and the Study column I was using as the key and *all* the fields in my data set

3. Removed IsEmpty Values because I had both blanks and nulls

4. Deduped on all fields at once

5. Transposed back with GroupBy on Study and ID and Column headers of "Name" and Values of "Value"

 

This approach ended up to be very minimalistic for my task. 

 

Hope it helps.

Labels