Percentage of common records-CodePudding

I have a list of Views and their corresponding tables like this:

Views   Tables
V1      T1
V1      T2
V1      T3
V1      T4
V2      T2
V2      T3
V2      T4
V3      T1
V3      T4
V3      T5
V3      T6

I want the percentage of tables covered for a given view like this:

    V1               V2             V3
V1  n(V1&V1)/n(V1)  n(V1&V2)/n(V2)  n(V1&V3)/n(V3)
V2  n(V1&V2)/n(V1)  n(V2&V2)/n(V2)  n(V2&V3)/n(V3)
V3  n(V1&V3)/n(V1)  n(V3&V2)/n(V2)  n(V3&V3)/n(V3)

so n(V1&V2) would mean no of tables common to V1&V2 and n(V2) is no of tables in V2. As such, the final output should look something like this:

     V1   V2    V3
V1  4/4   3/3   2/4
V2  3/4   3/3   1/4
V3  2/4   1/3   4/4

Can this be done in excel? Even partial solutions would be appreciated.

CodePudding user response：

In Office 365 we can use FILTER in MATCH to return the numbers:

=COUNT(MATCH(FILTER($B$2:$B$12,$A$2:$A$12=$D2),FILTER($B$2:$B$12,$A$2:$A$12=E$1),0))/COUNTIF($A$2:$A$12,E$1)

With older versions we can use SUMPRODUCT and COUNTIFS:

=SUMPRODUCT(COUNTIFS($A$2:$A$12,E$1,$B$2:$B$12,$B$2:$B$12)*($A$2:$A$12=$D2))/COUNTIF($A$2:$A$12,E$1)