# Excel Formula

Hello,

I have an Excel Spreadsheet with positive and negative values in Column A.
For Example:

-100.00
100.00
-50.00
50.00
20
10

How can I remove the values that equal each other regardless of the negative or positive value, so in my example remove the 100, -100, 50 & -50 and only keep those values where there isn’t a match? i.e. the 20 & 10
###### Who is Participating?

Commented:
Jspc,

You changed the criteria of the question...

In the attached file, the COUNTIF has been changed to
=COUNTIFS([Period],[@Period],[ABS],[@ABS], [Deposit No.],[@[Deposit No.]])
This will find unique values based on the Period, the Amount and the Deposit Number.   The attached file has the original table in one worksheet and the cleaned table in a second worksheet and match your subtotals.

Thanks - Jerry
EE-28959762-jp-v2.xlsx
0

EngineerCommented:
Try this macro

Sub delabsdups()
Dim i As Long, j As Long
i = 2
Do While Cells(i, 1) <> ""
j = i + 1
Do While Cells(j, 1) <> ""
If Abs(Cells(i, 1)) = Abs(Cells(j, 1)) Then
Cells(j, 1).EntireRow.Delete
Cells(i, 1).EntireRow.Delete
i = i - 1
Exit Do
End If
j = j + 1
Loop
i = i + 1
Loop
End Sub
0

Commented:
HI,

maybe you wanted to delete where the sum = 0
``````Sub DelSumZero()
Dim i As Long, j As Long
i = 2
Do While Cells(i, 1) <> ""
j = i + 1
Do While Cells(j, 1) <> ""
If Cells(i, 1) + Cells(j, 1) = 0 Then
Cells(j, 1).EntireRow.Delete
Cells(i, 1).EntireRow.Delete
i = i - 1
Exit Do
End If
j = j + 1
Loop
i = i + 1
Loop
End Sub
``````
Regards
0

Author Commented:
Hi Saqib,

I ran your script in a Marco and it removed 95% of the data. I know this is not correct as there should be thousands of entries remaining.
0

Author Commented:
Running your script Rgonzo1971 now - There are 25,000 records in the Sheet and the Marco has been going for over 5 min now, I think my Excel has now crashed.
0

EngineerCommented:
jspc, your response is very general and cannot help us understand your issue. Can you be more specific, with an example?
0

Author Commented:
I don’t know how to be more specific with my question than the original question posted at the start.
0

Author Commented:
Test.xlsx
0

Commented:
jspc,
In the attached file two columns were added.  The first to calculate the absolute value of the Amount column.  The second uses the COUNTIF function to count the number of those values that are the same.   Rows with multiple values that equal each other (+/-) will have a COUNTIF value greater than 1 and can be deleted.   Rows with 1 in the COUNTIF column are unique with no matching value in any other rows.   When the COUNTIF column is filtered to show only the 1's, there are 8,372 of 25,888 rows that are unique.
The sheet calculation take at least 30 seconds to update on my PC so please be aware.  You may want to copy / paste values so the sheet is easier to work with.

Thanks - Jerry
EE-28959762-jp.xlsx
0

Commented:
then try (swifter code)
``````Sub DelSumZero()
Dim i As Long, j As Long
i = 2
Do While Cells(i, 6) <> ""
If Evaluate("=SUMPRODUCT(--((F2:F1000000)=-" & Cells(i, 6).Address(False, False) & "))") Then
j = i + 1
Do While Cells(j, 6) <> ""
If Cells(i, 6) + Cells(j, 6) = 0 Then
Cells(j, 6).EntireRow.Delete
Cells(i, 6).EntireRow.Delete
i = i - 1
Exit Do
End If
j = j + 1
Loop
End If
i = i + 1
Loop
End Sub
``````
0

Change and Transition ManagerCommented:
alternatively, if you don't want to use code:

add a column: enter the formula in row1:
=if(a1<0,a1*-1,a1)
copy this down the entire sheet
select remove duplicates from the menu and use this field to remove the duplicate records.
0

Author Commented:
If I apply a Subtotal by column ‘Period’, then total the ‘Amount’ field I get a value. Please see attached example.
Focusing on July in this case, July total should be \$49177

This value is the result of all the non-offset values, so the unique values.
After I run any suggested formula it doesn't leave this value, it should.

So at the end of the day, I’m trying to eliminate all the other entries and leave only those which have no offset.
Untitled.png
Test.xlsx
0

Change and Transition ManagerCommented:
I'm sorry, but your test file just above still has multiple duplicates...

e.g. I see on line 50,51,68 and 69 a value of either 1288 or -1288

But, I stand corrected, my way of working will remove all the duplicates, but will leave the first encounter of any hit in the list...and i guess you don't want that
0

Finance AnalystCommented:
@koen - the ABS function does the same as your IF statement.

=ABS(A1)

will convert A1 to the absolute value, whether it is positive or negative.
0

Commented:
Then try

