group by unique pairs

[kris-2]

Hello!

I'm trying to group unique pairs from col A and col B.

For example, I have this dataset:

Col A	Col B
Apple	Red
Red	Apple
Banana	Yellow
Green	Banana
Green	Yellow
Lemon	Pink
Pink	Lemon

What I want to do is to group the unique pairs (in the sample table above, Red Apple = Apple Red. The order does not matter.) My desired output is like this:

Col A	Col B	Group
Apple	Red	1
Red	Apple	1
Banana	Yellow	2
Green	Banana	2
Green	Yellow	2
Lemon	Pink	3
Pink	Lemon	3

I've tried concatenating Cols A and B to form pairs, but I feel a bit lost and can't seem to find a way to group them. 

[atcodedog05]

Hi @kris-2 

Here is how you can do it.

Workflow:

1. Using formula tool to calculate pair key by using formula Max([Col A],[Col B])+"-"+Min([Col A],[Col B]). This way it doesn't matter whether the value is in Col A or Col B I will always come in the same order.

2. Using multi-row formula tool to create a new group when pair changes.

It's not the same as your expected output and I am not sure about the logic behind your expected output.

Hope this helps : )

[atcodedog05 · Accepted solution]

Hi @kris-2 

Here is another method that will give you the expected output. But the expectation is that pairs are occurring consecutively.

Workflow:

I am using multi-row formula to check whether [col a] is same as previous row [col a] or [col b] and similarly with [col b] if there are same then add it in the same group or else create a new group.

Hope this helps : )

[kris-2]

Thanks for sharing your workflow! :) I was hoping rows 3-5 would be in one group as if the pairs are transitive, if that makes more sense...

[atcodedog05]

Hi @kris-2 

Check my second response. Hope that helps.

[kris-2]

Hi @atcodedog05! Your workflow was very useful, thank you!

[atcodedog05]

Happy to help : ) @kris-2 

Cheers and have a nice day!