Google Sheets Cartesian product with transposed columns

Question

Is there recommended ways to convert table in A2:F6 to H2:K10?

Thanks.

Here is the sample Google Sheet https://docs.google.com/spreadsheets/d/1QOZVMIRvM_9lhvAflhQtA9H6KOpfHycPo5qgF1y-GzA/edit?usp=sharing

Answer 1

There is a relatively simple way to do than using FLATTEN:

={
  ARRAYFORMULA(A1:C1), "Product";
  ARRAYFORMULA(
    VLOOKUP(
      FILTER(
        FLATTEN(ROW(D2:F) + 0 * ISBLANK(D2:F)),
        FLATTEN(D2:F <> "")
      ),
      FILTER(
        {ROW(A2:A), A2:C},
        A2:A <> ""
      ),
      {2, 3, 4},
      0
    )
  ),
  FILTER(
    FLATTEN(D2:F),
    FLATTEN(D2:F <> "")
  )
}

Not sure why ARRAYFORMULA(ROW(D2:F)) gives row wise row numbers and not a 2d matrix with the row numbers. Could've remove 0 * ISBLANK(D2:F) otherwise...

Answer 2

Please use the following query formula:

=QUERY(QUERY({A:D;A2:C,E2:E;A2:C,F2:F},"where Col1 is not null order by Col1"), "where Col4 is not null")

Functions used:

• QUERY