I have a table here where I need to populate in in columns for charting purposes, but it needs to be automated so the formula needs too be there waiting for the data.

I have already found a formula for that:

=ARRAYFORMULA(SPLIT(FLATTEN(C3:C1000&"|"&D2:J2&"|"&D3:J1000),"|"))

but it seems like it is repeating the headers, see sample sheet: https://docs.google.com/spreadsheets/d/1-XIvAUt8b8atv37YvItvWUaa8HAmF3VaOUQUqVkff3E/edit#gid=0

Formula is in Column G.

Is there a way to modify the formula into a much cleaner result where it doesn't repeats the header at the bottom?

CodePudding user response：

use:

=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(A2:A1000&"|"&B1:E1&"|"&B2:E1000),"|"), 
 "where Col3 is not null", ))