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!
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!