CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
jbm
jbm

Reputation: 35

Google Sheets Cross Join Function Tables with More than Two Columns

The crossJoin function posted by @Max Makhrov from the below thread works almost completely for what I was hoping to achieve. It was in response to cross joining two columns and I tried joining two tables, one with two columns and one with five columns. It works but only partially.

  1. The delimiter of the column data is stuck as comma ",". This could be problematic for values with commas. The delimiter variable in the function only defines the two ranges being joined.
  2. If the column being joined is a date for example, it seems to extend out the full date text inclusive of time zone and fixed as text. Is there a way to allow for it to be non-text to be formatted? Even when it's parsed using the split() function it's definitely still text.

Result of JOIN is longer than the limit of 50,000 characters

Below is a link to the example input and output. The first output example is a standard cross join. The other is the actual desired output which filters for any data rows where the date in column 5 is greater than or equal to the date in column 2.

https://docs.google.com/spreadsheets/d/1FGS8lYyy60AH49Qyug8Uxaey5jxDksihOks7ll8Hq10/edit?usp=drivesdk

Upvotes: 0

Views: 749

Answers (1)

MattKing
MattKing

Reputation: 7783

Your spreadsheet is View Only, so i can't demo it there, but try this. On the demo sheet, start a new tab, then put this formula in cell A2.

Happy to walk you through it a bit if it works. Otherwise, maybe make the sample editable so i can troubleshoot w/ you in the same place?

=ARRAYFORMULA(QUERY({HLOOKUP({"A","B"},{"A","B";Sheet1!A5:B},SEQUENCE(COUNTA(Sheet1!D5:D)*COUNTA(Sheet1!A5:A),1,0)/COUNTA(Sheet1!D5:D)+2),HLOOKUP({"D","E","F","G"},{"D","E","F","G";Sheet1!D5:G},MOD(SEQUENCE(COUNTA(Sheet1!D5:D)*COUNTA(Sheet1!A5:A),1,0),COUNTA(Sheet1!D5:D))+2)},"where Col2>=Col5"))

Upvotes: 2

Related Questions