all possible combinations.

[Gaurav]

I was asked this question. I did not have the answer of course that is why i
am posting this here.

"In one column, find all possible combinations of two cells that add up to
7007.90"

how do we do this?

 

[Gary''s Student]

See:

http://www.tushar-mehta.com/excel/templates/match_values/index.html

 

[Gaurav]

Okay there are about 400 records from which i need to find the combinations.
If I try the code given on the website, the excel goes in a "Not Responding"
mode and I have to 'End Program'

The solver tempalate doesnt seem to work either. It just says too much data.

Are there other options?

Thanks for your help.

 

[Bill Sharpe]

I was asked this question. I did not have the answer of course that is why i
am posting this here.

"In one column, find all possible combinations of two cells that add up to
7007.90"

how do we do this?

You need some restraints on the two numbers that can be entered. I would
assume that you don't want any negative numbers. It looks like you're
working with currency, so I'd also assume that you don't want any
numbers beyond two decimal places.

With some restraints in place, solver should be able to come up with an
answer.

Other than the fact that it was probably the boss who asked this
question, one wonders why he or she needs the information.

Bill

 

[Dana DeLouis]

Hi. Just some quick thoughts.
Solver is limited to 200 changing cells, so your problem is too large
for Solver.

You want 2 cells that add to a total.
Two cells makes this a much easier problem.
For example, is A1 has 7000, then all you need to ask is if there is a
7.90 value somewhere in A2:A400.

This is much easier to code in vba.
Here's a "simple" demo using worksheet functions.
Suppose you have the number 1-25 in A1:A25 and you want a total of 40
based on two cells.
In B1, put =MATCH(40-A1,A2:$A$25,0) and copy down.
The value of 10 you see next to 15 means that it found the location of a
cell (10 cells away) that has 25 to give you a total of 40.
A formula in column C could give you this value ie 40 - 15 = 25 (and we
know that 25 exists later in the list)

= = =
HTH
Dana DeLouis