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 the number of 16 players.

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:

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 results only for one cell, and we need to drag it to the other cells to compute their values while avoiding the 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 are 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