Searching common items in two different cells

yad

Hi All,

I'm stuck in a business requirement where I need to find common values in two different rows and then output the values in a new column.

The values are comma-delimited and do not adhere to a specific order.

Sample Input: 

Profile	Entitlements
A	1, 2, 3
A	4, 2, 1, 5
A	1, 6 ,4
B	1, 2, 3, 5
B	6, 7, 8, 9

Expected Output:

Profile	Entitlements	Commonality
A	1, 2, 3	1,2
A	4, 2, 1, 5	1,2
A	1, 6 ,4, 2	1,2
B	1, 2, 3, 5	n/a
B	6, 7, 8, 9	n/a

Thanks!

LJoonas

Hi,

Here could be one possible solution.

I first split all the Entitlements to separate columns and transposed them. Then I took count of all the values and compared it to count of Profile rows. If count and Profile match then the Entitlements value should be in all rows for the profile. Then I Cross Tab the result and combine it to the original rows with Find and Replace tool. This might need some modification and to make sure that text to columns tool splits to enough columns. Let me know if this works.

Edit. fixed the naming on the workflow

Common item search.yxmd