Reputation: 3697
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
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
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