compare -neg and +pos number that cancel each other in a column

[Guest]

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

 

[Alan]

"chom krusopon" <chom (e-mail address removed)>
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.

 

[Guest]

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

 

[Max]

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)

 

[Guest]

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

 

[Guest]

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

 

[Max]

.. It works ALMOST perfectly. ..


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)

 

[Max]

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

 

[Guest]

Thank you. It works wonderfully.

 

[Max]

You're welcome !

 

[Guest]

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

 

[Max]

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 C11, 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.

 

[Guest]

Awsome!!
It worked perfectly Max. Thanks

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 C11, 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]

Welcome, George.
Good to hear that.

 

[Casper]

Hi Max,

I have a similar case, can you please help. Below is the data table I have.
On the 4th column "Status", I need to know which invoices remain outstanding
and which ones cancel off (squares with Credit amount). Will appreciate very
much if you can teach me a quick way to do it.

Customer Doc No. Amount Status
Susan Inv3355 893.10 Cancels off
Nelly Inv3356 250.14 Cancels off
Tom Inv3357 462.91 Cancels off
Nelly Cre1221 -250.14 Cancels off
Nelly Inv3358 350.14 Outstanding
Susan Cre1222 -893.10 Cancels off
Susan Inv3359 793.10 Outstanding
Tom Cre1223 -462.91 Cancels off
Tom Inv3360 462.91 Outstanding
Burt Cre1224 101.99 Outstanding