find all cells with same ref and subtract accordingly

[Felix Atagong]

I have a file with +/- 1000 reference numbers (col A), bin location (col B) and the amount of pieces in that bin. I call it Stock.xls.

Another sheet will give me what piece I have to take and the amount to take: Order.xls.

I would like that my 'picking note' tells me how many pieces to take from what bin and of course that the new stock result after picking is saved as well.

Example: If I need 20 x P346 and my stock says:
P345 - A3A5 - 15
P346 - A1B3 - 20
P346 - A2B3 - 15
P346 - A1A2 - 3
P347 - C1C1 - 15

The 'picking note' would have to empty the lowest bin first up till the highest:
P346 - A1B3 - 20 -> 2 OUT
P346 - A2B3 - 15 -> 15 OUT
P346 - A1A2 - 3 -> 3 OUT

and return the result

P346 - A1B3 - 18
P346 - A2B3 - 0
P346 - A1A2 - 0

Possible?

[Bob Phillips]

Why wouldn't it just take 20 from

P346 - A1B3

[Felix Atagong]

Why wouldn't it just take 20 from P346 - A1B3
Fine for me, but if I need to subtract 30, it still needs to make 1 bin completely empty and discount the rest (10) from another bin (and readjust the stock).