• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Fifo Problem

mahaveer

mahaveer

Member
i had a problem regarding fifo method.......can anyone help me on this.....i have put my file on the following link........and had write my problem in the same file......


i had tried so much but i did not get any solution on this......any one can help me.....


Thank in Advance.......


Regards

CA Mahaveer Somani

http://www.2shared.com/file/hGpBfy3a/fifo_problem.html
 
Mahaveer


First delete the 2 columns J & M (The 2 blank ones)

then in D10 put:

Code: 
=INDEX(OFFSET($G$3:$G$7,,MATCH($A10,$H$1:$M$1,0),,2),MATCH($C10,OFFSET($G$3:$G$7,,MATCH($A10,$H$1:$M$1,0),,1),0),2)


Copy down


You may want to put an error catch around it like:

[code]=IFERROR(INDEX(OFFSET($G$3:$G$7,,MATCH($A10,$H$1:$M$1,0),,2),MATCH($C10,OFFSET($G$3:$G$7,,MATCH($A10,$H$1:$M$1,0),,1),0),2),"n/a")


ps: Remarkably, The formula works if you don't delete Columns J & M,

Just Change the formula to:

=IFERROR(INDEX(OFFSET($G$3:$G$7,,MATCH($A10,$H$1:$O$1,0),,2),MATCH($C10,OFFSET($G$3:$G$7,,MATCH($A10,$H$1:$O$1,0),,1),0),2),"n/a")[/code]
 
thanks hui....


but if i have to write "book" and qty after searching again and again then i can write rate related to it.....myself.....


Actually i want that when i put book single time and qty write 25 then it will automatically fill my whole entry.....like below:


problem

book 25


solution

book 10 15

book 5 12

book 10 15
 
Mahaveer


I understand problem


book 25


solution

book 10 15

book 5 12

book 10 15

Total Cost 42


But what stops a solution from being?


problem

book 25


solution

book 20 18

book 5 12

Total Cost 30
 
thank again hui....


as i told you sir, that when i would issue 25 qty then it will take 25 units from first that will as follows:

book 10 15

book 5 12

book 10 15


it means it will not take from the bottom, it will take the qty from the top...and after make the entry when i will do the next entry then the data source will automatically updated and qty remains as follows: (i.e. i already done)


book

qty rate

2 15

18 10

20 18


so i have only the main problem is that user will put only the qty which he want to issue like 25 then i want that excel automatically gets the qty from the top for 25 only and rates related there to....


i hope i told you properly if it was not sufficient then you can ask me so i will give you further solution through an excel file again....


Thanks Hui,,,,

Regards

CA Mahaveer Somani
 
Mahaveer


I think this will need a VBA solution.


I don't have time at present to look at it


I'll keep thinking about it in my sleep and see if anything comes of it
 
thank dear.....if you can provide me a vba solution for it......i will wait for your kindness.......sir.....


Thanks again for you valuable time....


Regards

CA Mahaveer Somani
 
@Mahaveer


Hi


i am also interested in FIFO method but i don't know how to do that so i follow you and learning to do


i have some doubts can you


1. you mention one table as issue Entry, in the issue Entry you gave the validation list from A10:A16


2. You mention Qty Rate and Amount Columns when we enter the Qty in Qty column then the rate should be display of the First in na


as per the Hui sir formula i did when i enter in C10 =10 the i got 15 , C11 = 5 i got 12 and C12 = 10 again i got 15


but i can't understand , can you give me the manual entry with a sample file


Thanks


SP
 
I ATTACHED A FILE on the following link:

http://www.2shared.com/file/PXkuLM1O/fifo_problem.html


now i think @sgmpatnaik you understood what i need.....from you....


if still is there any pendency then you can ask easily my friend


Thanks for give your valuable time to me......


Regards

CA Mahaveer Somani
 
Hi Mahaveer,


* I have changed a little bit in the layout.

* As you are a 5+ rating forum member.. I have not added many validation.

* I hope you can manage.. overwrite related problem.. when data inserted in NOT IN LAST position..


https://dl.dropboxusercontent.com/u/78831150/Excel/fifo%20problem%20%28Mahaveer%20Somani%29.xlsm


Please let us know.. if you need any further help..


