-1

I'm trying to flatten a table and remove rows with a cell value of zero.

Here is what I've tried so far:

Table 2:

={"Customer Name", "Project Name", "Total Hrs", "Workforce","Hrs";ARRAYFORMULA(
     split(
        flatten(
           transpose(query(transpose(B3:D16&"|"),,9^9))&"|"&E2:H2&"|"&E3:H16
        ),"|"
     )
)}

My goals is to get to Table 3, but by manipulating the formula I have for Table 2 instead of using the filter formula in a different cell, if possible. Goal is to move from Table 1 to Table 3

Rubén
  • 34,714
  • 9
  • 70
  • 166
Adub
  • 1
  • 2
  • Why did you use the `google-apps-script` tag? are you looking for a script solution? – Marios Dec 01 '20 at 20:48
  • 1
    Yes - if there is a faster way to get from table 1 to table 3 using scripts, then I am open to that. I may have to repost my question to show the script I have so far. – Adub Dec 01 '20 at 20:52

1 Answers1

0

just query it:

={"Customer Name", "Project Name", "Total Hrs", "Workforce","Hrs";
 ARRAYFORMULA(QUERY(split(flatten(transpose(
 query(transpose(B3:D16&"|"),,9^9))&"|"&E2:H2&"|"&E3:H16 ),"|" ) , "where Col5 <> 0"))}
player0
  • 124,011
  • 12
  • 67
  • 124
  • 1
    Thank you! I actually found a solid script to 'unpivot' and then use a query to filter. https://stackoverflow.com/questions/24954722/how-do-you-create-a-reverse-pivot-in-google-sheets/43681525#43681525 – Adub Dec 01 '20 at 23:03