Dear Vaibhav, I would try to explain how we do it manually so that it is
*Cell C-5 refers to quantity found after Physical Counting. (IT has some 900 rows of unique items).* To determine ageing of Inventory, the item code is looked into "Purchase Data". If material code is not available in purchase sheet,Ageing is Zero "0". If material code is found then, data in purchase sheet is sorted at two level, first material(small to large) and second doc date (Newest to Oldest) [image: Inline image 1] After this, we put filter on material like below (*Same Material code as put by user in B4*) [image: Inline image 3] [image: Inline image 2] Then , select qty equal to physically available and entered in Cell C5 in Inventory List. For these 8 qty ageing ,Ageing days and value are are updated in Worksheet. [image: Inline image 4] This activity need to be repeated for list containing around 900 -1200 rows of material code. Doing manually is time consuming and accuracy not certain. Format of data and result expected is shown for one item code on "Inventory List" Tab Hope you can advise either formula or macro to determine ageing and purchase value. On Wed, Dec 24, 2014 at 12:59 PM, Vaibhav Joshi <v...@vabs.in> wrote: > I did not got the logic of putting 8 in cell C5, can u pl explain.. > > +++++ > *I did not do this for you. God is here working through me for you.* > > On Tue, Dec 23, 2014 at 9:18 PM, C.G.Kumar <kumar.bemlmum...@gmail.com> > wrote: > >> May someone please look and advise macro to solve the puzzle. >> >> Thanks and Regards, >> C.G.Kumar >> >> ---------- Forwarded message --------- >> From: C.G.Kumar <kumar.bemlmum...@gmail.com> >> Date: Mon Dec 22 2014 at 7:09:55 PM >> Subject: Re: $$Excel-Macros$$ Re: Macro for Ageing of Inventory >> To: excel-macros@googlegroups.com <excel-macros@googlegroups.com> >> >> >> Macro is not providing desired result. >> >> Attached worksheet looks at total Purchase of Specific qty and provides >> ageing. >> >> But the need is to have ageing (& Value) of balance stock only. >> >> This mean in Solved worksheet of yours, total qty left in Cell "B5" is to >> be keyed in Manually and macro is expected to fill all other data. >> >> Modified your worksheet and highlighted area in Yellow of Desired result. >> >> >> Hope I explained enough details for try . >> >> >> >> >> On Mon, Dec 22, 2014 at 6:23 PM, Mandeep Baluja <rockerna...@gmail.com> >> wrote: >> >>> Try this File hope it reach you expected Result. >>> >>> >>> Regards, >>> Mandeep Baluja >>> LearningZmyPassion >>> https://www.linkedin.com/profile/view?id=312532939 >>> https://www.facebook.com/VBAEXCELSQL?ref=hl >>> >>> >>> On Monday, December 22, 2014 9:44:49 AM UTC+5:30, kumar wrote: >>>> >>>> Hi, >>>> >>>> I have purchase data in a spreadsheet and List of Inventory Items on >>>> another worksheet of excel. >>>> >>>> May someone help me with a macro or advance formula which computes >>>> Ageing of Inventory. >>>> >>>> Age of Inventory is calculated in Purchase Sheet but do not know how to >>>> compute for available qty only. >>>> >>>> Sample Sheet attached. >>>> >>>> >>>> -- >>> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? >>> It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >>> https://www.facebook.com/discussexcel >>> >>> FORUM RULES >>> >>> 1) Use concise, accurate thread titles. Poor thread titles, like Please >>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice >>> will not get quick attention or may not be answered. >>> 2) Don't post a question in the thread of another member. >>> 3) Don't post questions regarding breaking or bypassing any security >>> measure. >>> 4) Acknowledge the responses you receive, good or bad. >>> 5) Jobs posting is not allowed. >>> 6) Sharing copyrighted material and their links is not allowed. >>> >>> NOTE : Don't ever post confidential data in a workbook. Forum owners and >>> members are not responsible for any loss. >>> --- >>> You received this message because you are subscribed to the Google >>> Groups "MS EXCEL AND VBA MACROS" group. >>> To unsubscribe from this group and stop receiving emails from it, send >>> an email to excel-macros+unsubscr...@googlegroups.com. >>> To post to this group, send email to excel-macros@googlegroups.com. >>> Visit this group at http://groups.google.com/group/excel-macros. >>> For more options, visit https://groups.google.com/d/optout. >>> >> >> -- >> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s >> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >> https://www.facebook.com/discussexcel >> >> FORUM RULES >> >> 1) Use concise, accurate thread titles. Poor thread titles, like Please >> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice >> will not get quick attention or may not be answered. >> 2) Don't post a question in the thread of another member. >> 3) Don't post questions regarding breaking or bypassing any security >> measure. >> 4) Acknowledge the responses you receive, good or bad. >> 5) Jobs posting is not allowed. >> 6) Sharing copyrighted material and their links is not allowed. >> >> NOTE : Don't ever post confidential data in a workbook. Forum owners and >> members are not responsible for any loss. >> --- >> You received this message because you are subscribed to the Google Groups >> "MS EXCEL AND VBA MACROS" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to excel-macros+unsubscr...@googlegroups.com. >> To post to this group, send email to excel-macros@googlegroups.com. >> Visit this group at http://groups.google.com/group/excel-macros. >> For more options, visit https://groups.google.com/d/optout. >> > > -- > Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s > =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ > https://www.facebook.com/discussexcel > > FORUM RULES > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > will not get quick attention or may not be answered. > 2) Don't post a question in the thread of another member. > 3) Don't post questions regarding breaking or bypassing any security > measure. > 4) Acknowledge the responses you receive, good or bad. > 5) Jobs posting is not allowed. > 6) Sharing copyrighted material and their links is not allowed. > > NOTE : Don't ever post confidential data in a workbook. Forum owners and > members are not responsible for any loss. > --- > You received this message because you are subscribed to the Google Groups > "MS EXCEL AND VBA MACROS" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to excel-macros+unsubscr...@googlegroups.com. > To post to this group, send email to excel-macros@googlegroups.com. > Visit this group at http://groups.google.com/group/excel-macros. > For more options, visit https://groups.google.com/d/optout. > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
