Google spreadsheet query formula - join like

Question

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.

Answer 1

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://goo.gl/sv8ViD

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