Search code examples
google-sheetsgoogle-sheets-formulaspreadsheetarray-formulasgoogle-query-language

Google Sheet - How to deal with REPT() max size to do a FULL OUTER JOIN

In addition on my last question Google Sheet - How to FULL OUTER JOIN with one table?, I see REPT() function is limited with 32k characters.

How can we deal with it to make a FULL OUTER JOIN ?

In addition, you can see an example with 2 sheets on https://docs.google.com/spreadsheets/d/19ThnwVme8f3Ee730w8lTAyEJE9YdxzLEfox8arl5Q4o/edit?usp=sharing

Thanks a lot for your answers :)

Solution

  • try:

    =ARRAYFORMULA(QUERY(SPLIT(FLATTEN(QUERY(
 TRANSPOSE(QUERY(UNIQUE(IF(INDEX(SPLIT(FLATTEN(
 FILTER(A3:A&"♦"&B3:B&"♦"&C3:C, A3:A<>"")&"♠"&TRANSPOSE(
 FILTER(A3:A&"♦"&B3:B&"♦"&C3:C, A3:A<>""))), "♠"),,1)<INDEX(SPLIT(FLATTEN(
 FILTER(A3:A&"♦"&B3:B&"♦"&C3:C, A3:A<>"")&"♠"&TRANSPOSE(
 FILTER(A3:A&"♦"&B3:B&"♦"&C3:C, A3:A<>""))), "♠"),,2), QUERY(SPLIT(FLATTEN(
 FILTER(A3:A&"♦"&B3:B&"♦"&C3:C, A3:A<>"")&"♠"&TRANSPOSE(
 FILTER(A3:A&"♦"&B3:B&"♦"&C3:C, A3:A<>""))), "♠"),
 "select Col1,Col2", 0), QUERY(SPLIT(FLATTEN(
 FILTER(A3:A&"♦"&B3:B&"♦"&C3:C, A3:A<>"")&"♠"&TRANSPOSE(
 FILTER(A3:A&"♦"&B3:B&"♦"&C3:C, A3:A<>""))), "♠"),
 "select Col2,Col1", 0))), 
 "where Col1<>Col2", 0)),,9^9)), "♦ ", 1, 1),
 "select Col1,Col2,Col4,Col5,Col6
  where Col1 <> Col4 
    and Col3 =  Col6", 0))

    enter image description here

    EU syntax:

    =ARRAYFORMULA(QUERY(SPLIT(FLATTEN(QUERY(
 TRANSPOSE(QUERY(UNIQUE(IF(INDEX(SPLIT(FLATTEN(
 FILTER(A3:A&"♦"&B3:B&"♦"&C3:C; A3:A<>"")&"♠"&TRANSPOSE(
 FILTER(A3:A&"♦"&B3:B&"♦"&C3:C; A3:A<>""))); "♠");;1)<INDEX(SPLIT(FLATTEN(
 FILTER(A3:A&"♦"&B3:B&"♦"&C3:C; A3:A<>"")&"♠"&TRANSPOSE(
 FILTER(A3:A&"♦"&B3:B&"♦"&C3:C; A3:A<>""))); "♠");;2); QUERY(SPLIT(FLATTEN(
 FILTER(A3:A&"♦"&B3:B&"♦"&C3:C; A3:A<>"")&"♠"&TRANSPOSE(
 FILTER(A3:A&"♦"&B3:B&"♦"&C3:C; A3:A<>""))); "♠");
 "select Col1,Col2"; 0); QUERY(SPLIT(FLATTEN(
 FILTER(A3:A&"♦"&B3:B&"♦"&C3:C; A3:A<>"")&"♠"&TRANSPOSE(
 FILTER(A3:A&"♦"&B3:B&"♦"&C3:C; A3:A<>""))); "♠");
 "select Col2,Col1"; 0))); 
 "where Col1<>Col2"; 0));;9^9)); "♦ "; 1; 1);
 "select Col1,Col2,Col4,Col5,Col6
  where Col1 <> Col4 
    and Col3 =  Col6"; 0))

    shorter fx:

    =INDEX(QUERY(SPLIT(FLATTEN(IF(
 ROW(INDIRECT("A1:A"&COUNTA(A3:A)))<=TRANSPOSE(
 ROW(INDIRECT("A1:A"&COUNTA(A3:A))));;
 FILTER(A3:A&"♦"&B3:B&"♦"&C3:C; A3:A<>"")&"♦"&TRANSPOSE(
 FILTER(A3:A&"♦"&B3:B&"♦"&C3:C; A3:A<>"")))); "♦"); 
 "select Col4,Col5,Col1,Col2,Col6
  where Col2 is not null 
    and Col3 =  Col6
  order by Col6,Col4"; 0))

    enter image description here