# Google sheet, from 2 lists to flattened table

I have two independent columns with data.

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

• 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. May 18 at 23:17

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. May 19 at 21:30

The outcome is obtained by an SQL CROSS JOIN:

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