Auto generation of serial no. based on two criteria in Google Spreadsheet

Question

If column C has the word "Matured", and D has a valid date, then formula in column E should automatically generate a serial no., one higher than the previously existing serial numbers in column E.

Here is the link to the Google Spreadsheet shared and filled with sample data :

https://docs.google.com/spreadsheets/d/1XdcKbxKvIOSFK37zwULZns6giE9ounHPS5iHrRFkvIk/edit#gid=1992300894

Needless to say, the status keep changing from Pending to Matured at different dates.

Answer 1

You allow everyone to edit your spreadsheet? I hope you made a copy.

Anyway I wrote a script that should work. To use it you put this formula into E2. =genSerials(C2:D10, 1, "Matured", 2, true) The second argument is the column that contains the status. The third argument is the text that needs to be in the status column followed by the column to sort and then either true or false to indicate the sort order.

function genSerials(range, statusCol, statusText, dataToSortCol, ascending) {
  statusCol -= 1; dataToSortCol -= 1;

  var sorted = range.filter(function(row) {return row[statusCol] === statusText && row[dataToSortCol] !== "";}).map(function(row) {return row[dataToSortCol];});
  if(ascending) {
    sorted.sort(function(a,b) {if(a > b) return 1; else if(b > a) return -1; else return 0;});
  } else {
    sorted.sort(function(a,b) {if(a > b) return -1; else if(b > a) return 1; else return 0;});
  }

  var result = [];
  for(var i = 0; i < range.length; i++) {
    var row = range[i];
    if(row[statusCol] === statusText && row[dataToSortCol] !== "") {
      var idx = sorted.indexOf(row[dataToSortCol]);
      result.push([idx + 1]);
    } else {
      result.push([""]);
    }
  }

  return result;
}