Need to Eliminate Rows where the Value column is blank, Google Sheets

Question

I have a sheets document that I am unpivoting to make the data more readable. In its unpivoted form there are 6 headers, the last of which are Item and Value. I need to eliminate rows that do not have anything in the Value column.

Here is the equation that I am using.

=ARRAYFORMULA(split(flatten(transpose(query(transpose(Sheet1!A3:E20&"|"),,9^9))&"|"&Sheet1!F2:EU2&"|"&Sheet1!F3:EU20),"|"))

I have been able to take the table I get and use filter to create the table that I want, however, I need this all to happen in one equation as the computation time needed in the initial equation is already too long to process all the information. I am hoping that by filtering the results as they are made that it will also speed up the render time.

Here is the filter I have had success with:

=FILTER(A:G, G1:G<>"")

I am working with over 16,000 rows of data in the initial sheet.

The other strategy I am working on is using an apps script and macro to set the equation above in, set the filter, copy the filter table, paste it in another sheet, then go back, change the previous equation, and repeat. This strategy is also very slow.

Any advice is welcome. I am new to working with sheets so sorry if there is any obvious solution that I am missing.

Thank you for the help.

Answer 1

Delete rows where Value is blank

Assume one header row and data starts of row 2

function myfunk() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("SheetName");
  const [hA,...vs] = sh.getDataRange().getDisplayValues();//assumed one header row and data start on row 2
  const idx = {};
  let d = 0;
  hA.forEach((h,i) => {idx[h]=i;});
  vs.forEach((r,i) => {
    if(r[idx['Value']]=="") {
      sh.deleteRow(i + 2 - d++);
    }
  });
}