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:




<thead>
[TH="align: left"]Group

[/TH]
[TH="align: left"]Sub-group

[/TH]
[TH="align: left"]


[/TH]

</thead> <tbody>
[TD="align: left"]1

[/TD]
[TD="align: left"]a

[/TD]
[TD="align: left"]


[/TD]

[TD="align: left"]1

[/TD]
[TD="align: left"]b

[/TD]
[TD="align: left"]


[/TD]

[TD="align: left"]1

[/TD]
[TD="align: left"]c

[/TD]
[TD="align: left"]


[/TD]

[TD="align: left"]2

[/TD]
[TD="align: left"]c

[/TD]
[TD="align: left"]


[/TD]

[TD="align: left"]2

[/TD]
[TD="align: left"]d

[/TD]
[TD="align: left"]


[/TD]

[TD="align: left"]3

[/TD]
[TD="align: left"]c

[/TD]
[TD="align: left"]


[/TD]

[TD="align: left"]3

[/TD]
[TD="align: left"]d

[/TD]
[TD="align: left"]


[/TD]

[TD="align: left"]4

[/TD]
[TD="align: left"]b

[/TD]
[TD="align: left"]


[/TD]

[TD="align: left"]4

[/TD]
[TD="align: left"]e

[/TD]
[TD="align: left"]


[/TD]

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




<thead>
[TH="align: left"]Sub-group

[/TH]
[TH="align: left"]Sub-group

[/TH]
[TH="align: left"]number of groups

[/TH]

</thead> <tbody>
[TD="align: left"]c

[/TD]
[TD="align: left"]d

[/TD]
[TD="align: left"]2

[/TD]

[TD="align: left"]a

[/TD]
[TD="align: left"]c

[/TD]
[TD="align: left"]1

[/TD]

[TD="align: left"]b

[/TD]
[TD="align: left"]c

[/TD]
[TD="align: left"]1

[/TD]

[TD="align: left"]a

[/TD]
[TD="align: left"]b

[/TD]
[TD="align: left"]1

[/TD]

[TD="align: left"]b

[/TD]
[TD="align: left"]e

[/TD]
[TD="align: left"]1

[/TD]

[TD="align: left"]


[/TD]
[TD="align: left"]


[/TD]
[TD="align: left"]


[/TD]

[TD="align: left"]


[/TD]
[TD="align: left"]


[/TD]
[TD="align: left"]


[/TD]

[TD="align: left"]


[/TD]
[TD="align: left"]


[/TD]
[TD="align: left"]


[/TD]

[TD="align: left"]


[/TD]
[TD="align: left"]


[/TD]
[TD="align: left"]


[/TD]

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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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

[TABLE="class: cms_table, width: 0"]
<tbody style="border-collapse: collapse; border-spacing: 0px 0px; font-size: 13px; margin-bottom: 0px;">[TR]
[TH="align: center"]Sub-group
[/TH]
[TH="align: center"]Sub-group[/TH]
[TH="align: center"]1[/TH]
[/TR]
[TR]
[TD="align: left"]a[/TD]
[TD="align: left"]b[/TD]
[TD="align: left"]Formula here[/TD]
[/TR]
[TR]
[TD="align: left"]a[/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"][/TD]
[/TR]
[TR]
[TD="align: left"]a[/TD]
[TD="align: left"]d[/TD]
[TD="align: left"][/TD]
[/TR]
[TR]
[TD="align: left"]a[/TD]
[TD="align: left"]e[/TD]
[TD="align: left"][/TD]
[/TR]
[TR]
[TD="align: left"]b[/TD]
[TD="align: left"]c[/TD]
[TD="align: left"]

[/TD]
[/TR]
</tbody>[/TABLE]
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.
 
Upvote 0
Another way


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
A
[/td][td="bgcolor: #DCE6F1"]
B
[/td][td="bgcolor: #DCE6F1"]
C
[/td][td="bgcolor: #DCE6F1"]
D
[/td][td="bgcolor: #DCE6F1"]
E
[/td][td="bgcolor: #DCE6F1"]
F
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
1
[/td][td]
Group​
[/td][td]
Sub-group​
[/td][td][/td][td]
Sub-group​
[/td][td]
Sub-group​
[/td][td]
Number of groups​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
2
[/td][td]
1​
[/td][td]
a​
[/td][td][/td][td]
a​
[/td][td]
b​
[/td][td]
1​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
3
[/td][td]
1​
[/td][td]
b​
[/td][td][/td][td]
a​
[/td][td]
c​
[/td][td]
1​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
4
[/td][td]
1​
[/td][td]
c​
[/td][td][/td][td]
a​
[/td][td]
d​
[/td][td]
0​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
5
[/td][td]
2​
[/td][td]
c​
[/td][td][/td][td]
a​
[/td][td]
e​
[/td][td]
0​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
6
[/td][td]
2​
[/td][td]
d​
[/td][td][/td][td]
b​
[/td][td]
c​
[/td][td]
1​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
7
[/td][td]
3​
[/td][td]
c​
[/td][td][/td][td]
b​
[/td][td]
d​
[/td][td]
0​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
8
[/td][td]
3​
[/td][td]
d​
[/td][td][/td][td]
b​
[/td][td]
e​
[/td][td]
1​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
9
[/td][td]
4​
[/td][td]
b​
[/td][td][/td][td]
c​
[/td][td]
d​
[/td][td]
2​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
10
[/td][td]
4​
[/td][td]
e​
[/td][td][/td][td]
c​
[/td][td]
e​
[/td][td]
0​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
11
[/td][td][/td][td][/td][td][/td][td]
d​
[/td][td]
e​
[/td][td]
0​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
12
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]


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.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

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
Back
Top