I have two independent columns with data.

enter image description here

How could I use that data to then produce a flattened table like this:

enter image description here

  • You haven't shown any row or column information, so it's hard to suggest a specific formula to try, since formulas are written around specific range criteria.
    – Erik Tyler
    May 18 at 23:17

2 Answers 2


See my comment to your post above for a tip regarding future posts.

That said, suppose that your "A" from the first image is in cell A2 and your "1" from the first image is in cell C2; and suppose that there is nothing in the rows below what is shown in the first image, all the way to the bottom of the sheet.

=ArrayFormula(QUERY(SPLIT(FLATTEN(FILTER(A2:A,A2:A<>"")&"~"&TRANSPOSE(FILTER(C2:C,C2:C<>""))),"~",1,0),"Select * WHERE Col1 Is Not Null AND Col2 Is Not Null"))

ArrayFormula( ) signifies that entire ranges should be processed at once.

FILTER(A2:A,A2:A<>"")&"~"&TRANSPOSE(FILTER(C2:C,C2:C<>"")) forms a 2D grid of every non-null element from A2:A with every non-null element from C2:C joined by a tilde ~.

FLATTEN creates one column from the two-column grid.

SPLIT splits those elements into two columns again at the temporary tilde character from above.

QUERY(__________,"Select * WHERE Col1 Is Not Null AND Col2 Is Not Null") retains only the resulting rows where neither is blank.

  • Fantastic, this works, thank you. And especially thanks for the detailed explanation on each of the steps. Noted about the missing column info in the post above.
    – evsc
    May 19 at 13:34
  • You're welcome. Glad everything worked out.
    – Erik Tyler
    May 19 at 21:30

The outcome is obtained by an SQL CROSS JOIN: enter image description here

The Formulas by Top Contributors Addon provides that.

Disclaimer: I've created and maintain the Addon

  • This looks great. Maybe one day google will add it their functions! For now, I am reluctant to use an addon as multiple people will interact with the sheet. Which I assume would mean everyone would have to have the addon installed?
    – evsc
    May 19 at 13:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.