Hello everyone, I have an issue trying to clean the a column on my workflow. I got this table and would like to clean column A by removing the occurrences ABChi,hi,hi,hi,hello145ab,ab,an,an254de,bon,bon332 I should have this result: ABChi,hello145ab,an254de,bon332 Thank you in advance for your help

Cleaning a column in alteryx

Hello everyone,

I have an issue trying to clean the a column on my workflow.

I got this table and would like to clean column A by removing the occurrences

A	B	C
hi,hi,hi,hi,hello	1	45
ab,ab,an,an	2	54
de,bon,bon	3	32

I should have this result:

A	B	C
hi,hello	1	45
ab,an	2	54
de,bon	3	32

Thank you in advance for your help

Workflow

Accepted answers

atcodedog05

Hi @NadaH

Use this formula. I have adapted it.

TrimRight(REGEX_Replace([A]+",", "([\w\s]+,)\1+", "$1"),",")

Workflow:

Hope this helps : )

remove dup words.yxmd

atcodedog05

Hi @NadaH

Use this formula. Should cover most scenarios

TrimRight(REGEX_Replace([A]+",", "([^,]+,)\1+", "$1"),",")

Workflow:

Hope this helps : )

remove dup words.yxmd

All comments

atcodedog05

Hi @NadaH

Here is how you can do it.

Formula:

TrimRight(REGEX_Replace([A]+",", "(\b\w+\b,)\1+", "$1"),",")

Workflow:

I am using regex for removing duplicate words.

Here is a weekly challenge which had a same kind of usecase https://community.alteryx.com/t5/Weekly-Challenge/Challenge-130-Is-There-an-Echo-in-Here/td-p/206817

Hope this helps : )

remove dup words.yxmd

mceleavey

Hi @NadaH ,

This is simply achieved by splitting the data to rows on column A using the comma separator in the Text to Columns tool.

Then you use the sum to group it creating a unique combination (or the Unique tool) then using the sum tool to group again by the second and third columns, and concatenating Column A separated by comma:

This gives the following:

Workflow attached.

Hope this helps,

mceleavey solution.yxmd

NadaH

Thank you for your formula.

When I use this formula it works on some rows like this one:

But it doesn't work here:

I have put the result on column newCCandOI , I should have "Hors scope" only one time

NadaH

Thank you! It works

atcodedog05

Happy to help : ) @NadaH

Cheers and have a nice day!

NadaH

I just noticed that it didn't work on this example,

Can you tell me what I can add to the formula .

Thank you

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703