Display a heatmap matrix showing % matches to other table values

[digitalx2001]

Hi all! First post (but long time lurker!). I've simplified the scenario and sample data I'm trying to solve to the following:

I'm trying to create a heatmap matrix to show percentage of school classes each person was in with every other person. From the raw data (provided below), I want to generate a unique name list, transpose same across the top, do the gradient conditional formatting for high/low values, etc (this isn't the part I need help on).

This issue I'm having is calculating the values. I've somewhat solved this problem but in a very, very ugly and slow way by adding calculated columns to the raw data and then VBA. Code took like 20 minutes to run since there were hundreds of unique names/classes... and this will have to be run often on brand new data sets, so my method is not really sustainable.

As for the solution (required output shown below).... For each cell in the matrix, it should divide the number of classes that RowName <row name="">was in with ColumnName<column name="">, by RowName<row name="">'s total classes.

For example, Bill has 2 classes total (History and Chemistry). Tom was in 1 of those 2 (History). Hence... 1/2 = 50% in cell C2 in the output below. From Tom's perspective, Bill was in all of his classes (1 class, just History). Hence... 1/1 = 100% in cell B3 below.

Really, I think the answer might boil down to just a single formula, but I couldn't find any other similar solutions online to mimic. If VBA would be better suited or required instead of just a repeated formula, that's fine too (and I'll be using VBA to generate the unique list to build the matrix and formatting anyway). Thanks in advance!

EDIT: This would be for Excel 2010+. Might need to run this on different machines, some of which have 2013, so better safe than sorry.

---

RAW DATA:
[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Class[/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD]History[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]History[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Chemistry[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Art[/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD]Chemistry[/TD]
[/TR]
[TR]
[TD]Sally[/TD]
[TD]Calculus[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Calculus[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]History[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Art[/TD]
[/TR]
</tbody>[/TABLE]

REQUIRED OUTPUT:
[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Bill[/TD]
[TD]Tom[/TD]
[TD]Joe[/TD]
[TD]Sally[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Bill[/TD]
[TD][/TD]
[TD].5[/TD]
[TD].5[/TD]
[TD]0[/TD]
[TD].5[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Tom[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Joe[/TD]
[TD].333[/TD]
[TD]0[/TD]
[TD][/TD]
[TD].333[/TD]
[TD].333[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Sally[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]John[/TD]
[TD].5[/TD]
[TD].5[/TD]
[TD].5[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

</row></column></row>

 

[shg]

One way:

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][/tr]
[tr][td]

1​

[/td][td]

Name​

[/td][td]

Class​

[/td][td][/td][td][/td][td][/td][td][/td][td]

​

[/td][/tr]

[tr][td]

2​

[/td][td]Bill[/td][td]History[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

3​

[/td][td]Tom[/td][td]History[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

4​

[/td][td]Joe[/td][td]Chemistry[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td]Joe[/td][td]Art[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]Bill[/td][td]Chemistry[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]Sally[/td][td]Calculus[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]Joe[/td][td]Calculus[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td]John[/td][td]History[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

10​

[/td][td]John[/td][td]Art[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

11​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

12​

[/td][td][/td][td]

Art​

[/td][td]

Calculus​

[/td][td]

Chemistry​

[/td][td]

History​

[/td][td][/td][td][/td][/tr]

[tr][td]

13​

[/td][td]Bill[/td][td]

​

[/td][td]

​

[/td][td]

x​

[/td][td]

x​

[/td][td][/td][td]B13 and across and down: {=IF(OR(($A$2:$A$10=$A13)*($B$2:$B$10=B$12)), "x", "")}[/td][/tr]

[tr][td]

14​

[/td][td]Joe[/td][td]

x​

[/td][td]

x​

[/td][td]

x​

[/td][td]

​

[/td][td][/td][td][/td][/tr]

[tr][td]

15​

[/td][td]John[/td][td]

x​

[/td][td]

​

[/td][td]

​

[/td][td]

x​

[/td][td][/td][td][/td][/tr]

[tr][td]

16​

[/td][td]Sally[/td][td]

​

[/td][td]

x​

[/td][td]

​

[/td][td]

​

[/td][td][/td][td][/td][/tr]

[tr][td]

17​

[/td][td]Tom[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

x​

[/td][td][/td][td][/td][/tr]

[tr][td]

18​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

19​

[/td][td]

​

[/td][td]Bill[/td][td]Joe[/td][td]John[/td][td]Sally[/td][td]Tom[/td][td][/td][/tr]

[tr][td]

20​

[/td][td]Bill[/td][td]

​

[/td][td]

1​

[/td][td]

1​

[/td][td]

0​

[/td][td]

1​

[/td][td]B20 and across and down: {=IF($A20=B$19, "", SUMPRODUCT((INDEX($B$13:$E$17, MATCH($A20, $A$13:$A$17), 0)="x") * (INDEX($B$13:$E$17, MATCH(B$19, $A$13:$A$17), 0)="x")))}[/td][/tr]

[tr][td]

21​

[/td][td]Joe[/td][td]

1​

[/td][td]

​

[/td][td]

1​

[/td][td]

1​

[/td][td]

0​

[/td][td][/td][/tr]

[tr][td]

22​

[/td][td]John[/td][td]

1​

[/td][td]

1​

[/td][td]

​

[/td][td]

0​

[/td][td]

1​

[/td][td][/td][/tr]

[tr][td]

23​

[/td][td]Sally[/td][td]

0​

[/td][td]

1​

[/td][td]

0​

[/td][td]

​

[/td][td]

0​

[/td][td][/td][/tr]

[tr][td]

24​

[/td][td]Tom[/td][td]

1​

[/td][td]

0​

[/td][td]

1​

[/td][td]

0​

[/td][td]

​

[/td][td][/td][/tr]

[tr][td]

25​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[digitalx2001]

Thanks for the reply. So, maybe I'm missing something - where do we get the percentages from? That's actually the key data: how often RowName is in ColumnName's class, divided by RowName's total classes. Which classes they are in together are completely irrelevant.

 

[digitalx2001]

Oh wait, this actually is it! I didn't notice because my test data didn't have any people with more than 1 class together. I thought it was only returning a 1 if they had any classes together, but it was actually a count. Perfect!

I got it to work by just adding the following to your code for B20...

{=IF($A20=B$19, "", SUMPRODUCT((INDEX($B$13:$E$17, MATCH($A20, $A$13:$A$17), 0)="x") * (INDEX($B$13:$E$17, MATCH(B$19, $A$13:$A$17), 0)="x"))/COUNTIF($A$2:$A$10,$A20))}

 

[shg]

You're welcome.