Comparing columns data with same values but in different order

Question

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.

UserIDValue1Value2Output134562British Columbia,CanadaCanada,British ColumbiaCanada,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

JamesCharnley · Answer

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.

TwoSortOrders.yxmd

Link · Answer

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

New Workflow1.yxmd