CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulacartesian-productcross-join
William Pontoppidan
William Pontoppidan

Reputation: 77

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

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

Upvotes: 5

Views: 13164

Answers (4)

Le Hibou
Le Hibou

Reputation: 1591

With LAMBDA you could also use this without relying on string hacking:

=LAMBDA(
  range_1, range_2, count_1, count_2,
  {
    BYROW(
      INDEX(ROUNDUP(SEQUENCE(count_1 * count_2) / count_2)),
      LAMBDA(r, INDEX(range_1, r))
    ),
    BYROW
      (INDEX(MOD(SEQUENCE(count_1 * count_2) - 1, count_2) + 1),
      LAMBDA(r, INDEX(range_2, r))
    )
  }
)('input 1'!A2:A, 'input 2'!A2:A, COUNTA('input 1'!A2:A), COUNTA('input 2'!A2:A))

Upvotes: 1

player0
player0

Reputation: 1

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

Upvotes: -1

kishkin
kishkin

Reputation: 5325

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

Upvotes: 1

Marios
Marios

Reputation: 27390

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

Upvotes: 10

Related Questions