Removing Duplicate Numeric Values after Sum Concatenate

Question

Hi all,

I need to remove duplicate values in the same cell. Data was already concatenated and is now separate by commas.

This is what is currently looks like.

IDDescriptionPricecommentsLY Prices0red1.08,2,1,1,5,3,2,4 1.08,2,1,1,5,3,2,41blue1,7,3,1,55,3 1,7,3,1,55,32orange8,2,2,3,4,5,5 8,2,2,3,4,5,53yellow9,9,9,8,1 9,9,9,8,14green1 15purple2,2,3, 2,2,3,

Below is what I need it to look like, same format just duplicate numbers in the same cell removed.  My data set is large and has many columns. I only need to remove duplicates from the price columns so I used a dynamic select to select columns that only have price in the name. Then was going to use multi field formula to try to use regex replace to remove duplicates, but am getting and error saying trying to apply string operator to numeric value. I true changing the data types to string but still no luck. Any ideas? Appreciate the communities help!

IDDescriptionPricecommentsLYPrice0red1.08,2,3,4,5 1.08,2,3,4,51blue1,3,55,7 1,3,55,72orange2,3,4,5,8 2,3,4,5,83yellow9,8,1 9,8,14green1 15purple2,3 2,3

danilang · Answer

Hi @lomeoari

In your case, you should look how the duplicates were created in the first place.  Is this the way you receive the data or are the concatenated strings produced earlier on in the workflow.  If this is how you receive the data, then the other solutions will help.  If your workflow creates the concatenated strings, through a summarize or crosstab, then consider removing the duplicate rows before the concatenation.  You can use the Unique tool to remove duplicates using Description and Price or LY Price as the keys

Dan

lomeoari · Answer

I got the regex replace to work but the formula is off. I am using the below formula and the price column is the current input, however, the below regex formula is giving me the below output and not the desired output. Any idea how to amend the formula? Regex is not my strong suit, the formulas confuse me.

TRIM(REGEX_Replace([_CurrentField_], '\b(.+),(?=.*\b\1,?)', ''))

IDDescriptionPriceOUTPUTDesired Output0red0,0,67,67,00670,671blue-30,-30--30-30

apathetichell · Answer

In your sample output red 1 wasn't included. Is there a reason?