I am using a filter formula to output multiple results based on a condition. Each result contains 4 columns of data, each result is in a new row. However, I would like all results to be output one after the other (i.e. in one row). The data of each result takes up 4 columns and in the fifth column, the new result starts with 4 columns.
This is how it looks now:
This is how it should look:
To get this dynamically, follow these steps.
1 - in the sheet where you want the results on cell A2 past this formula.
=TRANSPOSE(FLATTEN(QUERY(Data!A2:D," Select * where A is not null ")))
TRANSPOSE because FLATTEN formula outputs a column.QUERY to remove blank rows.FLATTEN to convert all values from one or more ranges into a single column.2 - to make the header dynamic you need to paste this formula in resault sheet in cell A1.
=SPLIT(REPT(JOIN(";",Data!A1:D1)&";",COUNTA(Data!A2:A)),";")
JOIN the header with ; so you can split it with the SPLIT formula after repeating the header rang n time with the REPT formula, to handel how many times the header repeated just use COUNTA to count how many rows are there.Input example:
Output example:
You first need to use Flatten() to make it a one dimensional array since the spreadsheet data is in a nested array no matter how many rows/columns you have.
Then from here you can just transpose the data so instead of the data going down the rows it will be transposed to go horizontally to the columns.
Try:
=TRANSPOSE(FLATTEN(A2:D))
To combine it with your formula just replace the Range "A2:D" with your current formula.
Result:
References: