Compare values in a single column seperated by a comma

[Shivi2110]

In a single column i have up to 7 values which are seperated by a comma. Is there any way i can identify if there is any different value in them. Below ex column 1 is a change and column 2 is not a change.

Example

                Column 1.    Column 2

Row 1.    A, a, b, x          A,a,a,a,a

[atcodedog05]

Hi @Shivi2110 

You saying single column at the beginning but later you are giving 2 columns 🤔 confused on that. Can you give us the expected output how it should look like. Does it always have 7 values separated by comma.

[Shivi2110]

Hi,
I want the data in the below format. First 2 are input columns and last two are Output to determine, if there a change value within the each input column, where the values are separated by a comma.

Record	InputColumn1	InputColumn2	OutputColumn1	OutputColumn2
1	a,a,a,a	a,b,c,e	No Change	Change
2	h,d,I,q	a,a,t,a	Change	Change
3	a,a,a,a	a,a,a,a	No Change	No Change

[atcodedog05 · Accepted solution]

Hi @Shivi2110 

Here is how you can do it.

Workflow:

Hope this helps : )

[Shivi2110]

Yes that helps. Thanks 😊

[atcodedog05]

Happy to help : ) @Shivi2110 

If the response helps please don't forget to mark it as solution.

Cheers and have a nice day!

[danilang · Accepted solution]

Hi @Shivi2110 

Here's a different way to do it that will handle any number of columns to check or any number of values within each column

Start by transposing all the input columns using the record ID as key.  Split the transposed columns and make them lower case.  Count the number of distinct values in each Id and column.  If the answer is 1 then the values are all the same, so mark the answer on each row and add in the OutputColumn name.  Cross tab these and join back to your original data

Dan

[atcodedog05]

I had a similar thought at first. I skipped this method to prevent data explosion 😅

[danilang]

@atcodedog05 

If it's a choice between a dynamic workflow that explodes data and having a non-dynamic workflow that will need to be modified if the input changes a little, I always choose explosion. 

Dan

[atcodedog05]

@danilang 

You make a great point. Definitely something to be considered 🙂