Finding most popular combined sub-groups

[jamescurtis29]

Hello Excel superior beings, I bow to your wisdom and knowledge.

I know this must be possible but I cannot think of how to do it and have used this forum in the past to find information but cannot think of how to even look for the answer.

I have a list of groups with group ids and members of each group representing subgroups. It looks a little like this:

Group Sub-group
1 a
1

b​

1

c​

2

a​

2

c​

2

e​

3

b​

3

d​

4

a​

4

b​

4

c​

4

e
… …

I want to find the most common pairings of subgroups. So in the list above a+b appear in 2 groups, a+c appear in 3 groups, b+c appear in 1 group, etc. But I cannot think of how to achieve it. Any ideas?

Thank you in advance for your help. If your answer is also finding this posted before in a way I've not been able to find, that would also be very welcome!

Kind Regards,

James​