I have a matrix-like table like so:

  1 2 3 4 
1|  x y
3|      y
4|x y  

I want to create a summary table like so but I'm lost as to which functions to utilize:

1 2 x
1 3 y
3 4 y
4 1 x
4 2 y
  • If you can be more specific about the layout (e.g., your actual sheet name, the range in that sheet where the posted data would lie), it would be fairly easy to set you up with a solution.
    – Erik Tyler
    Commented Jan 4, 2022 at 2:17

1 Answer 1


See my comment to your original post. But for now, supposing that the data in your post were in a sheet named Sheet1 and that the upper-left space of the grid were in cell A1, try the following formula in a new sheet:

=ArrayFormula(QUERY(SPLIT(FLATTEN(Sheet1!A2:A&"~"&Sheet1!B1:Z1&"~"&Sheet1!B2:Z),"~",1,0),"Select * WHERE Col3 Is Not Null"))

This formula is written to be flexible and assumes both that your real grid is larger and that it may grow over time. Make sure that your sheet has columns at least through Col Z, or adapt the formula to swap out each Z for the rightmost column that the grid may actually encompass.

If your real grid will only contain only exactly what is show in your post, with that upper-left space in A1, you could limit the ranges to specific ending points:

=ArrayFormula(QUERY(SPLIT(FLATTEN(Sheet1!A2:A5&"~"&Sheet1!B1:E1&"~"&Sheet1!B2:E),"~",1,0),"Select * WHERE Col3 Is Not Null"))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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