[Solved] Reorganizing Google Sheets data dynamically

by


Yes, it’s possible.

One way is to use arrays and the QUERY function.

For simplicity, let say that

  • Columns A and B have the general information of the order
  • Columns C and D have the data for version 1
  • Columns E and F have the data for version 2
  • Columns G and H have the data for version 3

On the output sheet, add the headers.
Below of them add a formula like the following:

=ArrayFormula(QUERY({A2:B,C2:D,ROW(A2:A);IFERROR(LEN(A2:B)/0),E2:F,ROW(A2:A);IFERROR(LEN(A2:B)/0),G2:H,ROW(A2:A)},"select Col1,Col2,Col3,Col4 where Col3<>'' order by Col5"))

  • References start on row 2 to skip the headers to avoid to include them on the output sheet.
  • ROW(A2:A) is used to keep the order
  • IFERROR(LEN(A2:B)/0) is a “trick” used to “hide” the order (general information) data for the second and following rows for the same order. On the select parameter of the QUERY function, it’s referrey as Col5 on the order by clause.
  • It’s assumed that lookup-choice-1 will never be empty.

NOTES:

  1. If more columns were added, the column numbers should be updated accordingly
  2. Don’t use the order by clause to sort the result by the general information columns because the “trick” to hide the “labels”. If you need to apply a sort, do it’ before applying the above formula, you could do this by sorting the source range through the Data > Sort range… feature, so the data is sorted before it’s transformed by the above formula.

See also

9

solved Reorganizing Google Sheets data dynamically