Inventory Ageing

Hi,

I want to calculate the age of the product lying at the store, I scratched my head a lot but was not able to do so

I have a Invoice data in One Excel and Stock on Hand (SOH) data in another excel, I want Invoice no and Invoice date against the Customer name, Item code and Item Description.

My problem is I have huge data around 3 to 5 lakh rows in both files so cannot do it manually. So I need a VBA Code to perform the task and do it faster.

I have attached the sample file for your reference. Please check the data marked in red in Output file it is marked red because the Product i.e. Item has gone in two invoices, it should take the latest invoice first.

Any Help will be highly appreciated.

Thanks in Advance.

Re: Inventory Ageing

Please Help.......... its urgent

Re: Inventory Ageing

Dear FDibbins,

Thanks for the reply, I have attached the samples files for your reference which are not so bulky you can see the size of "Sample Files.rar" attached which is only 37.1 kb. I need the code urgent.

Re: Inventory Ageing

Quote from MrRedli;768946

hi Chand, why don't you just add a vlookup as only thing missing from invoice file is SOH

Dear MrRedli,

Thank you for your reply but with VLOOKUP I cannot achieve the desired output because as you can see in the file you attached when the item goes in 2 invoices the VLOOKUP gives the same value for all the duplicates or repeats and my problem is in my files the duplicates can be around 10 to 15 thousand so i cannot check and make changes manually.

Re: Inventory Ageing

Dear MrRedli,

Please see the attached file for your reference.

Re: Inventory Ageing

Sorry, Please find the attached file for your reference.

Re: Inventory Ageing

Dear MrRedli,

Thank you, but this formula does not help because when the item SOH qty is More than the first invoice qty it does not jump to another invoice for the same item instead it gives gives 0 in SOH column i.e. when you change the qty of "KC 220" from 3 to 6 in Stock Sheet the formula gives 6 value in invoice sheet and what i want is the formula should take 5 from first invoice and 1 from next invoice.
[TABLE="width: 141"]

[tr]

[TD="class: xl63, width: 141"][/TD]

[/tr]

[/TABLE]

Re: Inventory Ageing

Dear MrRedli,

Thankyou for reply but this formula does not help because for the second invoice for the same item it gives "0" in column SOH in Invoice sheet. for example if we change the qty for "KC 220" from to 3 to 6 in SOH sheet it should take 5 from first invoice and 1 from second invoice for the same Item.