Inventory Ageing

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • 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

    If your file is that large that you need to compress it (.rar) then I suggest you provide us with a much smaller file. All we need is enough data to show what you are working with, and some sample answers showing what you expect - we don't need your whole file



  • 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,

    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"]


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



  • 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.

  • Re: Inventory Ageing

    try below:


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!