Inventory Ageing help FIFO

Hi there, yes you are spot on


1 item for 04/03/2011, 1 item for 16/03/2011 and 6 items for 17/03/2011.

would be the correct result

thanks

ok, what will the data table look like with 2 or 3 products then or we will have only one and always in row 2?

Hi

ive attached file with 4 different products...

apprecaite your help here

Thanks

for info

the actual file is 18098 rows of which 628 relate to actual inventory Qty and the remaining are the receipts...

Thanks again

please check attachment, run code "test". Output is on the new sheet.

Hi thanks very much, I wish I knew how to do this myself and your script calculates the dates exactly.... thank you very much indeed...

there is one thing tho... When it updates the data on the new sheet it only copies part of the data.

I have attached the master sporeadsheet with your macro to let you see what it does..

46C7577 is the last part number on the new sheet which is row 1902.

I tried to step thru the macro and noticed that it does run all the way thru to row 14746, but for some reason does not put all the info onto new sheet.

Thank again for your help. I hope are able to give me some help with getting all the data on to the new sheet.

Thanks
Craig

hi, cmcconna, yes I've noticed that as well but you were the first I was just going to update the file, I've corrected the code, now it will process everything to the end, please check

Fantastic, that is exactly what Im looking...

...Im going to spend some time looking at your code to see exactly what its doing as I have other projects this would be usefull in.

Thank you very much for help

if you are happy with the solution, please mark the thread as solved (see Forum rules for details)

Hi Watersev, I'am also find your code is very useful. However, it's a liter bit inconvenience when I always have to copy my data into this file then run macro. I have an idea like that: you can pack your code into an add-in file, named "aging report". In addition, when I click into add-in button in excel ribbon/menu, dialog boxes appear "please choose code column", "please choose Quantity column", "please choose date of receipt column", then I click ok, then the result data appear. (Like the way we choose function "subtotal" or "pivot table"). You can also put your weblink or your contact into the dialog boxes .

Hi Excel gurus

I am also interested in finding the age of my Inventory.
I have a list of purchases of securities. A purchase has a unique deal no and becomes a part of the inventory at settlement date.
I have a list of sales where, a sale is recorded for each Item with a unique deal no. there can be multiple sales for a security on the same day.

The output i require should list the remaining list of Purchase transaction with residual balance for a purchase for each security, if a part of it has been sold. The remaining Inventory needs to output on the basis of FIFO , up to a given report date.


Help will be greatly appreciated.

ok Arlu, thanks will do that.

Hi,
What is the solution if Closing qty is the last column of each row.

Thank you for your help...