Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Eliminating the high 4 values | Excel Worksheet Functions | |||
Excel 2003-eliminating blank rows in target worksheet | Excel Discussion (Misc queries) | |||
Eliminating 0 values when using the Small formula | Excel Worksheet Functions | |||
Eliminating rows in Excel worksheet | Excel Worksheet Functions | |||
Identifying/Eliminating Worksheet References | Excel Discussion (Misc queries) |