Google Sheets – List All Records with Certain Criteria

I've added the result of your question in the Google Spreadsheet you shared with us.
In the SUMMARY sheet I collected all data that fits the criterion D<>0, with the following formula:

=QUERY(DATA!A:D;"SELECT A,C,D WHERE D<>0")

I also added an ID number, for the two companies and added the column to the summary sheet. As you can see, this formula can be easily extended.

I've built two solutions, the difference is whether it removes all empty cells in the selected range or only the ones where all cells in a row (in the selection) is empty.

Both solutions require an additional sheet "ScriptSheet", which you can hide.
Both solutions shift the remaining cells up, including the ones below the selections.

You can try both here, they can be found in a custom menu called "Scripts". Make a copy of the spreadsheet.

Solution 1

This script only removes cells if an entire row of cells in the selection is empty.

function removeEmptyRowsInSelection(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var scriptSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ScriptSheet");
  var sheetLastRow = sheet.getLastRow();
  var range = sheet.getActiveRange();
  var rangeFirstRow = range.getRow();
  var rangeLastRow = range.getLastRow();
  var rangeFirstCol = range.getColumn();
  var rangeLastCol = range.getLastColumn();
  var rangeCols = rangeLastCol - rangeFirstCol + 1;
  var maxRows = sheet.getMaxRows();
  var maxCols = sheet.getMaxColumns();
  var scriptSheetMaxRows = scriptSheet.getMaxRows();
  var scriptSheetMaxCols = scriptSheet.getMaxColumns();
  var insertRows = maxRows - scriptSheetMaxRows;
  var insertCols = maxCols - scriptSheetMaxCols;
  var currScriptRow = 1;
  
  scriptSheet.clear();
  
  if (insertRows > 0){
    scriptSheet.insertRows(1, insertRows);
  }
  
  if (insertCols > 0){
    scriptSheet.insertCols(1, insertCols);
  }
  
  
  for(var i = rangeFirstRow; i <= rangeLastRow; i++){

    var currRange = sheet.getRange(i, rangeFirstCol, 1, rangeCols);
    var currValue = currRange.getValues().join();
    var emptyValue = '';
    for (j = rangeCols; j > 1; j--){
      emptyValue = emptyValue + ',';
    }
    if(currValue != emptyValue){
      currRange.copyTo(scriptSheet.getRange(currScriptRow, 1));
      currScriptRow++;
      var test;
    } 
  }
  
  
  
  for(var currRow = rangeLastRow + 1; currRow <= sheetLastRow; currRow++, currScriptRow++){
    sheet.getRange(currRow, rangeFirstCol, 1, rangeCols).copyTo(scriptSheet.getRange(currScriptRow, 1));
  }
  
  var clearRange = sheet.getRange(rangeFirstRow, rangeFirstCol, maxRows, rangeCols);
  var scriptSheetRange = scriptSheet.getRange(1, 1, scriptSheet.getLastRow(), scriptSheet.getLastColumn());
  var pasteRange = sheet.getRange(rangeFirstRow, rangeFirstCol);
  
  clearRange.clear();
  scriptSheetRange.copyTo(pasteRange);
}

This function is much slower than the other one. It went through about 1000 lines in the first of three columns before canceled by the six minute limitation.

Solution 2

This solution removes all empty cells in the selection.

There is two functions in this one, one two find the first empty cell in a column. This function (as perhaps the rest as well) is probably possible to write more efficient.

function removeEmptyCellsInSelection(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var scriptSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ScriptSheet");
  var sheetLastRow = sheet.getLastRow();
  var range = sheet.getActiveRange();
  var rangeFirstRow = range.getRow();
  var rangeLastRow = range.getLastRow();
  var rangeFirstCol = range.getColumn();
  var rangeLastCol = range.getLastColumn();
  var maxRows = sheet.getMaxRows();
  var maxCols = sheet.getMaxColumns();
  var scriptSheetMaxRows = scriptSheet.getMaxRows();
  var scriptSheetMaxCols = scriptSheet.getMaxColumns();
  var insertRows = maxRows - scriptSheetMaxRows;
  var insertCols = maxCols - scriptSheetMaxCols;
  var currRow;
  var pasteCol = 1;
  
  scriptSheet.clear();
  
  if (insertRows > 0){
    scriptSheet.insertRows(1, insertRows);
  }
  
  if (insertCols > 0){
    scriptSheet.insertCols(1, insertCols);
  }
  
  
  
  for(var i = rangeFirstCol; i <= rangeLastCol; i++, pasteCol++){
    currRow = rangeFirstRow;
    for(var j = rangeFirstRow; j <= rangeLastRow; j++){
      var currValue = sheet.getRange(j, i).getValue();
      if(currValue != ''){
        var scriptSheetEmptyRow = getColumnLastRow(pasteCol, scriptSheet);
        sheet.getRange(j, i).copyTo(scriptSheet.getRange(scriptSheetEmptyRow, pasteCol));
      }
      currRow++;
    }
    var scriptSheetRow = getColumnLastRow(pasteCol, scriptSheet);
    for(; currRow <= sheetLastRow; currRow++){
      sheet.getRange(currRow, i).copyTo(scriptSheet.getRange(scriptSheetRow, pasteCol));
      scriptSheetRow++;
    }
  }
  
  var clearRange = sheet.getRange(rangeFirstRow, rangeFirstCol, maxRows, rangeLastCol - rangeFirstCol + 1);
  var scriptSheetRange = scriptSheet.getRange(1, 1, scriptSheet.getLastRow(), scriptSheet.getLastColumn());
  var pasteRange = sheet.getRange(rangeFirstRow, rangeFirstCol);
  
  clearRange.clear();
  scriptSheetRange.copyTo(pasteRange);
}

function getColumnLastRow(column, sheet){
  var row = 0;
  do{
    row++;
    var value = sheet.getRange(row, column).getValue();
  }while(value !== '')
    return row;
}

This solution is obviously faster the the previous one. It went through 1555 rows, 3 columns, in 02 minutes and 40 seconds.

I hope they can help!