How to match positive and negative values within subgroups
Category: [Excel] Demo Available
How to match positive and negative values within subgroups
Requirement:
The user has a sheet which contains credits and debits for several accounts:
| Customer | Amount |
| ABC | 2000 |
| XYZ | 3000 |
| XYZ | 1000 |
| ABC | 1000 |
| ABC | -5000 |
| XYZ | 1000 |
The user needs to match negative and positive values within each account and highlight them.
Solution:
Code:
Sub x()
Dim rFind As Range, sAddr As String, r As Range, s() As String, i As Long, a, b
With Range("A2", Range("A" & Rows.Count).End(xlUp))
ReDim s(1 To 2 * .Count)
For Each r In .Cells
Set rFind = .Find(What:=r, After:=r, LookAt:=xlWhole, searchdirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not rFind Is Nothing Then
sAddr = rFind.Address
Do
If r.Offset(, 1) = -1 * rFind.Offset(, 1) Then
a = Application.Match(r.Address, Application.Index(s, 1, 0), 0)
b = Application.Match(rFind.Address, Application.Index(s, 1, 0), 0)
If Not IsNumeric(a) And Not IsNumeric(b) Then
r.Resize(, 2).Interior.Color = vbYellow
rFind.Resize(, 2).Interior.Color = vbYellow
i = i + 1
s(i) = r.Address
s(i + 1) = rFind.Address
i = i + 1
Exit Do
End If
End If
Set rFind = .FindNext(rFind)
Loop While rFind.Address <> sAddr And rFind.Address <> r.Address
End If
Set rFind = Nothing
sAddr = ""
Next r
End With
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by StephenR.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to sort values in positive and negative numbers with formula |
| How to average both positive and negative numbers |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.