Finding difference in separate sheets using criteria to prepare for a pivot table

Hi Guys,

i'm trying to work out a way of subtracting items from one excel sheet onto another...

basically I have a SHIPPED SHEET, containing three fields,:

QUANTITY
SKU
WAREHOUSE LOCATION

the other sheet is called SHORTFALL, what i'm trying to do is minus the figures from the shortfall sheet onto my SHIPPED SHEET, this way i'm left with a SHIPPED figure MINUS SHORTFALL (if this makes any sense)
once this is done i can then build a pivot table up and add all SKU's together to get rid of duplicates, i hope this makes sense...


kind regards scott

Try in another column:

=A2-SUMPRODUCT(--(SHORTFALL!$B$2:$B$70=B2),--(SHORTFALL!$C$2:$C$70=C2),SHORTFALL!$A$2:$A$70)

copied down...

Again, if add that column in the Shipped sheet as mentioned, then use that column in your Pivot Table instead or in addition.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save