Google-sheets – Google sheets join arrays

I wrote a custom function in Google Apps Script that takes all the criteria you want it to have (and more) in consideration.

Code

function myPayments(paymentPlan, dateRange) {
  var output = [];  
  for(var i=0, iLen=dateRange.length; i<iLen; i++) {
    var checkDate = new Date(dateRange[i]);
    var weekDay = checkDate.getDay()+1, sum=0;
    for(var j=0, jLen=paymentPlan.length; j<jLen; j++) {
      if(paymentPlan[j][1] == weekDay) {   
        if(paymentPlan[j][2] === "" && paymentPlan[j][3] === "") {
          sum += paymentPlan[j][0];
        } else if(paymentPlan[j][2] === "") {
          if(checkDate <= new Date(paymentPlan[j][3])) {
            sum += paymentPlan[j][0];
          }
        } else if(paymentPlan[j][3] === "") {
          if(checkDate >= new Date(paymentPlan[j][2])) {
            sum += paymentPlan[j][0];
          }
        } else if(paymentPlan[j][2] !== "" && paymentPlan[j][3] !== "") {
          if(checkDate >= new Date(paymentPlan[j][2]) && 
            checkDate <= new Date(paymentPlan[j][3])) {
            sum += paymentPlan[j][0];
          }
        } 
      }
    }
    if(sum == 0) {
      output.push([]);
    } else {
      output.push([sum]);
    }
  } 
  return output;
}

Screenshot

Remarks

If you extend the paymentPlan range, to contain the rest of the data as presented in the PAYMENT PLAN sheet, then you will notice that overlapping payments will be summed.

Add the script under Tools>Script editor and press the save button (in your case it's already there).

Example

I've added the solution in your example file.

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