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


    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


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

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

  • Re: Inventory Ageing


    try below:


    =IF(VLOOKUP(G2,stock!D:F,3,FALSE)>I2,IF(I2-SUMIF($G$1:G1,G2,$J$1:J1)<0,VLOOKUP(G2,stock!D:F,3,FALSE)-SUMIF($G$1:G1,G2,$J$1:J1),I2-SUMIF($G$1:G1,G2,$J$1:J1)),VLOOKUP(G2,stock!D:F,3,FALSE)-SUMIF($G$1:G1,G2,$J$1:J1))

Participate now!

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