**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 ss = SpreadsheetApp.getActiveSpreadsheet();

//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;

};

Now you can use the following custom function in your spreadsheet:

**=subset(A2:E)**

Following is the screenshot of the spreadsheet, showing the desired subset in range G2:K5

I have inserted the custom formula in cell G2.

## No comments:

## Post a Comment