Joining two tables on a google sheet

Question

Every day I will download a "product inventory" list from an third party source, the list is in the format like this:

Product Code	Product Name	Quantity
1	Apple	100
2	Pear	200
3	Banana	150
4	Grape	400

However, not all product shows up everyday. In the next day the report may look like this:

Product Code	Product Name	Quantity
1	Apple	120
3	Banana	200
4	Grape	320
5	Kiwi	80

And I want to be able to join these two tables and build an "product movement" report like the following

Product Code	Product Name	Delta
1	Apple	+20
2	Pear	-200
3	Banana	+50
4	Grape	-80
5	Kiwi	+80

Could someone please give me some pointers on how to achieve this?

Answer 1

Put the first table in Sheet1 starting at cell A1, and the second table in Sheet2 starting at cell A1.

Then use this formula in Sheet3:

=arrayformula( 
  query( 
    { 
      if( 
        isnumber(Sheet1!A1:C) * (column(Sheet1!A1:C) = column(C1)), 
        - Sheet1!A1:C, 
        Sheet1!A1:C 
      ); 
      Sheet2!A1:C 
    }, 
    "select Col1, Col2, sum(Col3) 
     where Col1 is not null 
     group by Col1, Col2", 
    1 
  ) 
)

Format the third column like this:

Format > Number > More formats > Custom number format > +#,##0;-#,##0;±0.