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

[jamescurtis29]

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!

 

[jamescurtis29]

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.

 

[Marcelo Branco]

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.