Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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

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

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

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

Sorry, please use this file

Test.xlsx

Test.xlsx

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

```
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
```

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.

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

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

=ABS(A1)

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

```
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
```

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.

```
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("K2").CurrentRegion.Sort key1:=wks.Range("K1"), header:=xlYes
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("K1").CurrentRegion.AdvancedFilter xlFilterInPlace, wksCrit.Range("A1:B3")
wks.Range(wks.Range("K2"), wks.Range("K2").End(xlDown)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
wks.ShowAllData
wks.Range("K:M").Delete
wks.Range("A1").CurrentRegion.Sort key1:=wks.Range("A1"), header:=xlYes
wksCrit.Range("A1:B3").Clear
Application.ScreenUpdating = True
End Sub
```

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.

Formula 1 in column K:

=IF($F2>0,COUNTIFS($F$1:$F

Formula 2 in column L:

IF($F2<0,COUNTIFS($F$1:$F$

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$

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

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.

All Courses

From novice to tech pro — start learning today.

You changed the criteria of the question...

In the attached file, the COUNTIF has been changed to

=COUNTIFS([Period],[@Perio

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