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 :)

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")))