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

0
votes

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

1
google-sheetsgoogle-sheets-formula

1 Answers

1
votes

Use this:

=ARRAYFORMULA(SPLIT(FLATTEN('input 1'!A2:A11&","&TRANSPOSE('input 2'!A2:A13)),","))

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

enter image description here