Google Sheets – Array Expansion with Offset

Does the person making the booking really need access to the spreadsheet? Would it be better to make a Google Form which they enter their number and the course into, and which then puts the data into another sheet which has all the helper columns to fill in the rest of the details.

Trying to do this with built-in spreadsheet functions is sure to be frustrating. But the problem is easily solved with a custom function that returns an array:

=allocate(B3:G32, B36:G36)

The first argument is the list of preferences, the second is the list of class sizes. The output is a rectangular array with 1 marking enrollment. The function does not give you the actual enrollment in classes, which you can easily find with SUM.

Here is the function I wrote: it should be pasted into Script Editor, Tools > Script Editor...

function allocate(prefs, limits) {
  var remaining = limits[0], output = [];
  for (var i=0; i<prefs.length; i++) {
    output.push([]);
    for (var j=1; j<=prefs[i].length; j++) {
      var k = prefs[i].indexOf(j);
      if (k != -1 && remaining[k] > 0) {
        output[i][k] = 1; 
        remaining[k]--;
        break;
      }
    }
  }   
  return output;
}

Output for your example agrees with yours:

+---+---+---+---+---+---+
|   |   |   | 1 |   |   |
|   |   |   | 1 |   |   |
|   |   |   | 1 |   |   |
|   | 1 |   |   |   |   |
|   |   |   | 1 |   |   |
|   |   |   |   |   | 1 |
|   |   |   |   |   | 1 |
| 1 |   |   |   |   |   |
|   |   |   | 1 |   |   |
| 1 |   |   |   |   |   |
| 1 |   |   |   |   |   |
|   |   |   |   | 1 |   |
| 1 |   |   |   |   |   |
|   |   |   |   |   | 1 |
| 1 |   |   |   |   |   |
|   |   |   |   |   | 1 |
|   |   |   |   |   | 1 |
|   |   |   |   | 1 |   |
|   |   |   |   | 1 |   |
|   |   |   |   | 1 |   |
|   |   |   |   | 1 |   |
|   |   | 1 |   |   |   |
|   | 1 |   |   |   |   |
|   |   | 1 |   |   |   |
|   |   |   |   | 1 |   |
|   | 1 |   |   |   |   |
|   | 1 |   |   |   |   |
|   |   | 1 |   |   |   |
|   |   | 1 |   |   |   |
|   |   | 1 |   |   |   |
+---+---+---+---+---+---+