How do I delete matching positive and negative entries in a column

ghrek

Active Member
Joined
Jul 29, 2005
Messages
427
Hi

In column G of my workbook I have lots of positive and negative values. Im trying to get it to look all the way down column G and if there is a matching positive and negative entry of the same value I need the rows they are on completely deleting.

Any Ideas?

Thanks
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Do you think it could be my PC as it starts to run on excel then comes up NOT RESPONDING. Would it work on office 365
Should work on 365 yes … As on our sides the codes well work … If only you link your source text file or a workbook sample​
on a files host website like Dropbox in order we can test and give it a try for some faster way if really necessary …​
 
Upvote 0
Its strange. It appears to be working them comes up as not responding and then only way I can get out of it is to crash excel via task manager

Heres the actual file im using.

 
Upvote 0
When 'not responding' that often just means Excel is very busy with a VBA procedure execution …​
I will give it a cry try if my slow tests laptop stops to crash today ! :rolleyes:
 
Upvote 0
Obviously yes in particular when the procedure was not made for huge data !​
In the For Each loop add before the Next codeline this one :​
VBA Code:
            If Rg(2).Row Mod 30 = 0 Then DoEvents
As a reminder huge data often needs to be treated under a database software …​
 
Upvote 0
Thanks

Tell u what it is over the complete workbook I have a difference of an amount and I’m trying to find out what makes that difference. Is there any other way I can approach it?
 
Upvote 0

Under Excel ? Yes but I must first find back my post on another forum to try to adapt the procedure to your data …​
Maybe Joe as some ready to go procedure …​
 
Upvote 0
@ghrek
Try this:
The code sort the data by col D. If it's not ok then I can amend the code to sort it back to the initial order.
I use col H as temporary helper column.
The code took about 8 second to finish:
VBA Code:
Sub positive_negative_match2()
'positive-negative match, GROUP
Dim i As Long, z As Long, a As Long
Dim va, vb, vc, m, s, t
Dim d As Object

t = Timer
Application.ScreenUpdating = False
With Range("A1").CurrentRegion
.Sort Key1:=.Cells(1, 4), Order1:=xlAscending, Header:=xlYes
End With
n = Range("D" & Rows.Count).End(xlUp).Row
va = Range("D1:D" & n)
vc = Range("G1:G" & n)
ReDim vb(1 To n, 1 To 1)

For i = 1 To n
    vb(i, 1) = "x" 'mark for NOT positive-negative match
Next
Set d = CreateObject("scripting.dictionary")

For i = 2 To n
d.RemoveAll
    Do
    z = vc(i, 1)
       
        If d.Exists(z) Then
            d(z) = d(z) & ":" & i
        ElseIf d.Exists(-z) Then
            s = Split(d(-z), ":")
            m = s(UBound(s))
            vb(i, 1) = "" 'mark for positive-negative match
            vb(m, 1) = "" 'mark for positive-negative match
                If UBound(s) = 0 Then
                    d.Remove -z
                    Else
                    d(-z) = Left(d(-z), Len(d(-z)) - Len(m) - 1)
                End If
        Else
            d(z) = i
        End If
        i = i + 1
        If i > UBound(va, 1) Then Exit Do
    Loop While va(i, 1) = va(i - 1, 1)
    i = i - 1

Next

Range("H1").Resize(n, 1) = vb
With Range("A1").CurrentRegion
    .Sort Key1:=.Cells(1, 8), Order1:=xlAscending, Header:=xlYes
    a = Range("H" & Rows.Count).End(xlUp).Row + 1
    Rows(a & ":" & n).Delete
    Range("H:H").Delete
End With
Application.ScreenUpdating = True

Debug.Print Timer - t
End Sub

If you want to check whether the code works correctly or not, you can comment this part:
VBA Code:
'With Range("A1").CurrentRegion
'    .Sort Key1:=.Cells(1, 8), Order1:=xlAscending, Header:=xlYes
'    a = Range("H" & Rows.Count).End(xlUp).Row + 1
'    Rows(a & ":" & n).Delete
'    Range("H:H").Delete
'End With

then check col H, the blank cells in col H are the ones that have positive-negative match on the same date, so they are going to be deleted.
 
Upvote 0
Solution

Forum statistics

Threads
1,223,236
Messages
6,170,906
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top