google-sheetsformulasgoogle-query-language

Google spreadsheet query formula - join like

I got this case:

A; B; C; D
a; 3; b; 4
e; 6;  ;  
b; 5; c; 6
c; 4; a; 2
d; 5

I would like to build:

E; F; G
a; 3; 2
b; 5; 4
c; 4; 6
d;  ; 5
e; 6;

I tried some queries without sucess and didn't found a join simple solution... Can you help me to find a solution please ?

Thanks.

Solution

  • You could try this formula in cell E1:

    =SORT(UNIQUE(QUERY({A:A;C:C},"where Col1 <> ''")),1,1)

    and then this formula in cell F1 and dragged down the column:

    =TRANSPOSE(QUERY({A:B;C:D},"select Col2 where Col1='"&E1&"'"))

    See this example sheet to see these working: https://docs.google.com/spreadsheets/d/11opTmoxyEBllBE-3y9UkL0Cd8fX2aiKBJK-lZO5OVIo/edit?usp=sharing

    Also, it is possible to create the output in a single cell (see cell Q1) but the formula is not very adaptable