Clash Tables in Google Sheets

If you are organising exam diets, it is useful to know which exams you can schedule together without clashes. For this, you need a clash table. The starting data will be a list of all the pupils class enrolments. It'll look something like this, where each row is one pupil's courses:

And your clash table will look like this:

We can see that nobody is taking Spanish and German so we could schedule those exams together.

To construct this clash table, the formula in cell L2 is:

=ifna(query($B$2:$F$5,"select count(B) where (B='"&$K2&"' or C='"&$K2&"' or D='"&$K2&"' or E='"&$K2&"' or F='"&$K2&"') and (B='"&L$1&"' or C='"&L$1&"' or D='"&L$1&"' or E='"&L$1&"' or F='"&L$1&"') label count(B) ''"),0)

Replace the 5 in $F$5 with a bigger number to cover all the rows of your pupil data.

If you paste this into your sheet in your equivalent of L2, then copy right and copy down across your clash table you'll be done.

Let me know if you can see a more efficient way to achieve this.