Skip to content

Instantly share code, notes, and snippets.

@Max-Makhrov

Max-Makhrov/Cross-join-Google-Sheets2.1.sheets

Last active May 5, 2021 08:48
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 2 You must be signed in to fork a gist
  • Save Max-Makhrov/ce3b06e2bcb1ed2d3a8be4fd1b4c48d0 to your computer and use it in GitHub Desktop.
Save Max-Makhrov/ce3b06e2bcb1ed2d3a8be4fd1b4c48d0 to your computer and use it in GitHub Desktop.
Download ZIP
Formulas for Cartessian cross-join in Google Sheets
Raw
Cross-join-Google-Sheets2.1.sheets
=ArrayFormula(
VLOOKUP(
CEILING(ROW(OFFSET(INDIRECT("A1"),,,COUNTA(A2:A)*COUNTA(B2:B)))/COUNTA(B2:B)) ,
{ROW(A2:A)-row(A1),A2:A} , 2, )
)
Raw
Cross-join-Google-Sheets2.2.sheets
=ArrayFormula(
VLOOKUP(
MOD(ROW(OFFSET(INDIRECT("A1"),,,COUNTA(A2:A)*COUNTA(B2:B)))-1,COUNTA(B2:B))+1 ,
{ROW(B2:B)-ROW(B1) , B2:B} , 2 ,)
)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment