Eliminating Values from a worksheet
 

Hello I am hoping someone online here can help me with my delima.

I have a huge worksheet of values in US dollars, and these values are both
positive and negitive. I would like to know if their is a way to eliminate
the lines which cancel each other out. Bascially I am trying to sum the
values but eliminate the values which cancel each other out.

For example line 10 is $100
line 200 is -$100

Is there some type of function i can use to eliminate all values which sum
to 0 ?

Thanks in advance for any help or advice offered

Cheers,

Terry

Eliminating Values from a worksheet
 

In fact I do not even need to eliminate the values, rather just identify
which ones will sum to zero with their equal but opposite counterpart.

"Terry" wrote:

Hello I am hoping someone online here can help me with my delima.

I have a huge worksheet of values in US dollars, and these values are both
positive and negitive. I would like to know if their is a way to eliminate
the lines which cancel each other out. Bascially I am trying to sum the
values but eliminate the values which cancel each other out.

For example line 10 is $100
line 200 is -$100

Is there some type of function i can use to eliminate all values which sum
to 0 ?

Thanks in advance for any help or advice offered

Cheers,

Terry

Eliminating Values from a worksheet
 

try this
Sub findoppanddelete()
With Worksheets("sheet13").Columns("a")
mc = "a" '
For i = Cells(rows.Count, mc).End(xlUp).Row To 1 Step -1
Set c = .Find(-Cells(i, mc), LookIn:=xlValues, LookAt:=xlWhole)

If Not c Is Nothing Then
rows(i).Delete
rows(c.Row).Delete
End If

Next i
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Terry" wrote in message
...
Hello I am hoping someone online here can help me with my delima.

I have a huge worksheet of values in US dollars, and these values are both
positive and negitive. I would like to know if their is a way to eliminate
the lines which cancel each other out. Bascially I am trying to sum the
values but eliminate the values which cancel each other out.

For example line 10 is $100
line 200 is -$100

Is there some type of function i can use to eliminate all values which sum
to 0 ?

Thanks in advance for any help or advice offered

Cheers,

Terry

Eliminating Values from a worksheet
 

Sub findoppandidentify()
With Worksheets("sheet13").Columns("a")
mc = "a" '
For i = Cells(rows.Count, mc).End(xlUp).Row To 1 Step -1
Set c = .Find(-Cells(i, mc), LookIn:=xlValues, LookAt:=xlWhole)

If Not c Is Nothing Then
Cells(c.Row, mc).Offset(, 1).Value = " Matches " & Cells(i, mc)

End If
Next i
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Terry" wrote in message
...
In fact I do not even need to eliminate the values, rather just identify
which ones will sum to zero with their equal but opposite counterpart.

"Terry" wrote:

Hello I am hoping someone online here can help me with my delima.

I have a huge worksheet of values in US dollars, and these values are
both
positive and negitive. I would like to know if their is a way to
eliminate
the lines which cancel each other out. Bascially I am trying to sum the
values but eliminate the values which cancel each other out.

For example line 10 is $100
line 200 is -$100

Is there some type of function i can use to eliminate all values which
sum
to 0 ?

Thanks in advance for any help or advice offered

Cheers,

Terry

Eliminating Values from a worksheet
 

Hi Terry,

Assuming that values being evaluated are in column A, enter the following in
an empty column:

=IF(COUNTIF(A:A,-A1)=0,"",IF(COUNTIF(A:A,-A1)=COUNTIF(A:A,A1),"Sums to
Zero",""))

Hope this helps.
Peggy

"Terry" wrote:

In fact I do not even need to eliminate the values, rather just identify
which ones will sum to zero with their equal but opposite counterpart.

"Terry" wrote:

Hello I am hoping someone online here can help me with my delima.

I have a huge worksheet of values in US dollars, and these values are both
positive and negitive. I would like to know if their is a way to eliminate
the lines which cancel each other out. Bascially I am trying to sum the
values but eliminate the values which cancel each other out.

For example line 10 is $100
line 200 is -$100

Is there some type of function i can use to eliminate all values which sum
to 0 ?

Thanks in advance for any help or advice offered

Cheers,

Terry