CODEWITHSUNDEEP
arraysgoogle-sheetsgoogle-sheets-formulaarray-formulasgs-vlookup
Ludvig Hansson
Ludvig Hansson

Reputation: 23

Join tables in google sheet - full join

I have two tables in google spreadsheet. They have a common unique identifier (Account id). Now I need to join these tables into a third table containing all rows from both tables.

Please have a look at this sheet:

screen on spreadsheet

or follow the link to an example spreadsheet: https://docs.google.com/spreadsheets/d/17ka2tS5ysXqJnrpCxCTwNmCsTORPFP1Gatq4p1fPldA/edit?usp=sharing

I have manage to join the tables using this arrayformula:

=ARRAYFORMULA({G3:H8,VLOOKUP(G3:G8,{A3:A7,B3:C7},{2,3},false)})

But with this formula the joined table "misses" two rows:

20  N/A Klaus   Berlin
4   VW  David   Paris

The first missing row is found only in Table 1. The second missing has an ID that is found in Table 2 and has two (2) matching ID's in Table2, but only one row in the joined table

Is there a way to provide a formula that can handle this?

Upvotes: 2

Views: 7182

Answers (2)

Wicket
Wicket

Reputation: 38424

Formula

=ArrayFormula(UNIQUE({
VLOOKUP({A3:A7;G3:G8},{G3:H8;{A3:A7,IF(A3:A7,)}},{1,2},FALSE),
{B3:C7;VLOOKUP(G3:G8,{A3:C7;{G3:G8,IF(G3:G8,),IF(G3:G8,)}},{2,3},FALSE)}
}))

Result

Explanation

  • {A3:A7;G3:G8} creates a list of all the account ids (it includes duplicates)
  • {G3:H8;{A3:A7,IF(A3:A7,)}} creates an array having three columns with values from Table 2 and blanks for ids from Table 1
  • {A3:C7;{G3:G8,IF(G3:G8,),IF(G3:G8,)}} creates an array having three columns with values from Table 1 and blanks for ids from Table 2
  • The first vlookup populates the first two columns (account id and car)
  • {B3:C7;VLOOKUP(G3:G8,{A3:C7;{G3:G8,IF(G3:G8,),IF(G3:G8,)}},{2,3},FALSE)} populates the third and fourth columns (name and city)
  • UNIQUE removes duplicates

Related

Upvotes: -1

player0
player0

Reputation: 1

previous answer is incorrect. use:

=ARRAYFORMULA(QUERY(UNIQUE(IFNA({G2:H10, 
 VLOOKUP(G2:G10, A2:C10, {2, 3}, 0);
 VLOOKUP({A3:A10; G3:G10}, {G3:H10; {A3:A10, IF(A3:A10, )}}, {1, 2}, 0), {B3:C10; 
 VLOOKUP(G3:G10, {A3:C10; {G3:G10, IF(G3:G10, ), IF(G3:G10, )}}, {2, 3}, 0)}})), 
 "where Col1 is not null order by Col1", 1))

0

Upvotes: 2

Related Questions