Google-sheets – Concatenate using an array formula to result in last name.firstname

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 |   |   |   |
+---+---+---+---+---+---+

The way I usually tend to do my arrayformulas, and actually any formula that requires action based on a value in another cell, is i start them off with an ISTEXT statement, and then just a simple comma in place of the else portion:

={"Start Time";arrayformula(IF(ISTEXT(B2:B),text(C2:C+vlookup(D2:D,Administrative!$A$1:$C$10,2,false),"mm-dd-yyyy hh:mm:ss"),))}

Note: the reason I leave just a comma instead of "", is that "" will leave a blank string, which will prevent new entries from being added in those rows if your using google forms or any other integration to add in those entries