The data for the search should be organized in a single contiguousrange in one column.

[sam]

Hi ALL,

I am new to this forum. basically looking for a macro in excel that
would match the values that are opposite and not equal and also in the
same column.
E;g: 100
250
-100
-100
-25
-25

Basically the values should match and the cell highlighted.

I want that values , which are matched are highlighted. In other
world , where the sum =0, then it should match all the relevant values
and highlight's them
Note: the column should be min of 100 lines
the result should show:Highlight 250,-100,-100,-25,-25 , as there sum
=0
100
250
-100
-100
-25
-25


Hope to see a reply soon ....

 

[Sixthsense]

Hi Sam,

Read your post once again and check whether you can able to understand
it. If not then It’s impossible for us to understand. Please explain
your data and the expected result clearly for our better
understanding.

 

[Hans Terkelsen]

Hi ALL,

I am new to this forum. basically looking for a macro in excel that
would match the values that are opposite and not equal and also in the
same column.
E;g: 100
250
-100
-100
-25
-25

Basically the values should match and the cell highlighted.

I want that values , which are matched are highlighted. In other
world , where the sum =0, then it should match all the relevant values
and highlight's them
Note: the column should be min of 100 lines
the result should show:Highlight 250,-100,-100,-25,-25 , as there sum
=0
100
250
-100
-100
-25
-25


Hope to see a reply soon ....

Hi Sam.

As I understand your problem, you want to locate some combination of 100 cells, that add to 0.

For each extra cell the number of possible combinations is multiplied by 2.

20 cells would mean a million combinations to check.
Possible.

To do an exhaustive check of 100 cells would be quite impossible.
And if there is only a few solutions,
it would have to be an exhaustive search of all the combinations.

Many lifetimes of the universe.

Hans T.

 

[Claus Busch]

Hi Sam,

Am Sun, 17 Jul 2011 12:02:20 -0700 (PDT) schrieb sam:

I want that values , which are matched are highlighted. In other
world , where the sum =0, then it should match all the relevant values
and highlight's them
Note: the column should be min of 100 lines
the result should show:Highlight 250,-100,-100,-25,-25 , as there sum
=0
100
250
-100
-100
-25
-25

your values in A2:A120. Then in A1:
=SUM(A2:A120)
in B2:B120 enter 0
and in B1:
=A1-SUMPRODUCT(A2:A120,B2:B120)
Start Solver => target cell B1 with value 0, changeable cells B2:B120,
conditions B2:B120 >=0, B2:B120 <=1, B2:B120 Integer
When solver has finished in B2:B120 are 0 and 1. Highlight all cells in
A2:A120 if in column B = 0.


Regards
Claus Busch