Google Sheets: Merge two tables (arrays) with many to many relationship

Question

I have 2 tables (arrays) in Google Sheets, that they have a related column. And I need to join these tables. What I need is get a result being that the two tables have a many-to-many relationship.

Example sheet

In this example, I have two tables (A:C and E:G). I need a result like table I:N.

Table A:C -> I have players and how much them has completed certain game. (key: 'player'+'game')

Table E:G -> I have groups that each one has different goals for some games. (key: 'group'+'game')

I need a result that column 'game' is the related column of each table.

Note that:
row 15 of first table: (d - b6 - 55%), and
row 7 of second table: (k7 - b7 - 60%),
have no relation, so they aren't on third table.

I've tried to combine some formulas like ARRAYFORMULA, VLOOKUP, FILTER, INDEX, OFFSET... But I hadn't success with none of them.

I've found a solution with script SUPERSQL() Function, but I wouldn't like to use scripts, just formulas.

Done! https://docs.google.com/spreadsheets/d/1UoJiVkm1yRKhHWxI8YPd-47tGv5GFkYdx3Ph_N9KsN4/edit?usp=sharing I have two tables on the ranges: 'A:C' and 'E:G'. I desire get 'I:N' with formulas, at this example I filled up manually. — rodcord, Aug 10 '22 at 19:58
Your best option is `SUPERSQL` but you wouldn't like to use scripts. — Osm, Aug 10 '22 at 20:25
My problem using `SUPERSQL` is because my other scripts stop working. :( — rodcord, Aug 17 '22 at 01:52

score 0 · Answer 1 · answered Aug 21 '22 at 09:44

Main idea is to place one key column vertically as it is, and another one horizontally (TRANSPOSE()).

Outside QUERY is just for rearranging columns and sorting.

More on TRANSPOSE(QUERY(TRANSPOSE(...))) and such is here.

Solution:

=QUERY(
  {
    A1:C1\ E1:G1;
    ARRAYFORMULA(
      SPLIT(
        REGEXREPLACE(
          QUERY(
            FLATTEN(
              IF(
                FILTER(B2:B; B2:B <> "") <> TRANSPOSE(FILTER(F2:F; F2:F <> ""));;
                    TRANSPOSE(QUERY(TRANSPOSE(FILTER(A2:C & "@"; B2:B <> ""));; COLUMNS(A2:C))) & " "
                  & QUERY(TRANSPOSE(FILTER(E2:G & "@"; F2:F <> ""));; COLUMNS(E2:G))
              )
            );
            "WHERE Col1 IS NOT NULL";
          );
          "@$";
        );
        "@ ";
      )
    )
  };
  "SELECT Col4, Col1, Col2, Col6, Col3
   ORDER BY Col4, Col1, Col2";
  1
)

Google Sheets: Merge two tables (arrays) with many to many relationship

1 Answers1