I have a total column that contains several positive and negative numbers
that offset one another. Example:
Total
123
523
153
-523
-123
Is there a way to write a formula so that excel will delete all the numbers
that wash each other and leaves only numbers that are stand alone. From the
example above it will be $153. Please help ASAP. I have a big worksheet that
has well over 1,000 roles of mixed numbers that I need to delete. Thank YOu,
chom

"chom krusopon" <chom
wrote in message
...

I have a total column that contains several positive and negative
numbers that offset one another. Example:
Total
123
523
153
-523
-123
Is there a way to write a formula so that excel will delete all the
numbers that wash each other and leaves only numbers that are stand
alone. From the example above it will be $153. Please help ASAP. I
have a big worksheet that has well over 1,000 roles of mixed numbers
that I need to delete. Thank YOu, chom


Hi Chom,

See my reply to your other post.

If that doesn't work, reply back there.

Alan.

Hello Alan,

I can't find your reply to my other post. Can you help me again, please?
Here's my sample:
752.00
(752.00)
852.00
(852.00)
(852.00)
5.00
Thank You, Chom
"Alan" wrote:

"chom krusopon" <chom
wrote in message
...

I have a total column that contains several positive and negative
numbers that offset one another. Example:
Total
123
523
153
-523
-123
Is there a way to write a formula so that excel will delete all the
numbers that wash each other and leaves only numbers that are stand
alone. From the example above it will be $153. Please help ASAP. I
have a big worksheet that has well over 1,000 roles of mixed numbers
that I need to delete. Thank YOu, chom


Hi Chom,

See my reply to your other post.

If that doesn't work, reply back there.

Alan.

There may well be some kind of array formula which can extract the numbers
you want but somebody else would have to help us with that.

However, assuming your data is in Column A starting in row 1, Column B is
empty and you want to delete the entire rows where you have numbers that
cancel each other out then this macro should work. NB save your work before
running this - there will be no undo afterwards.

Sub DelThese()
Dim lRow As Long
Dim colA As Range
Dim Cell As Range
Dim Mtch As Range
Dim l As Long

Application.ScreenUpdating = False
lRow = Cells(Rows.Count, 1).End(xlUp).Row
Set colA = Range(Cells(1, 1), Cells(lRow, 1))
For Each Cell In colA
If Cell.Offset(0, 1).Value = Empty Then
Set Mtch = Columns(1).Find(Cell.Value * -1)
If Not Mtch Is Nothing Then
If Mtch.Offset(0, 1).Value = Empty Then
Cell.Offset(0, 1).Value = True
Mtch.Offset(0, 1).Value = True
Else
Cell.Offset(0, 1).Value = False
End If
Else
Cell.Offset(0, 1).Value = False
End If
End If
Next Cell

For l = lRow To 1 Step -1
If Cells(l, 2).Value Then
Cells(l, 2).EntireRow.Delete
End If
Next l
Columns(2).ClearContents
Application.ScreenUpdating = True
End Sub

Note: If you have more than one set of the same offsetting numbers only one
set will be deleted. You can simply run the macro again to delete the other
set.

Hope this helps
Rowan

"chom krusopon" wrote:

I have a total column that contains several positive and negative numbers
that offset one another. Example:
Total
123
523
153
-523
-123
Is there a way to write a formula so that excel will delete all the numbers
that wash each other and leaves only numbers that are stand alone. From the
example above it will be $153. Please help ASAP. I have a big worksheet that
has well over 1,000 roles of mixed numbers that I need to delete. Thank YOu,
chom

Another play to try ..

Assume source data is in col A, from A1 down

Put in B1:
=IF(A1="","",IF(ISNA(MATCH(-A1,A:A,0)),ROW(),""))

Put in C1:
=IF(ISERROR(SMALL(B:B,ROWS($A$1:A1))),"",INDEX(A:A ,MATCH(SMALL(B:B,ROWS($A$1
:A1)),B:B,0)))

Select B1:C1, fill down until the last row of data in col A

Col C should return the required results, neatly bunched at the top

(above assumes the +/- number cancellations within the column
are exactly in pairs)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"chom krusopon" <chom wrote in message
...
I have a total column that contains several positive and negative numbers
that offset one another. Example:
Total
123
523
153
-523
-123
Is there a way to write a formula so that excel will delete all the
numbers
that wash each other and leaves only numbers that are stand alone. From
the
example above it will be $153. Please help ASAP. I have a big worksheet
that
has well over 1,000 roles of mixed numbers that I need to delete. Thank
YOu,
chom

Max, First thanks so much for your reply. It works ALMOST perfectly. The only
problem I still have is when I have more than one set of numbers that cancel
each other out, the formula doesn't capture it. Ex:
123
-123
-123

