Friday, October 23, 2015

Google Spreadsheet return a subset given complex criteria


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!


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++) {
    else if(v[i][2]=='red')
    else if(v[i][2]=='blue')
  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();
  var total = total.sort(function(a,b) { return a[1] - b[1]; });
  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:

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