Compare Negative & Positive Numbers

How to compare values in a colum that has several data of positive and negative numbers, of which most of them cancel one another. What I am trying to do is to delete all the numbers that cancel each other and have obly numbers that are not cancel left in that column.

Column A
123
-123
-325
325
123

from the example above, after I successfully delete all numbers that offsetting each other, I should have only $123 as a total in column A

Please help. Thanks Chom

"chom" <[email protected]>
wrote in message
news:[email protected]...
>
> How to compare values in a colum that has several data of positive
> and negative numbers, of which most of them cancel one another.
> What I am trying to do is to delete all the numbers that cancel each
> other and have obly numbers that are not cancel left in that column.
>
> Column A
> 123
> -123
> -325
> 325
> 123
>
> from the example above, after I successfully delete all numbers that
> offsetting each other, I should have only $123 as a total in column
> A
>
> Please help. Thanks Chom
>

Hi Chom,

See my reply to your other post. If that doesn't work, reply there.

Multiple posts are bad form - stick with one thread and follow a
discussion of suggestions there.

HTH,

Alan.

Just posted this response to you in your earlier post in
..worksheet.functions
(As Alan says, please refrain from multi-posting ..)

" .. Think this revised set-up should deliver what we're after ..

Assume data is in Sheet1's col A, A1 down:

123
-123
-123
124
-124
124
etc

Put in B1:
=IF(A1="","",COUNTIF($A$1:A1,A1))

Put in C1, and array-enter the formula,
i.e. press CTRL+SHIFT+ENTER
(instead of just pressing ENTER):

=IF(B1="","",IF(ISNUMBER(MATCH(-A1&"_"&B1,$A$1:$A$100&"_"&$B$1:$B$100,0)),""
,ROW()))

Select B1:C1, fill down to say, C100,
to cover the max expected data in col A

Note: Adapt the ranges $A$1:$A$100, $B$1:$B$100 in the formula in C1 to
suit. We can't use entire col references (e.g.: A:A, B:B) in the array
formula for col C.

In a new Sheet2
--------------
Put in A1:

=IF(ISERROR(SMALL(Sheet1!C:C,ROWS($A$1:A1))),"",INDEX(Sheet1!A:A,MATCH(SMALL
(Sheet1!C:C,ROWS($A$1:A1)),Sheet1!C:C,0)))

(Normal ENTER will do)

Note that the entire formula above should be in one line. You would need to
rectify the inevitable line breaks / wraps [especially for long formulas]
after you directly copy > paste the formula from the post into the cell /
formula bar.

Copy A1 down to A100
(cover the same range as done in Sheet1's cols B & C)

Sheet will return the desired results neatly bunched at the top,
viz. for the sample data above, you'd get:

-123
124
(blank rows below) ... "
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"chom" <[email protected]> wrote in message
news:[email protected]...
>
> How to compare values in a colum that has several data of positive and
> negative numbers, of which most of them cancel one another. What I am
> trying to do is to delete all the numbers that cancel each other and
> have obly numbers that are not cancel left in that column.
>
> Column A
> 123
> -123
> -325
> 325
> 123
>
> from the example above, after I successfully delete all numbers that
> offsetting each other, I should have only $123 as a total in column A
>
> Please help. Thanks Chom
>
>
> --
> chom
> ------------------------------------------------------------------------
> chom's Profile:
http://www.excelforum.com/member.php...o&userid=25665
> View this thread: http://www.excelforum.com/showthread...hreadid=390810
>

Here's a link to a sample file with the implemented construct:
http://www.savefile.com/files/4522483
File: Compare Neg n Pos Nos_ChomKrusopon_wksht.xls
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----