``````Sub DelSumZero()
Application.ScreenUpdating = False
Dim i As Long, j As Long
i = 2
Do While Cells(i, 6) <> ""
If Evaluate("=SUMPRODUCT(--((F2:F1000000)=-" & Cells(i, 6).Address(False, False) & "),--(C2:C1000000=" & Cells(i, 3).Address(False, False) & "),--(G2:G1000000=" & Cells(i, 7).Address(False, False) & "))") Then
Debug.Print i
j = i + 1
Do While Cells(j, 6) <> "" And Cells(i, 7) = Cells(j, 7)
If Cells(i, 6) + Cells(j, 6) = 0 And Cells(i, 3) = Cells(j, 3) Then
Cells(j, 6).EntireRow.Delete
Cells(i, 6).EntireRow.Delete
i = i - 1
Exit Do
End If
j = j + 1
Loop
End If
i = i + 1
Loop
End Sub
``````
0

Finance AnalystCommented:
I think the issue is because it is not as simple as a pair with one positive and one negative. I was working on something in the office but had to dash for a train before I could upload.

In 2 separate columns do countifs formulas, in plain speak

Formula 1: If value in F is less than zero count number of occurences of -F for that period less number of occurences of F for that period else 0

Formula 2: If value in F is more than zero count number of occurences of F for that period less number of occurences of -F for that period else 0

In a third column add those two together. Where there are equal number of opposites the sum will be zero.

Apply a filter and filter on period and the third added column to exclude zero.

Doing this for July 15 showed the \$49k like you had.
0

Commented:
Set processing approach using advanced filter.  Takes about 40 seconds on my ancient laptop.
``````Sub Q_28959762()
Dim wks As Worksheet, wksCrit As Worksheet

Set wks = Worksheets("Sheet1")
Application.ScreenUpdating = False

wks.Range("K1:M1").Value = Array("ABS", "EqNext", "EqPrev")
wks.Range("K2").Formula = "=ABS(F2)"
wks.Range("K2").AutoFill wks.Range(wks.Range("K2"), wks.Range("J2").End(xlDown).Offset(0, 1))
wks.Range(wks.Range("K2"), wks.Range("K2").End(xlDown)).Value = wks.Range(wks.Range("K2"), wks.Range("K2").End(xlDown)).Value
wks.Range("L2").Formula = "=K2=K3"
wks.Range("M2").Formula = "=K2=K1"
wks.Range("L2:M2").AutoFill wks.Range(wks.Range("L2:M2"), wks.Range("K2").End(xlDown).Offset(0, 1))
wks.Range(wks.Range("L2:M2"), wks.Range("L2").End(xlDown)).Value = wks.Range(wks.Range("L2:M2"), wks.Range("L2").End(xlDown)).Value

Set wksCrit = Worksheets("Sheet2")
wksCrit.Range("A1:B1").Value = Array("EqNext", "EqPrev")
wksCrit.Range("A2").Value = True
wksCrit.Range("B3").Value = True

wks.Range(wks.Range("K2"), wks.Range("K2").End(xlDown)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
wks.ShowAllData

wks.Range("K:M").Delete
wksCrit.Range("A1:B3").Clear

Application.ScreenUpdating = True
End Sub
``````
0

Author Commented:

I was asked to give greater detail so needed to alter the question.

Thank you for you help and thank you to everyone who has given input, much appreciated.
0

Commented:
Jspc,

No problem at all.  Glad we we're able find a solution to your question.

Thanks - Jerry
0

Finance AnalystCommented:
I know you have now closed the question but here is the solution that I was working on yesterday:

Formula 1 in column K:
=IF(\$F2>0,COUNTIFS(\$F\$1:\$F\$25888,-\$F2,\$G\$1:\$G\$25888,\$G2)-COUNTIFS(\$F\$1:\$F\$25888,\$F2,\$G\$1:\$G\$25888,\$G2),0)

Formula 2 in column L:
IF(\$F2<0,COUNTIFS(\$F\$1:\$F\$25888,-\$F2,\$G\$1:\$G\$25888,\$G2)-COUNTIFS(\$F\$1:\$F\$25888,\$F2,\$G\$1:\$G\$25888,\$G2),0)

Formula 3 in Column M:
=SUM(K2:L2)

Filter on Column G for July 15 and column M to exclude 0 and the sum of column F gives \$49177.20

The 3 formulas could be combined into one column if you wished:

=SUM(IF(\$F2>0,COUNTIFS(\$F\$1:\$F\$25888,-\$F2,\$G\$1:\$G\$25888,\$G2)-COUNTIFS(\$F\$1:\$F\$25888,\$F2,\$G\$1:\$G\$25888,\$G2),0),IF(\$F2<0,COUNTIFS(\$F\$1:\$F\$25888,-\$F2,\$G\$1:\$G\$25888,\$G2)-COUNTIFS(\$F\$1:\$F\$25888,\$F2,\$G\$1:\$G\$25888,\$G2),0))

To end up with just the required data you have two options:
1) Filter on column M to exclude 0 and then copy the visible data elsewhere. When a filter is applied copying a block of data will only copy those cells that are visible and pasting will paste as a contiguous block.
2) Filter to show only 0 and then delete the visible rows. Again selecting as a block and deleting rows will only delete the visible rows, leaving the hidden rows untouched.

Thanks
Rob H
0

Commented:
So, you didn't need to eliminate values based on ABS()?
There is a great deal of difference between your \$49177 sum and my \$6683.82 for the July 15 period, if only eliminating 'duplicates' within a time period.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.