Your formula will capture the 123 in row 1 and row 2 as being cancelled one
another, but doesn't recognize that the 3rd row should be unique and has
nothing to do with the first two rows. Can you help me a little further,
pleaset? Thank You, Chom

"Max" wrote:

Another play to try ..

Assume source data is in col A, from A1 down

Put in B1:
=IF(A1="","",IF(ISNA(MATCH(-A1,A:A,0)),ROW(),""))

Put in C1:
=IF(ISERROR(SMALL(B:B,ROWS($A$1:A1))),"",INDEX(A:A ,MATCH(SMALL(B:B,ROWS($A$1
:A1)),B:B,0)))

Select B1:C1, fill down until the last row of data in col A

Col C should return the required results, neatly bunched at the top

(above assumes the +/- number cancellations within the column
are exactly in pairs)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"chom krusopon" <chom wrote in message
...
I have a total column that contains several positive and negative numbers
that offset one another. Example:
Total
123
523
153
-523
-123
Is there a way to write a formula so that excel will delete all the
numbers
that wash each other and leaves only numbers that are stand alone. From
the
example above it will be $153. Please help ASAP. I have a big worksheet
that
has well over 1,000 roles of mixed numbers that I need to delete. Thank
YOu,
chom

.. It works ALMOST perfectly. ..

Yes, as per the caveat mentioned in my earlier response <g,
... (above assumes the +/- number cancellations
within the column are exactly in pairs)

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))),"",IN DEX(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 <atyahoo<dotcom
----
"chom krusopon" wrote in message
...
Max, First thanks so much for your reply. It works ALMOST perfectly. The
only
problem I still have is when I have more than one set of numbers that
cancel
each other out, the formula doesn't capture it. Ex:
123
-123
-123

Your formula will capture the 123 in row 1 and row 2 as being cancelled
one
another, but doesn't recognize that the 3rd row should be unique and has
nothing to do with the first two rows. Can you help me a little further,
pleaset? Thank You, Chom

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

I saw your response to to Chom krosopon in July of 2005 and I noticed that
the formula worked perferctly. My problem is similar to Chom's however my
data is a concatenation of date and amount. For example instead of just
looking for +123 and -123 I am looking for 1/2/07 +$123 and 1/2/07 -$123.
Below is a copy of your response to Chom in July of 2005. Is there a formula
that would indicate this?

Thanks

George

One way to amend it to suit is illustrated in this sample:
http://www.savefile.com/files/666555
NettOffNegative_n_PositiveNos_v2.xls

Assume your source concat data is within A1:A100 in Sheet1

In Sheet 1,

Use Data Text to Columns to split the concat data into 2 cols, col A for
the dates, col B for the amounts. Select col A, click Data Text to Columns
(delimited). Click Next, check "Space" in step 2. Click Next. In step 3 of
the wiz., select col A in the data preview window, check "Date", then select
the correct date format from the droplist. Click Finish.

Then place in C1:
=IF(B1="","",COUNTIF($B$1:B1,B1))

Put in D1, array-enter the formula by pressing CTRL+SHIFT+ENTER (instead of
just pressing ENTER):
=IF(C1="","",IF(ISNUMBER(MATCH(-B1&"_"&C1&"_"&A1,$B$1:$B$100&"_"&$C$1:$C$100&"_"&$ A$1:$A$100,0)),"",ROW()))
Select C1:D1, copy down to D100.

In Sheet 2,

Put in A1 (normal ENTER):
=IF(ROW()COUNT(Sheet1!$D:$D),"",INDEX(Sheet1!A:A, SMALL(Sheet1!$D:$D,ROW())))

Copy A1 to B1, then fill down to B100. Format col A as date, col B as
currency to taste. Sheet2 returns the required results, ie only the o/s
lines from Sheet1 with date - amounts which do not cancel each other.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"checkQ" wrote in message
...

I saw your response to to Chom krosopon in July of 2005 and I noticed that
the formula worked perferctly. My problem is similar to Chom's however my
data is a concatenation of date and amount. For example instead of just
looking for +123 and -123 I am looking for 1/2/07 +$123 and 1/2/07 -$123.
Below is a copy of your response to Chom in July of 2005. Is there a
formula
that would indicate this?

Thanks

George

Thank you. It works wonderfully.

"chom krusopon" wrote:

I have a total column that contains several positive and negative numbers
that offset one another. Example:
Total
123
523
153
-523
-123
Is there a way to write a formula so that excel will delete all the numbers
that wash each other and leaves only numbers that are stand alone. From the
example above it will be $153. Please help ASAP. I have a big worksheet that
has well over 1,000 roles of mixed numbers that I need to delete. Thank YOu,
chom

You're welcome !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"chom krusopon" wrote in message
...
Thank you. It works wonderfully.