Remove Duplicate values contained within the same field

I have a dataset in Alteryx that has 81 fields. In most of the fields, there are concatenated values separated by a pipe. How do I clean the data in each field whereby I only leave in distinct values within each field and remove the duplicate values?

I attached a dummy example for one customer. Do I need a Macro or Regex expression to remove duplicate values in a field? The official customer list I have is 60,000 customers so the type of data in the fields will vary. I just need to know what the most efficient process would be to remove duplicates and the Pipes from within each field.

Any help you can provide would be TREMENDOUSLY appreciated!

Thank you.

Dummy.xlsx

Datasets

Accepted answers

All comments

jrlindem

Oh, this is a fun problem to solve! I think you're going to get a variety of ideas from the community.

I'll kick us off and recommend a macro that parses, transposes, groups up (distinct), and then if you need it back in one cell, crosstabs. Loop through each field doing the same.

RCern

@jrlindem I don't have great experience with creating macro's? Is there a basic one you can show me with my dataset?

Hsandness

Here is a macro you can use as a starting point. It's an iterative macro that can process any amount of fields you have to parse. So if you increase the count of fields you use to more than 81 you won't have to make edits to the macro.

Before sending the data to the macro you need to...

1. Transpose it.

2. Give each transposed field it's own RecordID column.

In the macro itself, you'll do the following.

1. Use a filter tool to return only one RecordID/field for processing.

2. Use a Text to Columns tool to split the values into rows, using a "|" as your delimiter.

3. Use a unique tool to return unique values from the Value column.

4. Use a Summarize tool to combine unique values for the RecordID and Field grouping. I concatenated the values with a comma.

In the Interface Designer window pane, you'll have to make sure the Iteration Input is the Macro Input that takes in the data from your workflow. The Iteration Output will be the data that doesn't get processed for the current iteration. You'll have to determine what you want to do when your max iteration is hit. In this case it is set to 100.

NewMacro1.yxmc

dummy_worfklow.yxmd

jrlindem

@RCern -

Here's a Batch Macro that gets you there:

The workflow itself flips everything back to horizontal for you too:

Both workflow files are attached. Hope this, and/or @Hsandness's iterative macro version helps!

Cheers, -Jay

batch_macro.yxmc

jrlindem_example.yxmd

RCern

THank you both! I am going to try both approaches!!!

@Hsandness when i use the iterative macro on my workflow, I get an error that says "Iteration limit of 100 was reached". Is there a way to increase the limit?

binu_acs

@RCern one way of doing this without macro

Split_Fields_22102025.yxmd

jrlindem

@Hsandness - You can increase the number of iterations in the Interface Designer, under "Maximum Number of Iterations" shown here:

Also, regarding @binu_acs non-macro solution, which is great in of itself; there is one change I would recommend. Move the Record ID tool in front of the first Transpose tool so that the workflow can handle multiple rows of data. In your example, assuming only one row, the way binu_acs workflow is configured works fine, but if you have multiple rows, you need the Record ID to be ahead of the other Transform'ing tools. I also threw a crosstab tool at the end to put the records back into their respective rows:

So, between the Batch-Macro, Iterative-Macro, and Non-Macro; I think you should be set to pick the one you like best to move forward with your work!

Hope all of this helps. If you get a chance, mark all three as solutions as they all directly address your request. Cheers, -Jay

RCern

@Hsandness I have the macro working... partly. The problem I am seeing is that I have a Customer with 33 rows. When I run this customer "within" the macro, it produces 26 rows (Screenshot 1 below). However, when I add the macro to my workflow, it produces 25 rows (screenshot 2). Why would one of the rows drop? Are there any settings I need to adjust?

Also, all 33 fields are going in but not all 33 are coming out in both scenarios above. Any idea why this would be?

flying008

Hi, @RCern

Another dynamic solution just one formula for you: (the transpose tool is only for demo.)

Spoiler

IIF(IsString([_CurrentField_]), REGEX_Replace(REGEX_Replace(Trim([_CurrentField_]), '([^|]+)(\|\1)+', '$1'), '[\|]{2,}', Null()), [_CurrentField_])

录制_2025_10_27_09_17_42_869.gif

Hsandness

Without having the exact input data and expected output data it's difficult for me to troubleshoot. If you can provide those with obscured data so customer information is protected I can take a look. Otherwise, I would go with @binu_acs solution, it's more straightforward and should allow for easier review in the future.

RCern

@flying008 can you show me how you configured the tool or can you share that worklfow?

flying008

Hi, @RCern

Please select the all fields, use above multi-filed formula, then you can got it.

RCern

@flying008 @Hsandness @jrlindem @binu_acs Thank you all for your help! I have learned a tremendous amount!

RCern

@flying008 Can you help me modify the REGEX as it seems to work for most of the string columns but not all.

For example, before the multi-field formula tool, I have a field that contains:

SAP,SAP,TBD,SAP,SAP,TBD,SAP,SAP,TBD,SAP,SAP,TBD,TBD,TBD,TBD,NO ACTIVE BILLS,DIRECT,LIST BILL,TBD,NO ACTIVE BILLS

After the multi-field formula tool, the field now contains:

SAP,TBD,SAP,TBD,SAP,TBD,SAP,TBD,NO ACTIVE BILLS,DIRECT,LIST BILL,TBD,NO ACTIVE BILLS

I can't figure out how to adjust the RegEx.

Another example is a field that contains:

Regional Market,Regional Market,Regional Market,Regional Market,Regional Market,Regional Market,Regional Market,Regional Market,Regional Market,Regional Market,Regional Market,Regional Market,Unassigned,Unassigned,Unassigned,Unassigned,Regional Market,R...

and after the tool it contains:

Regional Market,Unassigned,Regional Market

any advice?

flying008

Hi, @RCern

This is a nested Pattern, so you can use transpose tool as @binu_acs or @jrlindem .

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280