Parsing comma separated values for similar values

Question

Hi All,

I have several columns with concatenated values separated by comma. Each row has a unique key and multiple columns each column has several values separated by comma. I want to parse each column and identify if the values within this column is similar or different. If the values are similar I need to report one value otherwise report the different values.

Is there away to use RegEx or formula to do that, please?

qais1975 · Answer

Dear @BethanG ,

Attached are the sample data and desired output. Thank you so much for your help and support.

Sample Data.xlsx

qais1975 · Answer

Hi @Luke_C

The sample data and the desired output attached below. Thank you in advance for your help.

Sample Data.xlsx

BethanG · Answer

Hi @qais1975, I'd recommend using the Text to Columns tool, getting it to Split To Rows, then using the Unique tool to remove duplicates