CODEWITHSUNDEEP
google-sheetsgoogle-sheets-query
Shijilal
Shijilal

Reputation: 2169

Google sheet query - combining two sets of data

I have tow sets of Data

Data    1       Data 2

   A      B        C      D

Tank No Gross   Tank No Packed
   1    7.00       1    6.09
   2    5.00       2    5.21
   3    7.50       3    7.03
   4    4.00       4    3.33
   5    6.00       5    2.00
   6    7.00       6    3.11
   7    7.00       7    8.11
   8    5.50       9    5.19
   9    8.00      10    7.92
  10    9.00      11    6.53
  11    7.50      12    7.70
  12    8.00      13    5.02
  13    8.00      14    8.21
  14    9.00      15    3.00
  15    7.00      16    6.66
  16    8.00      17    4.00
  17    4.00      19    5.22
  18    8.50      20    7.41
  19    9.50      21    5.85
  20    8.00        
  21    8.00

IN the second set of data Tank no 8 and 18 is missing. Now what i want is a result like this.

Tank No Gross   Packed
   1    7.00    6.09
   2    5.00    5.21
   3    7.50    7.03
   4    4.00    3.33
   5    6.00    2.00
   6    7.00    3.11
   7    7.00    8.11
   8    5.50    
   9    8.00    5.19
  10    9.00    7.92
  11    7.50    6.53
  12    8.00    7.70
  13    8.00    5.02
  14    9.00    8.21
  15    7.00    3.00
  16    8.00    6.66
  17    4.00    4.00
  18    8.50    
  19    9.50    5.22
  20    8.00    7.41
  21    8.00    5.85

But when i am using query 

=Query(Production,"Select A,B,D where A=C",1)

where Production is a named range of column A B C and D, i am not getting the desired results.

The result stops at Tank No 7. Its like this 

Tank No Gross   Packed
   1    7.00    6.09
   2    5.00    5.21
   3    7.50    7.03
   4    4.00    3.33
   5    6.00    2.00
   6    7.00    3.11
   7    7.00    8.11

How can i get my desired result using query or any other sheet functions. You can find the sheet with data here : https://docs.google.com/spreadsheets/d/1TQvm1jLktVb3JcMSN5J2oKEbZX9u4iQQ7LQECaPbQAE/edit?usp=sharing

Thanks

Upvotes: 1

Views: 382

Answers (2)

Grzegorz Mogilewski
Grzegorz Mogilewski

Reputation: 962

Solution:

Not the cleanest in the world but will do the trick

={
Query({Production},"select Col1,Col2",1),
ArrayFormula(IFERROR(VLOOKUP(QUERY({Production},"select Col1"),
Query({Production},"select Col3,Col4",1),2,0),))
}

The differences to pnuts solution (which is the simplest) are

  1. this formula reside only in one cell and produce report in form desired
  2. is based 100% on named range so you can put this wherever you want without worrying of references

Upvotes: 1

pnuts
pnuts

Reputation: 59495

Copy A3:B23 to E3 and in G3 and copied down to suit:

=iferror(vlookup(E3,C:D,2,0),"")

Upvotes: 1

Related Questions