Google sheets - cross join / cartesian join from two separate columns

user_15025822 0
Viewed 3110

Hope that someone can help me with a cartesian product in Google Sheets. I have data in two separate columns and wish to create all possible combinations of the two columns in a separate tab. The first column is ID (text) and the second is date format. The output should be two separate columns. The formula should be dynamic, i.e. list should update when new IDs or dates are added to the input lists.

I have looked for solutions online, but haven't found a solution that works. I'm proficient in Excel but not so much Google Sheet :)

Here is a sample sheet: https://docs.google.com/spreadsheets/d/150uIg3XH1hxZa8vSxDhcVZVOcSEOp175OPYL4Rc-wWI/edit?usp=sharing

google-sheets-formula google-sheets cross-join cartesian-product
3 Answers

Use this:

=ARRAYFORMULA(
  SPLIT(
    FLATTEN(
        FILTER('input 1'!A2:A, 'input 1'!A2:A <> "")
      & ","
      & TRANSPOSE(FILTER('input 2'!A2:A, 'input 2'!A2:A <> ""))
    ),
    ","
  )
)

in cell A3 but make sure you reformat column B into date (copy the format from column E):

enter image description here

user_112252910

I think soMarios's solution is better in your case, but here is another solution just to show a different approach. With some work this approach could be generalized for any number of columns.

=ARRAYFORMULA(
  {
    VLOOKUP(
      INT(SEQUENCE(COUNTA('input 1'!A2:A) * COUNTA('input 2'!A2:A), 1,) / COUNTA('input 2'!A2:A)) + 1,
      {SEQUENCE(COUNTA('input 1'!A2:A)), FILTER('input 1'!A2:A, 'input 1'!A2:A <> "")},
      2,
    ),
    VLOOKUP(
      MOD(SEQUENCE(COUNTA('input 1'!A2:A) * COUNTA('input 2'!A2:A), 1,), COUNTA('input 2'!A2:A)) + 1,
      {SEQUENCE(COUNTA('input 2'!A2:A)), FILTER('input 2'!A2:A, 'input 2'!A2:A <> "")},
      2,
    )
  }
)

enter image description here

user_2798060

try:

=INDEX(SPLIT(FLATTEN(
 OFFSET('input 1'!A2,,,COUNTA('input 1'!A2:A))&"♠"&TRANSPOSE(
 OFFSET('input 2'!A2,,,COUNTA('input 2'!A2:A)))), "♠"))

enter image description here

and format B column as date

user_56326290
Related