# Finding most popular combinations of sub-groups in a list of groups

#### jamescurtis29

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

Apologies for reposting this question, the last post had such poor layout! I think I've fixed it now and you can see the tables below.

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 have not been able to find this question asked previously.

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

c

2

d

3

c

3

d

4

b

4

e

</tbody>

I want to find the most common pairings of subgroups. So in the list above a+b appear in 1 groups, a+c appear in 1 groups, b+c appear in 1 group, c+d appear in 2 groups and b+e appears in 1 group. But I cannot think of how to achieve it.

I think it would end with a table that looks like:

Sub-group

Sub-group

number of groups

c

d

2

a

c

1

b

c

1

a

b

1

b

e

1

</tbody>

Any ideas? Something to do with countifs and pivot tables?

Thank you in advance for your help. If you alternatively can think of the answer to this from a previous post and could share that with me, that would also be very welcome!

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

#### jamescurtis29

##### New Member
I figured out a way to do it! Thank goodness, took three days!

So: first, I put all the subgroups together so that it had all the possible combinations as in the following table and put all the group numbers along the top row. This is in columns J,K and M

Sub-group
Sub-group1
abFormula here
ac
ae
bc

<tbody style="border-collapse: collapse; border-spacing: 0px 0px; font-size: 13px; margin-bottom: 0px;">
</tbody>
Then I put the following formula where it says formula (for all groups):
=IFERROR(((MATCH(CONCAT(M\$1,\$J2),\$G:\$G,0)+MATCH(CONCAT(M\$1,\$K2),\$G:\$G,0))/(MATCH(CONCAT(M\$1,\$J2),\$G:\$G,0)+MATCH(CONCAT(M\$1,\$K2),\$G:\$G,0))),0)

This is probably not the best way of getting this data but what this formula does is check the sub-group of each column is in the group in the top row using the MATCH formula. It then divides it by itself so that the result is '1'. If it fails, IFERROR gives it a '0'.

After I did this for each group and part combination, it was simple enough to put add a column a the end with a sum of each row and then sort that column by size.

Do you have a better way of doing this? Would be interested to know a better method.

#### Marcelo Branco

##### MrExcel MVP
Another way

 A​ B​ C​ D​ E​ F​ 1​ Group​ Sub-group​ Sub-group​ Sub-group​ Number of groups​ 2​ 1​ a​ a​ b​ 1​ 3​ 1​ b​ a​ c​ 1​ 4​ 1​ c​ a​ d​ 0​ 5​ 2​ c​ a​ e​ 0​ 6​ 2​ d​ b​ c​ 1​ 7​ 3​ c​ b​ d​ 0​ 8​ 3​ d​ b​ e​ 1​ 9​ 4​ b​ c​ d​ 2​ 10​ 4​ e​ c​ e​ 0​ 11​ d​ e​ 0​ 12​

Array formula in F2 copied down
=SUM(COUNTIFS(\$B\$2:\$B\$10,D2,\$A\$2:\$A\$10,IF(\$B\$2:\$B\$10=E2,\$A\$2:\$A\$10)))
Ctrl+Shift+Enter

M.

Replies
7
Views
52
Replies
7
Views
84
Replies
1
Views
49
Replies
4
Views
558
Replies
5
Views
96

1,127,181
Messages
5,623,223
Members
415,957
Latest member
Newguy1924

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back