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

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

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

2. ## Re: Formula/Macro to lookup oldest product quantities (Excel 2002)

Kieran,

I think you attached the wrong workbook.

3. ## Re: Formula/Macro to lookup oldest product quantities (Excel 2002)

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)

4. ## Re: Formula/Macro to lookup oldest product quantities (Excel 2002)

Hans,

As always you are correct.

I think it should be fixed now.

Thanks

5. ## Re: Formula/Macro to lookup oldest product quantities (Excel 2002)

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

6. ## Re: Formula/Macro to lookup oldest product quantities (Excel 2002)

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

7. ## Re: Formula/Macro to lookup oldest product quantities (Excel 2002)

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.

8. ## Re: Formula/Macro to lookup oldest product quantities (Excel 2002)

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

9. ## Re: Formula/Macro to lookup oldest product quantities (Excel 2002)

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

Steve

11. ## Re: Formula/Macro to lookup oldest product quantities (Excel 2002)

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•