Google-sheets – Filter by merged cells in Google Sheets

I've been working on your file, even before the bounty period, and it as been a struggle. I wanted to give you the automation te ARRAYFORMULA can give in combination with a bit of Google Apps Script. In doing so, I wanted to understand the calculations a bit and thus I started with optimizing the first formula for column E:

=ARRAYFORMULA(IF(A4:A<>"";ARRAYFORMULA(A4:A*D4:D);""))

For column H, I created to following formula:

=ARRAYFORMULA(IFERROR(VLOOKUP(G4:G30;Suits!A:AC;{MATCH(H2, suits!A1:AC1 ,0)}*SIGN(ROW(G4:G30));0),""))

For column I, I created the following script:

function skills(array1, array2, array3) {
  var array = new Array();

  for(i=0, len2=array2.length; i<len2; i++) {
    var prod = 1;
    for(j=0, len1=array1.length; j<len1; j++) {
      if("% " + array2[i][0] == array1[j][0]) {        
        prod *= array1[j][2] + 1;        
      }      
    }
    var value;
    if(array3[i][0] == "") {
      value = "";
    } else {
      value = prod * array3[i][0];
    }
    array.push([value]);
  } 
  return array;
}

For column J, I created te following script:

function mods(array1, ModAttrb, ModHeader, ModStats, array5, array6) {
  var array = new Array(), aSecond = new Array(), aThird = new Array();

  // second calculation  
  for(i=0, len1=array1.length; i<len1; i++) {    
    var secondSum=0;    
    for(j=0, len2=ModAttrb.length; j<len2; j++) {      
      if(array1[i][0] == ModAttrb[j][0]) {        
        for(l=1, len4=ModStats[0].length; l<len4; l++) {
          if(ModStats[j][l] == 0 || ModStats[j][l] == "") {
            secondSum += 0;
          } else {
           secondSum += ModStats[j][l];
          }
        }
      }
    } 
    aSecond.push(secondSum);
  } 

  // third calculation
  for(i=0, len1=array1.length; i<len1; i++) {
    var thirdSum=0;
    for(m=0, len5=array5.length; m<len5; m++) {
      if(array1[i][0] == array5[m][0]) {        
        thirdSum += parseInt(array5[m][3]);        
      }      
    }
    if(thirdSum == 0) {
      thirdSum = 0;
    } 
    aThird.push(thirdSum);
  }  

  for (var i=0; i < aSecond.length; i++) {
    var result = 0;
    result += (array6[i] * 1 + parseInt(aSecond[i]) + parseInt(aThird[i]));
    array.push([result])
  }  
  return array;
}

Here, for the sake of time, I set the first calculation to be 1.

After that, time was running short. The Mult column is/was difficult to comprehense. We can work on it afterwards if you want?

See te example file I copied in te beginning: Fitting Tool

Spreadsheet functions cannot delete rows. You need a script for this (introduction to Apps Script). The following function deletes any rows in Sheet2 where the entry in column E is less than 1 or greater than 9.

function deleteRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet2');
  var data = sheet.getDataRange().getValues();
  for (var i = data.length - 1; i >= 1; i--) {
    if (data[i][4] < 1 || data[i][4] > 9) {
      sheet.deleteRow(i + 1);
    }
  }
}  

Notes

• The indices of array data begin with 0; row and column numbers begin with 1. This is why there is i + 1 in the line with row deletion.
• The number 4 in data[i][4] corresponds to E, because 0,1,2,3,4... are A,B,C,D,E,...
• The rows are deleted from bottom up, so that deletion does not move the rows that are yet to be processed.