Find how many times two specified members will be contained in a series of sets
Below is an Excel table recording the groups in a competition. In the range of C1:V13, every 6 columns corresponds to a table having 4 players, and 116 represents numbers of 16 players.
A 
B 
C 
D 
E 
F 
G 
H 
I 
J 
K 
L 
M 
N 
O 
P 
Q 
R 
S 
T 
U 
V 

1 
1 
8 
14 
11 
12 
2 
1 
4 
3 
13 
10 
15 
16 
6 
5 

2 
2 
3 
1 
5 
7 
9 
11 
13 
15 
2 
4 
6 
8 
10 
12 

3 
3 
11 
8 
14 
15 
3 
6 
9 
12 
13 
16 
5 
2 
1 
4 

4 
4 
9 
13 
1 
5 
4 
8 
12 
16 
3 
7 
10 
14 
2 
6 

5 
5 
3 
2 
11 
16 
14 
7 
10 
15 
1 
6 
12 
13 
8 
5 

6 
6 
2 
14 
3 
9 
7 
1 
13 
8 
5 
11 
6 
4 
15 
12 

7 
7 
12 
6 
13 
7 
4 
5 
12 
11 
1 
8 
15 
9 
2 
16 

8 
8 
1 
9 
2 
10 
7 
15 
8 
16 
3 
11 
4 
12 
5 
13 
Task: Compute the frequency of any two players who compete on the same table and display the result as a matrix diagram, as the following shows:
A 
B 
C 
D 
E 
F 
G 
H 
I 
J 
K 
L 
M 
N 
O 
P 
Q 
R 

11 
1 
2 
3 
4 
5 
6 
7 
8 
9 
10 
11 
12 
13 
14 
15 
16 

12 
1 

13 
2 
2 

14 
3 
2 
3 

15 
4 
2 
2 
2 

16 
5 
2 
1 
1 
2 

17 
6 
1 
2 
1 
2 
2 

18 
7 
2 
0 
2 
0 
1 
1 

19 
8 
2 
1 
0 
2 
1 
1 
2 

20 
9 
3 
2 
2 
0 
1 
1 
0 
1 

21 
10 
1 
1 
1 
0 
0 
0 
2 
0 
1 

22 
11 
0 
1 
2 
3 
2 
1 
0 
2 
1 
0 

23 
12 
1 
0 
2 
3 
1 
3 
1 
2 
1 
1 
3 

24 
13 
3 
1 
0 
0 
3 
2 
2 
1 
2 
1 
1 
2 

25 
14 
0 
1 
2 
0 
0 
0 
2 
2 
1 
2 
2 
1 
0 

26 
15 
1 
0 
0 
0 
0 
0 
2 
3 
2 
2 
2 
1 
2 
2 

27 
16 
0 
3 
1 
1 
1 
0 
1 
2 
0 
1 
1 
1 
2 
0 
2 
Enter the following formula in C13 in SPL XLL:
=spl("=?1.conj(~.group((#1)\6)).count( ~.contain( ?2,?3) )",$C$1:$V$8,$B13,C$11)
The formula gets result only for one cell, and we need to drag it to the other cells to compute their values while avoiding cell on the diagonal (because it is meaningless to put one player on the same table twice). Note that the matrix will compute twice, so you just need to drag to draw a rectangular area.
group()function groups rows; # is the sequence number of the current member, and ~ is the current member. contain() function finds if the specidied items all members of a certain sequence.
Source:https://www.reddit.com/r/excel/comments/1cceq2q/how_to_count_how_many_times_two_players_are_at/
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_Desktop/
SPL Learning Material 👉 https://c.scudata.com
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProcDesktop
Linkedin Group 👉 https://www.linkedin.com/groups/14419406/