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?
>Solution :
use:
=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(A2:A1000&"|"&B1:E1&"|"&B2:E1000),"|"),
"where Col3 is not null", ))