HI EXPERTS,

i hope you and excel can do this.
i have a master data with so many rows.
i.e A B C D
No Name City Values
1 - - 100
2 - - 3081
-
5000 - - 6

now my bos ask me to find values with total sum i.e 258,000 from data above.

can excel do this, or should create program using vb?
can anybody help me?

hope you understand with what i want to achieve.

thanks

regards,
reza

Try this Tom Ogilvy classic for a way using Solver:
http://tinyurl.com/5kx9bw
Link above still works, I just tested
--
Max
Singapore
---
"reza" wrote:
HI EXPERTS,

i hope you and excel can do this.
i have a master data with so many rows.
i.e A B C D
No Name City Values
1 - - 100
2 - - 3081
-
5000 - - 6

now my bos ask me to find values with total sum i.e 258,000 from data above.

can excel do this, or should create program using vb?
can anybody help me?

hope you understand with what i want to achieve.

thanks

regards,
reza

Try this Tom Ogilvy classic for a way using Solver:

As a side note, Solver is limited to 200 changing cells.
I may be wrong, but it appears the op is looking among 5000 numbers.
It may require a mixture of approaches.
= = = = = =
HTH :)
Dana DeLouis

On 5/21/10 3:11 AM, Max wrote:
Try this Tom Ogilvy classic for a way using Solver:
http://tinyurl.com/5kx9bw
Link above still works, I just tested
-- Max Singapore --- "reza" wrote:
HI EXPERTS,

i hope you and excel can do this.
i have a master data with so many rows.
i.e A B C D
No Name City Values
1 - - 100
2 - - 3081
-
5000 - - 6

now my bos ask me to find values with total sum i.e 258,000 from data above.

can excel do this, or should create program using vb?
can anybody help me?

hope you understand with what i want to achieve.

thanks

regards,
reza

It may require a mixture of approaches ..

Such as ... ? Any links to share with us?

Hi. No, I'm afraid not. I just wanted to point out that Solver was
limited to 200 changing cells vs the op's input of 5000 numbers.
There's no easy solution, but if one wanted to use Excel's Solver, here
are some general ideas one "may" be able to work with.
It really depends on the data.

Suppose among the 5000 data, one had...
{25, 50, 75, 100, 125, 150, 175, 200, 225, 250...etc}

These would take up 10+ Binary changing cells among the 200 one can use.
One 'could' remove these numbers from the list and write the equation:
=25*x
where 'x' is now an "Integer" constraint (vs binary) with the added
constraint that places limits on the possible values.
(ie x<=10 for an upper limit of 250. Option of 'Assume non-negative for
the lower value)

This would replace multiple changing cells with just 1.

Same concept if one had duplicates. If one had 20 of the number 100,
one could write = 100*x, subject to x<=20

Some other not so great ideas might be to break the 5000 numbers into
groups of 25 numbers (having 200 groups). Total each group.
You now have 200 numbers. Now use the Binary technique that finds a
total that is Minimized, subject to the constraint that the total is =
258,000.

Suppose the closest you get is 258,500.
Now, look at each group that was picked, and see if you can spot a
combination that totals 500 that you can remove from the list.
Again, it's not a easy problem when you have 5,000 numbers to work with.
It can be more of an art than a science at this point.

= = = = = =
Dana DeLouis

On 5/22/10 6:10 PM, Max wrote:
It may require a mixture of approaches ..

Such as ... ? Any links to share with us?

Thanks for responding further, Dana. Useful ideas, those. From your
experience, what would be the other usual business applications of this
technique beyond matching payments/partial payments to invoices/accounts?