To Fin out Purchases against Closing stock

[Prabhu]

Hi Friends,

Plz help to have Macro for the below requirement

I have part number wise purchase data and part number wise closing stock.
Now I need to identify the purchases against the closing stock i.e if I have 500 Qty in my closing stock I need to identify the last purchases up to the extent of 500 Qty considering First in First out (FIFO)

Example 1
I have 100 Qty of part number ABCD and purchases against the part number as below. Form the below purchase data I need to identify the last 100 Qty
purchase details

Purchase List
Received Date PO No Part Number details Qty Unit Pirce Value
02-02-2013 10 ABCD XXX 30 900 22500
03-03-2013 11 ABCD XXX 40 920 46000
06-03-2013 12 ABCD XXX 30 950 28500
06-04-2013 13 ABCD XXX 20 940 18800
19-05-2013 14 ABCD XXX 10 925 9250
10-05-2013 15 ABCD XXX 15 970 14550

Output need to be as below.
Received Date PO No Part Number details Qty Unit Pirce Value
03-03-2013 11 ABCD XXX 25 920 23000
06-03-2013 12 ABCD XXX 30 950 28500
06-04-2013 13 ABCD XXX 20 940 18800
19-05-2013 14 ABCD XXX 10 925 9250
10-05-2013 15 ABCD XXX 15 970 14550

In the output PO no. "11" reflecting only 25 instead of 40 since we are carrying only 100 Qty and we need to consider last 100 Qty purchase, hence it has considered that 15Qty has sold against Po No. "11"" and reflecting 25 as closing stock.
I have attached sample file for your reference Kindly help to get Macro to automate my routine activity.

[Admin]

Hi

Some links to FIFO calculation

First in First Out (FIFO) Inventory Valuation
FIFO REPORT - VBA Express Forum