PDA

View Full Version : Solved: Parsing an array, calculating cumulative sum and spitting some output



mandelbrot
04-23-2013, 09:02 PM
Dear all,

I tried to resolve my issue by plain Excel formulas, but it looks like I need VBA to make this work.

I have a spreadsheet with seven columns: ID, Quantity, LastRow, NeedAmount, Flag, Result1, Result2. Looking something like this (empty cells contain some irrelevant numbers, using _ for spaces when needed):
row1: ID Quantity LastRow NeededAmount Flag_______Result1 Result2
row2: 13 1002
row3: 11 3900
row4: 22 1000
row5: 12 1212
row6: 22 1000
row7: 22 2000
row8: 21 3100
row9: 22 1313____3_______1500________CALCULATE

In the ID columns these are ID of goods, the second column has relevant quantities. Now, in the row where Flag column contains the word CALCULATE, I would like Excel to search the above array (starting from row specified in LastRow) for Quantities of good ID until it collects NeededAmount of them. And then to spit out into the neighboring Result1 cell the row at which it finished and into Result2 cell the remaining part of Quantity. In the above example, it will start from row 3 looking for 1500 units of good 22, finding them in row4 and row6, thus producing 6 in column Result1 and 500 (=1000+1000-1500) in Result2, both in row9.

I guess I would like to specify it as a flexible function that I could call from say cells in column Result1 passing to it the values from ID, LastRow and NeededAmount in the corresponding row.

Could someone please help? Thank you very much for reading!

mdmackillop
04-24-2013, 05:38 AM
Welcome to VBAX

Select a Calculate cell and run the macro
Option Explicit

Sub GetData()
Dim c As Range, i As Long
Dim Strt As Long, Endd As Long
Dim R As Long
Dim x As Long
Dim ID As Long
Dim Needed As Long

Set c = ActiveCell
If c <> "CALCULATE" Then Exit Sub
R = c.Row

Strt = Cells(R, 3)
Endd = R - 1
ID = Cells(R, 1)
Needed = Cells(R, 4)

For i = Strt To Endd
If Cells(i, 1) = ID Then
x = x + Cells(i, 2)
If x >= Needed Then
Cells(R, 6) = i
Cells(R, 7) = x - Needed
Exit For
End If
End If
Next
End Sub

mandelbrot
04-24-2013, 06:39 AM
mdmackillop:
Thank you very much for looking into my question!

The macro you suggested works as intended. But there is one problem. The reason I didn't implement it this way straight away (I'm real rookie, but writing simple macros is something I can do :) ) is that the spreadsheet is large and I can not highlight the CALCULATE cell for every such operation. Hence I was thinking about defining my own function which I could call in every row to check if the Flag is set on and perform an operation when it is.

So, basically my additional question is this: how do I force this algorithm to work without the need to manually select the CALCULATE cells?

(And another minor thing: you defined ID as long, in reality I may have letters in there, will "If Cells(i, 1) = ID Then" be fine with this?)

Thank you again!

mandelbrot
04-24-2013, 11:46 AM
Figured out how to transform the above macro into function. The key was to realize that functions can only write in the cells they were called from (unless you do some heavy trickery).

Also, changed ID to String, which resolved the problem with comparisons.

Thanks very much again, mdmackillop, for your help! And to all people making this forum as great as it is!

mdmackillop
04-24-2013, 12:14 PM
I would probably use a FindNext routine to find the Calculate cells and then call the macro based on that range.

However, please post your solution for the benefit of others.

mandelbrot
04-25-2013, 05:04 PM
I would probably use a FindNext routine to find the Calculate cells and then call the macro based on that range.

However, please post your solution for the benefit of others.

The reason I didn't want to use macros every time running the search for the next CALCULATE cell is because my spreadsheet has dozens of places where the above operation is required. (Or perhaps I'm missing something and overcomplicating it...)

Below is the script that shows the mechanics of my solution. Unfortunately, it won't operate with the above sample Excel file. The thing is that I have been working on the real-life version of this whole business, which is insanely more complicated. So, I stripped my real-life script down to necessary minimum, it is relative clean and hopefully demonstrates the logic. The idea is to call it from a two-cell array, passing the row it is called from and whether it is looking for decreases or increases in inventories; it will return the row it finished at and the difference between how many it found versus how many was available in that row.


Option Explicit

Function ParseGoodsFIFO(ClosedTransact As Range, LookForUD As String) As Variant
Dim i As Long
Dim Strt As Long, Endd As Long
Dim R As Long
Dim TotalFound As Long
Dim ID As String
Dim Needed As Long
Dim tmp As Variant

ReDim tmp(1 To 2)

R = ClosedTransact.Row 'take only row number of where to start from

Strt = Cells(R, 25) 'LAST 0 CROSS
Endd = R - 1
ID = Cells(R, 15) 'ID
Needed = Cells(R, 18) 'Q in CLOSED transaction
TotalFound = -Cells(R, 27)

For i = Strt To Endd
If Cells(i, 15) = ID And Cells(i, 16) = LookForUD Then
TotalFound = TotalFound + Cells(i, 18)
If TotalFound >= Needed Then
tmp(1) = i
tmp(2) = Cells(i, 18) - (TotalFound - Needed)
Exit For
End If
End If
Next
If TotalFound < Needed Then
tmp(1) = i
tmp(2) = Cells(i, 18) - (TotalFound - Needed)
End If

ParseGoodsFIFO = tmp

End Function


Never used VBA before, but now it looks like this is the beginning of a beautiful friendship.:cloud9: