Macro to delete rows that contain values of exact but opposite value

[nlamb]

Hi,

I am looking for a macro to delete rows where there is another row in the worksheet that has the exact same value but is opposite

Eg.

Column 1 Column 2 Column 3
Date Description Amount
01/01/2013 Deposit $100.00
01/02/2013 POS Deposit ($100.00)
01/07/2013 Cheque $546.00
01/09/2013 PAD $312.23
01/10/2013 Cheque ($546.00)
01/13/2013 Deposit $50.36

In this example, the rows in red font will be deleted

I have no knowledge of macros but have been told there should be a way for excel to match and detele the rows for me. There are approximately 7,000 rows of data to match and delete.

Any info/help is greatly appreciated!!!

 

[Jeffrey Lopez]

The first problem I can see running into is that there is really no way to tell that it is a duplicate row other than the opposite values...

In your example:

01/01/2013 Deposit $100.00
01/02/2013 POS Deposit ($100.00)

Are not dups... so lets say we have another row further down, that just happens to be:

01/07/2013 Deposit ($100.00)

Would that one be deleted? So you can see where this is going, you may end up deleting a row that may not be a duplicate just because there is a value that matches another...

If that will not be an issue, we should be able to write something up...

 

[nlamb]

The only information that needs to be matched is the amount, the date and description will almost never match.

What I need is for the macro to match one negative value to one positive value, only deleting two rows.

If there are two values of $100.00 and one value of ($100.00), only one positive row and one negative row should be deleted, leaving one positive row

 

[tlowry]

Does this work for you?

Sub RemoveOps()
    Dim cCell, fnd, rng
    Set rng = Range("C1:C" & Cells(Rows.Count, 3).End(xlUp).Row)
    For Each cCell In rng
        If Not IsError(cCell) Then
            Set fnd = rng.Find(what:=-cCell.Value, LookIn:=xlValues, lookat:=xlWhole)
            If Not fnd Is Nothing Then
                Cells(cCell.Row, cCell.Column) = "#N/A"
                Cells(fnd.Row, fnd.Column) = "#N/A"
            End If
        End If
    Next
    Application.Rows.SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
 End Sub

 

[JLGWhiz]

Copy this to the standard code module.

I don't know if I covered all the angles or not. Run this on a copy of your file and let me know if there are any hiccups.

Sub posNeg()
Dim sh As Worksheet, lr As Long, rng As Range, fVal As Range, i As Long
Dim ar1 As Variant, ar2 As Variant, typ As String, trans As String
Set sh = Sheets(1) 'Edit sheet name
lr = sh.Cells(Rows.Count, 3).End(xlUp).Row
Set rng = sh.Range("C2:C" & lr)
    For i = lr To 2 Step -1
        ar1 = Split(sh.Cells(i, 2).Value, " ")
        typ = ar1(UBound(ar1))
        Set fVal = rng.Find(-sh.Cells(i, 3).Value, LookIn:=xlValues, LookAt:=xlWhole)
            If Not fVal Is Nothing Then
                fadr = fVal.Address
                Do
                ar2 = Split(fVal.Offset(0, -1), " ")
                trans = ar2(UBound(ar2))
                If typ = trans Then
                    Rows(i).Delete
                    fVal.EntireRow.Delete
                    Exit Do
                End If
                fVal = -sh.Cells(i, 3).Value
                Set fVal = rng.FindNext(fVal)
                Loop While fVal.Address <> fadr
            End If
    Next
End Sub

 

[nlamb]

tlowry - that worked! Thank you so so much, you have saved me weeks of work!! I cant thank you enough!

 

[albytrader]

Hi nlamb,

I have the same problem and am trying to figure out how to delete rows that contain exact but opposite values. I see this worked for you and I am not quite an Excel genius and am wondering if you can please guide me through to how you solve this problem. I tried putting in the VB code but am getting errors.
Wondering if you can post or e-mail me a step by step way of doing this. Really appreciate your help.

 

[albytrader]

Hi tlowry,

I have the same problem and nlamb and am trying to figure out how to delete rows that contain exact but opposite values. I see this worked for nlamb and I am not quite an Excel genius and am wondering if you can please guide me through to how you solve this problem. I tried putting in the <acronym title="vBulletin">VB</acronym> code but am getting errors.
I know I am doing something wrong and am wondering if you can post or e-mail me a step by step way of doing this. Really appreciate your help.