Excel or Access advice needed (1 Viewer)

[ISW]

I just started a new job. They brought me in because I have an Accounting Info. Systems degree. I know more than the average joe but I am not an expert at all. So here I am and I am glad to meet you.

Now on to my issue.

I am working on recogonizing revenue on a FIFO basis. This is a change from the way my company previously recognized their revenue. The tables I get look something like the picture of the table I have attached. (I would have embedded it but my count is too low.


Now, In access or Excel or Access I need to take the count and and fill to the amount in the "Amount to be depleted" column IE "Allocated Count".

Please forgive my lack of correct terminology but I have never tried to do something like this and I am not sure where I should start. I need to be able to do this with sheets with thousands of rows so as you can see I would like to find an automated way to do this.

Tips, Solutions, and advice are much appreciated.

 

[NBVC]

I don't know if it would be easier in Excel or in Access.. but I was wondering if it is column D that you are trying to complete. And if so, how did you arrive at those numbers?.. I am not seeing the logic.

 

[tedsalad]

I always find it easier to manipulate in Excel

 

[ISW]

I don't know if it would be easier in Excel or in Access.. but I was wondering if it is column D that you are trying to complete. And if so, how did you arrive at those numbers?.. I am not seeing the logic.

Yes column D is the one I am trying to populate.

Basically filling up the totals in column B (1 then 2 then 3 etc) and putting any remainders in the next.

 

[NBVC]

Go through the specific numbers you have in the attachment and tell us every step and how you get the column D results at each row.

 

[ISW]

Here is a new sheet I added more explanations too. To be honest I am beginning to wonder if this is even possible in Excel or Access. I appreciate your patients.

 

[ISW]

A friend from another forum helped me out. Now I really want to learn to write these. I have some VB programming experience but not in Excel. Where would be a great place to learn how to write these?


Sub test()
Range("D2").Select

iniVal = ActiveCell.Offset(0, -3).Value
totval = 0
rowcounter = 0
Do Until ActiveCell.Offset(0, -3).Value = ""
Do While ActiveCell.Offset(0, -3).Value = iniVal
totval = totval + ActiveCell.Offset(0, -2).Value
rowcounter = rowcounter + 1
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Offset(-rowcounter, 0).Select
Do While ActiveCell.Offset(0, -3).Value = iniVal
If totval < ActiveCell.Offset(0, -1).Value Then
ActiveCell.Value = totval
totval = 0
Else
ActiveCell.Value = ActiveCell.Offset(0, -1).Value
totval = totval - ActiveCell.Offset(0, -1).Value
End If
ActiveCell.Offset(1, 0).Select
Loop
rowcounter = 0
iniVal = ActiveCell.Offset(0, -3).Value
totval = 0
Loop

End Sub

 

[NBVC]

If you are going to cross post on other forums, please have the courtesy to show us the links here, so we are not stepping over each other and not wasting time.

Have a read in this article: Cross posting etiquette