Brandon
Brandon

Reputation: 3697

Formula for Outputting New Row for Each Non-Empty Cell in Specific Range

I have a range of data which I need to filter by non-empty cells within a specific sub-range, while generating a new row for each non-empty cell within that sub-range.

Observe the following example sheet, containing input data and desired output:

https://docs.google.com/spreadsheets/d/1c1lOtXpGbY9MwYHEoFIe1fxgW7TnygcgImCIIp2gzqM/edit?usp=sharing

For each "Type 1 - 4" cell that is not blank, a new row should be output containing the date, name, type and value from the input row.

What formula or functions are needed in order to accomplish this?

I am not new to using QUERY, VLOOKUP, and FILTER, and yet am unable to achieve the desired output via formula, nor even come up with anything that begins to get close.

Upvotes: 0

Views: 108

Answers (2)

Brandon
Brandon

Reputation: 3697

An alternative answer, similar to player0's in concept, but a bit simpler. It selects columns after the full array of ranges has been formed:

=arrayformula( 
  query( 
    { 
      B2:B, C2:C, iferror(F2:F/0, F2), F2:F; 
      B2:B, C2:C, iferror(G2:G/0, G2), G2:G; 
      B2:B, C2:C, iferror(H2:H/0, H2), H2:H; 
      B2:B, C2:C, iferror(I2:I/0, I2), I2:I 
    }, 
    "where Col4 is not null 
     order by Col1", 
    0 
  ) 
)

Via Google Product Forum.

Upvotes: 0

player0
player0

Reputation: 1

=ARRAYFORMULA({"Date", "Name", "Type", "Value"; QUERY({
 QUERY({B3:I, IF(F3:F<>"", $F$2, )},
 "select Col1, Col2, Col9, Col5 where Col9 is not null", 0);
 QUERY({B3:I, IF(G3:G<>"", $G$2, )},
 "select Col1, Col2, Col9, Col6 where Col9 is not null", 0);
 QUERY({B3:I, IF(H3:H<>"", $H$2, )},
 "select Col1, Col2, Col9, Col7 where Col9 is not null", 0);
 QUERY({B3:I, IF(I3:I<>"", $I$2, )},
 "select Col1, Col2, Col9, Col8 where Col9 is not null", 0)},
 "select * order by Col1 asc", 0)})

Upvotes: 1

Related Questions