0

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?

1
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.