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