Im having a column with numbers... a lot of these number are exactly the
same except that they are negative/postive so the sum of them will be 0


is there any way I can make a macro which wil indetify to cells which
will qual eachother out to 0?


-1500
-1000
-1000
-200
-100
-30
20
30
99.5
1000
1500

on this list above, these numbers will equal each other out:

-1500 + 1500 = 0
-1000 + 1000 = 0
-30 + 30 = 0


All help apprichiated.


--
Ctech
------------------------------------------------------------------------
Ctech's Profile: http://www.excelforum.com/member.php...o&userid=27745
View this thread: http://www.excelforum.com/showthread...hreadid=501981

You could use conditional formatting with a formula of

=ISNUMBER(MATCH(-A1,A:A,0))

--

HTH

RP

"Ctech" wrote in
message ...

Im having a column with numbers... a lot of these number are exactly the
same except that they are negative/postive so the sum of them will be 0


is there any way I can make a macro which wil indetify to cells which
will qual eachother out to 0?


-1500
-1000
-1000
-200
-100
-30
20
30
99.5
1000
1500

on this list above, these numbers will equal each other out:

-1500 + 1500 = 0
-1000 + 1000 = 0
-30 + 30 = 0


All help apprichiated.


--
Ctech
------------------------------------------------------------------------
Ctech's Profile:
http://www.excelforum.com/member.php...o&userid=27745
View this thread: http://www.excelforum.com/showthread...hreadid=501981

I believe it is an acceptable solution, though not a macro.

What I would do, would be for each cell in the row that has not previously
tested as to be countered, if the negative of the answer exists in the rows
below it, do whatever is necessary to mark the row as not to be used. (Delete
it or flag it) <--- Whatever you do here will determine whether you should be
concerned about it previously testing true as a countered value.


"Bob Phillips" wrote:

You could use conditional formatting with a formula of

=ISNUMBER(MATCH(-A1,A:A,0))

--

HTH

RP

"Ctech" wrote in
message ...

Im having a column with numbers... a lot of these number are exactly the
same except that they are negative/postive so the sum of them will be 0


is there any way I can make a macro which wil indetify to cells which
will qual eachother out to 0?


-1500
-1000
-1000
-200
-100
-30
20
30
99.5
1000
1500

on this list above, these numbers will equal each other out:

-1500 + 1500 = 0
-1000 + 1000 = 0
-30 + 30 = 0


All help apprichiated.


--
Ctech
------------------------------------------------------------------------
Ctech's Profile:
http://www.excelforum.com/member.php...o&userid=27745
View this thread: http://www.excelforum.com/showthread...hreadid=501981

Also, by looking at the example you provided, all of your numbers appear to
be sorted from smallest to largest. Therefore you could use the first cell
to compare to the last cell until the difference of the two numbers is
negative or that the absolute of your negative value is larger than your
positive value. This would cut your comparison in half, as no positive
number will offset a positive number, and no negative number will offset a
negative number. :) Just a thought....

"GB" wrote:

I believe it is an acceptable solution, though not a macro.

What I would do, would be for each cell in the row that has not previously
tested as to be countered, if the negative of the answer exists in the rows
below it, do whatever is necessary to mark the row as not to be used. (Delete
it or flag it) <--- Whatever you do here will determine whether you should be
concerned about it previously testing true as a countered value.


"Bob Phillips" wrote:

You could use conditional formatting with a formula of

=ISNUMBER(MATCH(-A1,A:A,0))

--

HTH

RP

"Ctech" wrote in
message ...

Im having a column with numbers... a lot of these number are exactly the
same except that they are negative/postive so the sum of them will be 0


is there any way I can make a macro which wil indetify to cells which
will qual eachother out to 0?


-1500
-1000
-1000
-200
-100
-30
20
30
99.5
1000
1500

on this list above, these numbers will equal each other out:

-1500 + 1500 = 0
-1000 + 1000 = 0
-30 + 30 = 0


All help apprichiated.


--
Ctech
------------------------------------------------------------------------
Ctech's Profile:
http://www.excelforum.com/member.php...o&userid=27745
View this thread: http://www.excelforum.com/showthread...hreadid=501981

This may not be pretty, but it may do what you want ie pair off th
matches

Sub nn()
For Each c In [a:a]
If Not IsEmpty(c) Then
x = c.Value
n = 1
k = RGB(Rnd * 255, Rnd * 255, Rnd * 255)
Do Until Cells(n, 1) = ""
If x + Cells(n, 1) = 0 Then
c.Interior.Color = k
Cells(n, 1).Interior.Color = k
End If
n = n + 1
Loop
End If
Next
End Sub


Davi

--
david
-----------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...fo&userid=2064
View this thread: http://www.excelforum.com/showthread.php?threadid=50198

This will cause a value to be marked twice when in fact it can only be offset
one time. (i.e., if -1000 is in the list twice, but 1000 is only present
once, then 1000 will be marked twice (Though you wouldn't really know it.))
An additional test should be added that if the cell interior color is already
= k then do not use that cell for comparison. Yeah it's a dirty solution but
works. Depends on how nicely you want this thing to work/look. To prevent
this, you might also be able to set n = c.row + 1 instead of n = 1. Afterall
if you started at the beginning of the list, and have already found a match
with the first item, why look at the first item again later? I think though,
you will still need to verify that cells(n,1) interior color is not already =
k before testing that they cancel each other out.

My two cents...


"davidm" wrote:


This may not be pretty, but it may do what you want ie pair off the
matches

Sub nn()
For Each c In [a:a]
If Not IsEmpty(c) Then
x = c.Value
n = 1
k = RGB(Rnd * 255, Rnd * 255, Rnd * 255)
Do Until Cells(n, 1) = ""
If x + Cells(n, 1) = 0 Then
c.Interior.Color = k
Cells(n, 1).Interior.Color = k
End If
n = n + 1
Loop
End If
Next
End Sub


David


--
davidm
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=501981

I wish to draw attetion to the incipient trend of some "contributors" on
this forum failing to honour the social protocol that befiits a forum of
this stature.

On no less than three ocassions, a poster masquarading under the "pen
name" GB took on the garb of a soi-disant reviewer of the contributions
made by others on a primary post by CTECH. It is hard to judge his
motives other than to deduce that by attempting to be captious and
nitpicking without himself coming forward with a hard-crust feasible
solution, he passes off as someone wishing to flaunt the little
knowledge that he has in excel programming. Surely, not many people
would be impressed with this stunt which has far more greater chance of
attracting ordure than lucre.

To use the corny trite phrase, time is the essence in most of what we
do. Not surprisingly, most well-meaning "helpers" on the forum would,
hard-pressed for time, rather offer something rough-and-ready than
remian reticient. It is not to say they are not unaware of the
shortcomings in what they are offering nor to imply that they have not
the capacity to effect any necessary corrections. The group
brainstorming effect on a forum always ensures that someone with some
nous and genuine selfless spirit and with time on his hands would come
along to spruce up "the quick-and-dirty" without as much as raising a
disparaging hint in any shape or form.

GB and those who may be tempted to ape him need appreciate what group
ettiquet in a forum setting requires. Common sense serves as a guide
he you don't profit any by getting under the skin of others.

Davidm.


--
davidm
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=501981