Google Sheet - How to FULL OUTER JOIN with one table?

Question

I would like to create a tables for my tournament. I create a table with some players and their leagues.

I would like to generate matchmaking table, by league, like that

I'm to weak with google spreadsheet to do that. I understood I can't use "JOIN" with "QUERY()" function. It seems I need to use "ArrayFormula()" and "VLOOKUP()" functions but after hours pasted to try, I failed.

If anyone of you can help me, it will be so great !

Here is a googlesheet with datas : https://docs.google.com/spreadsheets/d/19ThnwVme8f3Ee730w8lTAyEJE9YdxzLEfox8arl5Q4o/edit?usp=sharing

Thanks a lot, I hope my problem is clear :)

Edit : Seems we are limited by number of characters in REPT() function. If there is an other solution than the answer or a "workaround", feel free to share it :)

question from:https://stackoverflow.com/questions/65600837/google-sheet-how-to-full-outer-join-with-one-table

Answer 1

try:

=ARRAYFORMULA(UNIQUE(QUERY(SPLIT(IF(
 FLATTEN(SPLIT(REPT(CONCATENATE(IF(A3:A="";;A3:A&"?"&B3:B&"?"&C3:C)&"?"); COUNTA(A3:A)); "?"))<
 FLATTEN(SPLIT(CONCATENATE(REPT(IF(A3:A="";;A3:A&"?"&B3:B&"?"&C3:C)&"?"; COUNTA(A3:A))); "?")); 
 FLATTEN(SPLIT(REPT(CONCATENATE(IF(A3:A="";;A3:A&"?"&B3:B&"?"&C3:C)&"?"); COUNTA(A3:A)); "?"))&"?"&
 FLATTEN(SPLIT(CONCATENATE(REPT(IF(A3:A="";;A3:A&"?"&B3:B&"?"&C3:C)&"?"; COUNTA(A3:A))); "?"));
 FLATTEN(SPLIT(CONCATENATE(REPT(IF(A3:A="";;A3:A&"?"&B3:B&"?"&C3:C)&"?"; COUNTA(A3:A))); "?"))&"?"&
 FLATTEN(SPLIT(REPT(CONCATENATE(IF(A3:A="";;A3:A&"?"&B3:B&"?"&C3:C)&"?"); COUNTA(A3:A)); "?"))); "?"); 
 "select Col1,Col2,Col4,Col5,Col6
  where Col1 <> Col4 
    and Col3 =  Col6")))