VBA New Constraint - Not sure how to start

[KevinZ]

I am looking to add a new constraint to my query. I am not sure how to start setting this up.

I want to add a constraint to set a maximum number of times a value can be used in my solutions. My loop will run for 1 to a maximum of 150 times. This will be set prior to running the solution.

For example, I may want Row 1 ($A$2) to be included a maximum of 6 times, Row 2 ($A$3) 4 times, Row 3($A$4) - 6 times, row 4($A$5) - 2 times, etc up to the may number of rows (Generally less than 200). These can be set anywhere from 0 to the max number of solutions on an individual basis.

I am solving for 6 out of the up to 200 rows at a time and looping for each value, when the row has been used the maximum number of times, it should be removed from consideration for any remaining solves.

My "A" column is the change variable, I have 2 other columns that can be used for a unique identifier for each row. The Name (Column B) and an ID Column (Column C)

I'm not sure how to set up a counter for each row and if I need to set up some type of error notice if there are not enough variables to create all of the solutions based on the other constraints.

Note: I asked a similar question in a previous thread but did not receive any replies. Please let me know if this makes sense, if not, I can try to reword/clarify it.

 

[sijpie]

I am assuming you are doing these calculations using VBA?
Is it some formula that takes each of the values in turn? Is only one variable used at a time, or does the formula use more than one variables (from several rows)?

 

[KevinZ]

I am using VBA to calculate a max solution, then I will loop to find the next best solution and so on up to about 150 times. The formula is set to use only 6 values at a time. I want to limit the number of times each value can be used but each value can be set individually.

Foe example, I am maximizing Column F with limits on Column D. Column A changes from 0 to 1 in the solved solution. I will copy the value from Column C to a new spreadsheet. Then it will loop and look for the next best solve. I want to limit the number of times a value can be used so if I am running the loop 50 times, I may want the first value a maximum of 6 times, the second a maximum of 2 times, etc. The first and second may or may not be used at the same time, it would depend on what the solver comes back with.

Let me know if this makes sense or if you need any clarification.

Column A		Column C	Column D		Column F
0		12472012	9000		231
0		12472013	8900		95
0		12472014	8800		204
0		12472015	8700		231
0		12472016	8600		207
0		12472017	8500		124
0		12472018	8400		242
0		12472019	8300		87
0		12472020	8200		194
0		12472021	8100		187

<colgroup><col width="64" style="width:48pt" span="6"> </colgroup><tbody>
</tbody>

 

[sijpie]

A simple (not the quickest) solution will be ta add a column where you put in the number of times a variable can be used. Then when the code runs, every time it wants to use a variable, it checks to see if the number-of-times cell for that variable is >0, if so use it and substract 1 from the number-of-times cell.

It will be a lot faster if you load this number-of-times column into an array and then do this checking and subtracting in the array, to minimise the read/writes to the sheet. But you can do that in the optimise phase after checking if it works.

 

[KevinZ]

Thank you, I didn't think about that. I will look into how to build an array for this.