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

jamescurtis29

New Member
Joined
Jan 9, 2019
Messages
3
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





<thead>
</thead> <tbody>
</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






































<thead>
</thead> <tbody>
</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!
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

jamescurtis29

New Member
Joined
Jan 9, 2019
Messages
3
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
ad
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
Joined
Aug 23, 2010
Messages
16,501
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,172
Messages
5,629,134
Members
416,365
Latest member
dof

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top