Formula/Macro to lookup oldest product quantities (Excel 2002)

Topic

New Reply

Hi!
I’m trying to automate our FIFO (first in, first out) process on our deferred revenue calcs, however I’m thinking a macro/VBA code would be better suited for this case unless someone has some combination of Excel formulas that might work better?? I’ve attached a file with a detailed description of what I’m trying to do. As usual, any help would be greatly appreciated!
Thanks,
Lana

Reply | Quote

Replies

LJM,

I played around with a simple sort and sum lay out.

It seem to do what you want. Have a look at the attachment. (Now I have fixed the file – thanks Hans:))

Reply | Quote

Kieran,

I think you attached the wrong workbook.

Reply | Quote

Hans,

As always you are correct.

I think it should be fixed now.

Thanks

Reply | Quote

Thanks for your response Kieran… I do appreciate it, however it doesn’t quite do what I need it to yet… my spreadsheet example is quite simplified and small, however the real database is much larger and I’d like to eliminate the manual task of someone typing in the data into the data worksheet (as I did in the original example attached to show how & where to move it to)… with your formula example someone would still have to breakdown the 15,000 into the three months and type in the 3 lines and find the appropriate FIFO month to attach to it. The pivot tables will give us the running quantity totals and values (the key I’m looking for is tracking the FIFO values, ie, what cost should really be used when the product gets sold). My lengthy notes inside the spreadsheet outline the movements needed… hopefully I’ve clarified it a bit more in this note, sorry about that, and thanks again for giving some input!
Thanks,
Lana

Reply | Quote

Hello again… anyone out there got any other ideas/options on this one????
Thank you!
Lana

Reply | Quote

Please remember that all Loungers, including WMVPs, Moderators and Admins, are volunteers spending some of their free time to help others. Although you’ll usually get a reply pretty quickly here, it all depends on who’s online and on how complicated the question is. This one is a rather tall order.

Reply | Quote

This is true, it is a tall order… I apologize for my large request as well as my impatience! I’ve been spoiled by your immediate responses in the past, and because you are so knowledgeable I assumed that it was just a quick thing to throw together… I should know better than to assume anything as I hate it when people to that to me (here at work). Sorry Hans!
Lana

Reply | Quote

I have not had a chance to work on this, but as an approach, one could do similar to your “checking”:
Create pivot table to combine and count, then go thru the items in the pivot table to remove get the max that are used, after each step refresh the pivot table to get the next value > 0.

The problems may arise that since you don’t use real dates that the pivot table will not be sorted correctly.

Also what happens if you want to remove more than is available? Should it do nothing? do what it can and forget the rest? Should a sheet be created with unfillable orders?

Steve

Reply | Quote

How about this code, it seems to work with your example set:

Steve

Reply | Quote

Hi Steve,
This code works great! Now I need to review what you wrote so I can see exactly what it’s doing… I’ve learned a lot from all the different code that you and others from Woody’s have created for me, so I’m getting better at applying bits & pieces of the various code to different projects that come about on a daily basis. I really do appreciate you taking the time to write this code, as well as helping me learn the wonderful magic that VBA has to offer!! I really love this stuff!
Thank You!!
Lana

Reply | Quote