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 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 : )

pairs v2.yxmd