VBA to write Unique Solutions ONLY?
 

Hi everyone,

I run an optimizer n times, say 10, and tell the model to write
feasible solutions into an excel table as follows:

RUN Cost Weight Volume
1 2.3 51.27 20.3
2 3.1 49.2 22.2
3 4.3 37.2 16.7
4 2.3 51.27 20.3
5
6
and so on. As you see solutions 1 and 4 are duplicate which I don't
want. How can I write a piece of VBA code to prevent this? In other
words, the table should like this:

RUN Cost Weight Volume
1 2.3 51.27 20.3
2 3.1 49.2 22.2
3 4.3 37.2 16.7
4

and so on.

Regards,
Mike

VBA to write Unique Solutions ONLY?
 

Mike,

Here is some code to tidy up afterwards

Dim cLastRow As Long

cLastRow = Cells(Rows.Count, "B").End(xlUp).Row
Range("C2").EntireColumn.Insert
Range("C1").Value = "Temp"
Range("C2").FormulaR1C1 = "=COUNTIF(R2C2:RC[-1],RC[-1])"
Range("C2").AutoFill Destination:=Range("C2:C" & cLastRow),
Type:=xlFillDefault
Columns("C:C").AutoFilter Field:=1, Criteria1:="1"
Range("C2:C" &
cLastRow).SpecialCells(xlCellTypeVisible).EntireRo w.Delete
Columns("C:C").EntireColumn.Delete


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Michael" wrote in message
om...
Hi everyone,

I run an optimizer n times, say 10, and tell the model to write
feasible solutions into an excel table as follows:

RUN Cost Weight Volume
1 2.3 51.27 20.3
2 3.1 49.2 22.2
3 4.3 37.2 16.7
4 2.3 51.27 20.3
5
6
and so on. As you see solutions 1 and 4 are duplicate which I don't
want. How can I write a piece of VBA code to prevent this? In other
words, the table should like this:

RUN Cost Weight Volume
1 2.3 51.27 20.3
2 3.1 49.2 22.2
3 4.3 37.2 16.7
4

and so on.

Regards,
Mike

VBA to write Unique Solutions ONLY?
 

You would have to loop through the previous soltutions and see if they are
duplicate.

As an alternative, you could create your table, then use the advanced filter
to select unique solutions.

--
Regards,
Tom Ogilvy

"Michael" wrote in message
om...
Hi everyone,

I run an optimizer n times, say 10, and tell the model to write
feasible solutions into an excel table as follows:

RUN Cost Weight Volume
1 2.3 51.27 20.3
2 3.1 49.2 22.2
3 4.3 37.2 16.7
4 2.3 51.27 20.3
5
6
and so on. As you see solutions 1 and 4 are duplicate which I don't
want. How can I write a piece of VBA code to prevent this? In other
words, the table should like this:

RUN Cost Weight Volume
1 2.3 51.27 20.3
2 3.1 49.2 22.2
3 4.3 37.2 16.7
4

and so on.

Regards,
Mike

VBA to write Unique Solutions ONLY?
 

I run an optimizer n times, say 10, and ...

By "optimizer," do you mean Excel's Solver? If you are trying to minimize
Cost, then I would assume that each loop would give you that same minimized
cost. Are you changing constraints during each loop to arrive at different
Costs?

Don't know what you have set up, but here is a general technique that I use
(I assume you are using Solver.) The first run finds the minimum Cost. To
find the "Next best" solution I would add a constraint that Cost be slightly
greater then that previously found. For example, add the constraint that
Cost = 2.31, or rounded up to 2.40, etc. Whatever is good for you. On
each loop, bump up the value of this constraint.

You also may have multiple solutions at a cost of 2.3. Another technique
on the second pass is to keep the main constraint of minimizing cost, but
add a constraint that changes one of the solutions. (ie Weight <= 40 say)
There could be a solution of 2.3, with less weight, but more volume (which
you may prefer if shipping, etc..)

Of course, it all depends on what you have set up.


--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Michael" wrote in message
om...
Hi everyone,

I run an optimizer n times, say 10, and tell the model to write
feasible solutions into an excel table as follows:

RUN Cost Weight Volume
1 2.3 51.27 20.3
2 3.1 49.2 22.2
3 4.3 37.2 16.7
4 2.3 51.27 20.3
5
6
and so on. As you see solutions 1 and 4 are duplicate which I don't
want. How can I write a piece of VBA code to prevent this? In other
words, the table should like this:

RUN Cost Weight Volume
1 2.3 51.27 20.3
2 3.1 49.2 22.2
3 4.3 37.2 16.7
4

and so on.

Regards,
Mike