#60 - Find How Many Times Two Specified Members Will Be Contained in A Series of Sets
Problem description & analysis:
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 1-16 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 |
Solution:
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.
Explanation:
group()function groups rows; # is the sequence number of the current member, and ~ is the current member. contain() function finds if the specified items all members of a certain sequence.
The example was originally on Reddit. You may go ahead and compare the conventional solutions with the SPL approach. Now who’s ready to level up their data game? 📈 Feel free to click on the following links and supercharge your Excel today:
SPL download address: esProc Desktop FREE Download
Plugin Installation Method: SPL XLL Installation and Configuration
References to other rich Excel operation cases: Desktop and Excel Data Processing Cases
YouTube FREE courses: SPL Programming