I have a spreadsheet with multiple columns. Each column represents a recipe. Each column has a week number (e.g. 2, 3, 4)... Below the week number, we have a variable number of ingredients. I want to be able to merge all the ingredients from a given week into a single column.

Sample Data

I know how to statically merge ranges e.g. {B4:B20;C4:C20} and I can also think of using an if statement to check the week value e.g. =if(B1=2,{B4:B20;C4:C20}) but that's not close to what I want. I need to check the range B1:Z1 for the value of the week and if that value is X (let's say 2) then take the ingredients in B2:B and tack them on to C2:C and so on...

CodePudding user response：

If I'm understanding you correctly, this should do it:

=QUERY(FLATTEN(FILTER(B3:Z,B1:Z1=2)),"WHERE Col1 Is Not Null")

FILTER keeps (i.e., "filters in") only the data in B3:Z where the header is 2.

FLATTEN forms one column (blank cells and all) from the FILTER results.

QUERY keeps only non-blank (i.e., Is Not Null) entries from that single-column list.