calculate various cells to acheive a specific value

[Jennifer_C]

Hello,
I am trying to identify two cells within a column that total a specific
amount. For example:

ColA
546
9732
654
6548
687
9871

I need to find the two (or more cells in some cases) that would create the
total of 10,525.

For my current task, I have over 100 rows of data and I know two cells equal
a specific amount, but I do not know which two. Any help, or assistance given
would be greatly appreciated!

Jennifer

 

[Peo Sjoblom]

You would need to use code or the solver add-in, for the solver add-in using
your example you would create something like this



546 1
9732 1
654 1
6548 1
687 1
9871 1


assume that is A1:B6 (you would put 1s in B1:B6)

in C1 put

=SUMPRODUCT(A1:A6,B1:B6)


in D1 put

10,525

do tools>solver


then set target cell $C$1 equal to value of 10525
by changing cells $B$1:$B$6

then click add and use $B$1:$B$6 and from dropdown select bin, click OK

then click solve



depending on how large (note that the solver that comes with Excel is
limited)
the range is it can take some time to get a solution, but your example gives
this result

546 0
9732 0
654 1
6548 0
687 0
9871 1


where the 1s in B1:B6 show which 2 amounts total 10,525


I believe Harlan Grove wrote VBA code to do this as well, you can Google on
that

 

[David Biddulph]

One way, not necessarily very elegant, is if you've got your data in a
column as your example, copy and paste special in a row across the top of
the sheet to form a square table with your original column values, then add
values from column A and row 1 to form the square.
Conditional format to highlight where the result is 10525, & it shows
9871+654 (or v.v.).

 

[Guest]

for two only
in B1 enter
=if(isnumber(match(10525-A1,A:A,0)),10525-A1),"")
copy and paste down
or you could use the match portion in if true to find where it is.

for three, You can set up an NxN table and do something similar
for 4 or more it can get very unwieldy.

I wrote a macro to this one time, and when it didn't solve quickly, I
calculated how long it would take to solve and came up with about a hundred
years of computer time.

 

[Jennifer_C via OfficeKB.com]

Thank you for the input! It was easy to follow, however, it was taking too
long to complete, and I never got a correct answer. I think it might have
been better if I didn't have as much data to work with...
Thank you again!

You would need to use code or the solver add-in, for the solver add-in using
your example you would create something like this

546 1
9732 1
654 1
6548 1
687 1
9871 1

assume that is A1:B6 (you would put 1s in B1:B6)

in C1 put

=SUMPRODUCT(A1:A6,B1:B6)

in D1 put

10,525

do tools>solver

then set target cell $C$1 equal to value of 10525
by changing cells $B$1:$B$6

then click add and use $B$1:$B$6 and from dropdown select bin, click OK

then click solve

depending on how large (note that the solver that comes with Excel is
limited)
the range is it can take some time to get a solution, but your example gives
this result

546 0
9732 0
654 1
6548 0
687 0
9871 1

where the 1s in B1:B6 show which 2 amounts total 10,525

I believe Harlan Grove wrote VBA code to do this as well, you can Google on
that

Hello,
I am trying to identify two cells within a column that total a specific

[quoted text clipped - 18 lines]

Jennifer

 

[Jennifer_C via OfficeKB.com]

Thanks for the message. This was the way I was able to get my values. Great
help! However, I'll be stuck if I ever need to find three values!

One way, not necessarily very elegant, is if you've got your data in a
column as your example, copy and paste special in a row across the top of
the sheet to form a square table with your original column values, then add
values from column A and row 1 to form the square.
Conditional format to highlight where the result is 10525, & it shows
9871+654 (or v.v.).

Hello,
I am trying to identify two cells within a column that total a specific

[quoted text clipped - 18 lines]

Jennifer

 

[Jennifer_C via OfficeKB.com]

Thanks for the feedback for multiple values! I will need this for looking at
multiples! Very handy!

for two only
in B1 enter
=if(isnumber(match(10525-A1,A:A,0)),10525-A1),"")
copy and paste down
or you could use the match portion in if true to find where it is.

for three, You can set up an NxN table and do something similar
for 4 or more it can get very unwieldy.

I wrote a macro to this one time, and when it didn't solve quickly, I
calculated how long it would take to solve and came up with about a hundred
years of computer time.

Hello,
I am trying to identify two cells within a column that total a specific

[quoted text clipped - 16 lines]

Jennifer