Stock Aging- FIFO method

Date
24/08/12 -50
03/08/12 -2
03/08/12 245
27/07/12 -1
24/07/12 -10
17/07/12 -5
03/07/12 -6
02/07/12 -49
23/06/12 -15
17/06/12 -1
17/06/12 -20
09/06/12 147
26/02/12 -5
01/01/12 5

FIFO Method:

+ve is stock received, -ve is stock sold.

Received: 01/01/12 5 , Sold : 26/02/12 5 , bal =0
Received: 09/06/12 147, Sold : 17/06/12 to 27/07/12 107 Bal = 147 -107= 40
Received: 03/08/12 245, Sold : 03/08/12 2


Stock out 2 at 03/08/12 will use the stock balance 09/06/12:
Stock balance 09/06/12 : 40-2 = 38
Stock balance 03/08/12 : 245

Stock out 50 at 24/08/12 will use stock 09/06/12 and 03/08/12:
09/06/12 bal = 0
03/08/12 bal =245 -12 =233


Anyone know how to write the logic?

You wound need to create a Macro, I do not believe it is possible just using the Excel functions. Would a Macro be OK with you?

ok.Please help.

Originally Posted by xlbiznes

Hi,

Check this.

Logic is not right.

Actually the logic is right but because there are duplicates in the Date column, you do also have duplicates in Closing Qty column.
If you include, in the Date column, the Time also, you will get the right Closing Qty.
Otherwise, you would need a macro.

Any idea how to do it in Macro ?