Friday, October 23, 2015

Question:

Here is my sample sheet:

Given the example data in this sheet, I want to return a subset automatically that meets the following criteria:
* 4 total rows
* 1 row where color=green
* 1 row where color=red
* 2 rows where color=blue
* The COMBINED value of the PRICE column of the four returned rows cannot exceed a sum of 30
* The returned set should provide the highest possible COMBINED value of the POINTS column of the four returned rows.

In short, I would like to find the four records in the entire table that result in the highest possible combined POINTS value, without exceeding a combined PRICE value of 30.

Help is greatly appreciated!

Solution:

Insert the following script code in the script code of your spreadsheet:

////////////////////////////////////////////////////////

function subset(v) {
//var s = ss.getSheetByName('Sheet1');
//var r = s.getRange('A:E');
//var v = r.getValues();
var lastrow = getLastPopulatedRow(v);
var green = new Array();
var red = new Array();
var blue = new Array();

for(var i=0;i<lastrow;i++) {
if(v[i][2]=='green')
green.push(v[i]);
else if(v[i][2]=='red')
red.push(v[i]);
else if(v[i][2]=='blue')
blue.push(v[i]);
}

var combinations = new Array();
var c=0;
var total = new Array();

for(var w=0;w<green.length;w++) {
for(var x=0;x<red.length;x++) {
for(var y=0;y<blue.length-1;y++) {
for(var z=y+1;z<blue.length;z++) {
combinations[c] = new Array();
combinations[c].push(green[w]);
combinations[c].push(red[x]);
combinations[c].push(blue[y]);
combinations[c].push(blue[z]);
total.push([c,(green[w][3]+red[x][3]+blue[y][3]+blue[z][3]),(green[w][4]+red[x][4]+blue[y][4]+blue[z][4])]);
c=c+1;
}
}
}
}
var total = total.sort(function(a,b) { return a[1] - b[1]; });
//Logger.log(total);
for(var j=total.length-1;j>=0;j--)
if(total[j][2]<=30) break;

return combinations[total[j][0]];
};

function getLastPopulatedRow(data) {
for (var i=data.length-1;i>=0;i--)
for (var j=0;j<data[0].length;j++)
if (data[i][j]) return i+1;
return 0;
};

////////////////////////////////////////////////////////