BTW.. I am still working for any NON VBA solution .. :(


Regards,

Deb


_____________________

[pre]
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
exitfor = False
If Target.Column = 2 And Target.Row >= 10 And IsNumeric(Target.Value) Then
qty = Target.Value
Item = Target.Offset(0, -1)
With Target
loctn = [G1].Offset(0, WorksheetFunction.Match(Item, Range("H1:O1"), False)).CurrentRegion
For i = 3 To UBound(loctn)
If qty >= loctn(i, 1) Then
.Offset((i - 3), 1) = loctn(i, 1)
.Offset((i - 3), 2) = loctn(i, 2)
Else
.Offset((i - 3), 1) = qty
.Offset((i - 3), 2) = loctn(i, 2)
exitfor = True
End If
qty = qty - loctn(i, 1)
.Offset((i - 3), 3) = .Offset((i - 3), 1) * .Offset((i - 3), 2)
If exitfor Then Exit For
Next i
End With
End If
End Sub
[/pre]
_____________________
 
http://www.2shared.com/file/q72RyGRf/fifo_problem__Mahaveer_Somani_.html


thanks debraj,


very good vba.....and its working good but there are a few problem remains for me......which i have considered in my file.......with the red colour.....so will you please check out and solve my query........


and m thankful to you for your valuable time........


Regards

CA Mahaveer Somani
 
Hi Mahaveer..


Sorry .. I am on outing.. and thats also.. in JAIPUR.. yes.. jaipur.. your place.. :)


I will not able to check or update anything thing in this weekend.. Its a long tour.. Jaipur.. & Ajmer..


I will be in FORT in Saturday.. he he.. I will reply on Monday if I recover my HANGOVER.. :)


Can someone look into this.. for me.. :(


Regards,

Deb
 
its ok Debraj,


sorry to disturbed you.....


by the way this is not fine that you are in our city and dont told me......meet with us....friend on 7:30 where you want in jaipur........


i will wait for your reply regards my query and for meeting response


regards

CA Mahaveer Somani
 
@Deb


Hi


just for knowledge suppose we remove the qty from the Item Columns then the Qty rate and amount should be blank and unmerge the cells how to do that


Thanks


SP
 
Hi SP!


Sorry! I didn't get you..


Can you please send me a sample.. expected output..


Regards,

Deb
 
@Debraj


Sorry for my un understandable words please understand again what i am telling


Your code is working nice just i want to change some little bit they are


1. When we select the Item Books, Pens, Pencil what ever it may be and we put the Item Quantity of 40 then it extract the FIFO method in the Column C and rates when i remove the Item of 40 then it still stand as merge column and qunt.,rates and amount. so i think when i remove the Qun in Column B then the rest columns be clear and unMerge the ColumnB


is it possible to do, if you still need sample then i will give


Thanks


SP
 
Hi Patnaik!


"Mu kana kariba uchit?" (What should I do ?)


If you delete 40.. and write 60.. Then may be I need to INSERT a row in between.. Pen's Area and next ITEM area.. or may be I need to delete a ROW.. if you write 20..


"Sesa prashna kana?" (Whats your last Question?)

yes its possible.. but need to write with huge validation..

* If left column is fill or not..

* If deleted merge cell is last row or not..

* If previous data is greater than or less than current data..


I think.. if we need to run this type work.. then I think we need to use a separate area for complete input. then read the area and after cleaning the output area.. complete OUTPUT area at once..


BTW.. I really hate to work on MERGE cell in VBA..


Regards,

Deb
 
@Debraj


Dhanybad Bhai (Thaks Bro)


Daya Kari tole diya hoi thiba link ru file taku tike download korontu


Code: 
Please download the file from the below mention link


https://dl.dropboxusercontent.com/u/75654703/fifo%20problem%20%28Mahaveer%20Somani1.xlsm


Thanks


SP
 
thanks Debraj,


its really very useful to me.........but there is one problem......i asked you before it from you......


for example if i select book 14 then i get like this:


book 10 15 150

5 12 60

now i want book before 5. cause this cell is blank.......


and my dear friend........@sp actually i want to protect those cell which have values...so nobody can change the value......


Regards

CA Mahaveer Somani
 
hey sgmpatnaik,


can you do for me.......


If i select book 14 then i get like this.......

book 10 15 150

blank 4 12 48


but i want "book" again in place of "blank"


Regards

CA Mahaveer Somani
 
@Mahaveer


Hi


Sorry my friend i was busy with my work and i didn't check the posts any how i will try your requirement to do if i can't our Ninja's will do for you


Thanks


SP
 
You must log in or register to reply here.
Back
Top