easy macro?

[Ctech]

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.

[Bob Phillips]

You could use conditional formatting with a formula of

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

--

HTH

RP

"Ctech" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[GB]

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" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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
> >
>
>
>

[GB]

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" <[email protected]> wrote in
> > message news:[email protected]...
> > >
> > > 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
> > >
> >
> >
> >

[davidm]

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

[GB]

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
>
>

[davidm]

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

On no less than three ocassions on this thread, 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 here: you don't profit any by getting under the skin of others.

Davidm.