I have two spreadsheets. The first is a list of products, the second is a list of sales.
The products spreadsheet includes four columns: "SKU", "Shipment ID", "Quantity Purchased", and "Remaining Quantity."
A B C D
SKU Shipment ID Qty Purchased Remaining Qty
1 20121 X2992 40 38
2 20121 X8483 40 40
3 71662 X2242 40 39
The sales spreadsheet includes three columns: "SKU", "Shipment ID", and "Quantity Sold."
A B C
SKU Shipment ID Qty Sold
1 20121 X2992 1
2 71662 X2242 1
3 20121 X2992 1
I want "Remaining Quantity" to be calculated. Each row of this column should take the "Quantity Purchased" from it's own row and subtract the "Quantity Sold" from the sales spreadsheet for every row where the "SKU" and "Shipment ID" match the "SKU" and "Shipment ID" from its own row. What is the formula for this? (Many formulas are the same in Excel, so I'm tagging Excel as well.)
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…