Comparing columns data with same values but in different order

[kushalb]

Hi All,

Below mentioned data contains values in different orders but it has a same meaning so how to match them though it's order is different here.

UserID	Value1	Value2	Output
134562	British Columbia,Canada	Canada,British Columbia	Canada,British Columbia

Output can be in any format either value1 or value2, it doesn't matter but both should match as currently I am comparing both values and it's throwing as not matched.

kindly help regarding this.

Thank you

[Link]

Hi,

I would suppose tthe following:

- Text to column tool (split Value1 in rows)

- Sort Value1 (Ascending)

- Transform

You can find the workflow attached for further clarification.

You might need to do the same for Value2.

Please rate my answer as solved, if it helps you.

Regards

[JamesCharnley]

Hi @kushalb,

To deal with this problem, at some point you're going to need to do a sort. Exactly where would depend on how your current workflow is set up, so my answer here is more of a general concept solution to the problem than a copy and paste implementation. The idea is that by establishing an identical sort order (within each id) within both of the value columns before concatenation, they will concatenate in the same order afterwards as well.

Hope that helps, give me a shout if it's not clear enough.