Finding matching positive and negative numbers

S

starheartbeam

New Member
Joined
Aug 8, 2018
Messages
18
I have thousands of rows of data that I have to look through to see if there are matching positive and negative numbers matching by ID and then move the matching pair to a different tab. (Or at the very last highlight I have been trying to find an easer way to do this then having to manually do this as it takes me hours to do every week.

Example of Data:

IDOtherAmount
213980ON-HAND-55
174975ON-HAND12
999998ON-HAND55
999998ON-HAND-55
110081ON-HAND101
110081ON-HAND-101
110081ON-HAND23
<colgroup><col width="64" style="width: 48pt;" span="3"> <tbody> </tbody>
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
There are various techniques for handling this often encountered "reconciliation" task - a broader search of this site and others will reveal some - and the solution depends on the level of variability and complexity in the data and sophistication required.
If your data is reasonably simple (e.g. only one +ve and one -ve value for each IDs) then the solution is also simple.

In a spare column (say D in your example if these are A:C), headed "Absolute Value", enter the formula "=ABS(C2)" in the first data row, and then copy down the range. This will enable you to match amounts irrespective of their + or - sign.
You can then, in column E, concatenate the ID with the ABS value with formula "=A2 & "_" & D2" and copy down to create a (hopefully) unique value which will enable matching.
To do the matching, add a further formula in F, which counts how many entries in E match each other, viz: =COUNTIFS( D:D, D2 ) and copy down. So for your two 99998 IDs you should see a 2 returned next to each entry as there are 2 x 99998_55 values in D:D. However, be warned that two +ve values will also match!!
A value in F of 1 means there is no match (only one entry)
A value in F of 4 means there are 2 pairs of matching +ve and -ve (or any combination thereof!)

As you can see this is not foolproof, so you will need to experiment with additional formulas that will help you narrow down the matching (& therefore non-matching) entries.
Autofilters and/or a Pivot Table may help to summarise the position once you get table of data populated with appropriate "helper" values.
 
Upvote 0
starheartbeam said:
I have thousands of rows of data that I have to look through to see if there are matching positive and negative numbers matching by ID and then move the matching pair to a different tab. (Or at the very last highlight I have been trying to find an easer way to do this then having to manually do this as it takes me hours to do every week.
Click to expand...

If you're ok with vba then you can try this code.
First you need to sort the data by column A then run the macro.
Note: actually I've answered a similar problem in this thread:
https://www.mrexcel.com/forum/excel-questions/1075525-match-positive-numeric-value-negative.html

Code: 
[B][color=Royalblue]Sub[/color][/B] a1077651a()
[i][color=seagreen]'https://www.mrexcel.com/forum/excel-questions/1077651-finding-matching-positive-negative-numbers.htmlDim  i As Long, z As Long, ch As Long, f As Long[/color][/i]
[B][color=Royalblue]Dim[/color][/B] rng [B][color=Royalblue]As[/color][/B] Range
[B][color=Royalblue]Dim[/color][/B] m [B][color=Royalblue]As[/color][/B] [B][color=Royalblue]String[/color][/B]
[B][color=Royalblue]Dim[/color][/B]  va [B][color=Royalblue]As[/color][/B]  [B][color=Royalblue]Variant[/color][/B], vx  [B][color=Royalblue]As[/color][/B]  [B][color=Royalblue]Variant[/color][/B], s  [B][color=Royalblue]As[/color][/B]  [B][color=Royalblue]Variant[/color][/B]
[B][color=Royalblue]Dim[/color][/B] d [B][color=Royalblue]As[/color][/B] [B][color=Royalblue]Object[/color][/B]
[B][color=Royalblue]Dim[/color][/B]  rr [B][color=Royalblue]As[/color][/B]  [B][color=Royalblue]Long[/color][/B], f  [B][color=Royalblue]As[/color][/B] [B][color=Royalblue]Long[/color][/B],  g [B][color=Royalblue]As[/color][/B]  [B][color=Royalblue]Long[/color][/B], i  [B][color=Royalblue]As[/color][/B] [B][color=Royalblue]Long[/color][/B],  z [B][color=Royalblue]As[/color][/B]  [B][color=Royalblue]Long[/color][/B]

Application.ScreenUpdating = [B][color=Royalblue]False[/color][/B]

rr = Range([color=brown]"A"[/color] & Rows.count).[B][color=Royalblue]End[/color][/B](xlUp).row
vx = Range([color=brown]"A1:A"[/color] & rr + [color=crimson]1[/color])
[B][color=Royalblue]For[/color][/B]  f = [color=crimson]2[/color] [B][color=Royalblue]To[/color][/B]  UBound(vx, [color=crimson]1[/color]) - [color=crimson]1[/color]
    g = f
    
     [B][color=Royalblue]Do[/color][/B]  [B][color=Royalblue]While[/color][/B] vx(f, [color=crimson]1[/color]) =  vx(f + [color=crimson]1[/color], [color=crimson]1[/color])
    f = f + [color=crimson]1[/color]
    [B][color=Royalblue]Loop[/color][/B]
    
     [B][color=Royalblue]If[/color][/B] f = g  [B][color=Royalblue]Then[/color][/B]  [B][color=Royalblue]GoTo[/color][/B]  [B][color=Royalblue]skip[/color][/B]:
    
    [B][color=Royalblue]Set[/color][/B] rng = Range(Cells(g, [color=brown]"C"[/color]), Cells(f, [color=brown]"C"[/color]))
    va = rng
    [B][color=Royalblue]Set[/color][/B] d = CreateObject([color=brown]"scripting.dictionary"[/color])
    
     [B][color=Royalblue]For[/color][/B] i = [color=crimson]1[/color]  [B][color=Royalblue]To[/color][/B] f - g + [color=crimson]1[/color]
        z = va(i, [color=crimson]1[/color])
           
            [B][color=Royalblue]If[/color][/B] d.Exists(z) [B][color=Royalblue]Then[/color][/B]
                d(z) = d(z) & [color=brown]","[/color] & i
            [B][color=Royalblue]ElseIf[/color][/B] d.Exists(-z) [B][color=Royalblue]Then[/color][/B]
                s = Split(d(-z), [color=brown]","[/color])
                m = s(UBound(s))
                va(i, [color=crimson]1[/color]) = va(i, [color=crimson]1[/color]) & [color=brown]"#"[/color]
                va(m, [color=crimson]1[/color]) = va(m, [color=crimson]1[/color]) & [color=brown]"#"[/color]
                    [B][color=Royalblue]If[/color][/B] UBound(s) = [color=crimson]0[/color] [B][color=Royalblue]Then[/color][/B]
                        d.Remove -z
                        [B][color=Royalblue]Else[/color][/B]
                        d(-z) = Left(d(-z), Len(d(-z)) - Len(m) - [color=crimson]1[/color])
                    [B][color=Royalblue]End[/color][/B] [B][color=Royalblue]If[/color][/B]
            [B][color=Royalblue]Else[/color][/B]
                d(z) = i
            [B][color=Royalblue]End[/color][/B] [B][color=Royalblue]If[/color][/B]
    [B][color=Royalblue]Next[/color][/B] i
    rng = va
[B][color=Royalblue]skip[/color][/B]:
[B][color=Royalblue]Next[/color][/B] f
    
[B][color=Royalblue]Set[/color][/B]  rng = Range([color=brown]"C1"[/color], Cells(Rows.count,  [color=brown]"C"[/color]).[B][color=Royalblue]End[/color][/B](xlUp))
 
Application.ReplaceFormat.Interior.Color = vbYellow
     rng.Replace What:=[color=brown]"#"[/color],  Replacement:=[color=brown]""[/color], LookAt:=xlPart,  SearchOrder:=xlByRows, MatchCase:=[B][color=Royalblue]True[/color][/B], _
          SearchFormat:=[B][color=Royalblue]False[/color][/B], ReplaceFormat:=[B][color=Royalblue]True[/color][/B]
     rng.Replace What:=[color=brown]"#"[/color],  Replacement:=[color=brown]""[/color], LookAt:=xlPart,  SearchOrder:=xlByRows, MatchCase:=[B][color=Royalblue]True[/color][/B], _
          SearchFormat:=[B][color=Royalblue]False[/color][/B], ReplaceFormat:=[B][color=Royalblue]False[/color][/B]
Application.ReplaceFormat.Clear
Application.ScreenUpdating = [B][color=Royalblue]True[/color][/B]
[B][color=Royalblue]End[/color][/B] [B][color=Royalblue]Sub[/color][/B]


Excel 2007 32 bit
A​
B​
C​
1​
DOtherAmount
2​
123​
-8​
3​
123​
8​
4​
123​
8​
5​
123​
8​
6​
123​
-12​
7​
110081​
ON-HAND
101​
8​
110081​
ON-HAND
-101​
9​
110081​
ON-HAND
23​
10​
174975​
ON-HAND
12​
11​
213980​
ON-HAND
-55​
12​
999998​
ON-HAND
55​
13​
999998​
ON-HAND
-55​
Sheet: Sheet1
 
Upvote 0
Another option to turn Matching cells to Vbyellow.
Code: 
[COLOR="Navy"]Sub[/COLOR] MG15Nov05
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Rng         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dic         [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Dim[/COLOR] Q           [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] n1          [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] n2          [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
   
   [COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
     [COLOR="Navy"]Set[/COLOR] Dic = CreateObject("Scripting.Dictionary")
        Dic.CompareMode = 1
   [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
           n1 = 0: n2 = 0
            [COLOR="Navy"]If[/COLOR] Not Dic.exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]Set[/COLOR] Dic(Dn.Value) = CreateObject("Scripting.Dictionary")
            [COLOR="Navy"]End[/COLOR] If
        
        [COLOR="Navy"]If[/COLOR] Not Dic(Dn.Value).exists(Abs(Dn.Offset(, 2).Value)) [COLOR="Navy"]Then[/COLOR]
                ReDim ray(1 To Rng.Count, 1 To 2)
                [COLOR="Navy"]If[/COLOR] Dn.Offset(, 2) > 0 [COLOR="Navy"]Then[/COLOR]
                    [COLOR="Navy"]Set[/COLOR] ray(1, 1) = Dn.Offset(, 2)
                    n1 = n1 + 1
                [COLOR="Navy"]Else[/COLOR]
                    [COLOR="Navy"]Set[/COLOR] ray(1, 2) = Dn.Offset(, 2)
                    n2 = n2 + 1
                [COLOR="Navy"]End[/COLOR] If
                Dic(Dn.Value).Add (Abs(Dn.Offset(, 2).Value)), Array(ray, n1, n2)
        [COLOR="Navy"]Else[/COLOR]
                Q = Dic(Dn.Value).Item(Abs(Dn.Offset(, 2).Value))
                    [COLOR="Navy"]If[/COLOR] Dn.Offset(, 2) > 0 [COLOR="Navy"]Then[/COLOR]
                        Q(1) = Q(1) + 1
                        [COLOR="Navy"]Set[/COLOR] Q(0)(Q(1), 1) = Dn.Offset(, 2)
                    [COLOR="Navy"]Else[/COLOR]
                        Q(2) = Q(2) + 1
                        [COLOR="Navy"]Set[/COLOR] Q(0)(Q(2), 2) = Dn.Offset(, 2)
                    [COLOR="Navy"]End[/COLOR] If
                Dic(Dn.Value).Item(Abs(Dn.Offset(, 2).Value)) = Q
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Dn
   
   [COLOR="Navy"]Dim[/COLOR] k [COLOR="Navy"]As[/COLOR] Variant
   [COLOR="Navy"]Dim[/COLOR] p [COLOR="Navy"]As[/COLOR] Variant
   [COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
   
      [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] k [COLOR="Navy"]In[/COLOR] Dic.Keys
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] p [COLOR="Navy"]In[/COLOR] Dic(k)
            [COLOR="Navy"]For[/COLOR] n = 1 To UBound(Dic(k).Item(p)(0), 1)
                [COLOR="Navy"]If[/COLOR] Dic(k).Item(p)(0)(n, 1) <> "" [COLOR="Navy"]Then[/COLOR]
                   [COLOR="Navy"]If[/COLOR] Dic(k).Item(p)(0)(n, 1) + Dic(k).Item(p)(0)(n, 2) = 0 [COLOR="Navy"]Then[/COLOR]
                        Dic(k).Item(p)(0)(n, 1).Interior.Color = vbYellow
                        Dic(k).Item(p)(0)(n, 2).Interior.Color = vbYellow
                    [COLOR="Navy"]End[/COLOR] If
               [COLOR="Navy"]End[/COLOR] If
           [COLOR="Navy"]Next[/COLOR] n
       [COLOR="Navy"]Next[/COLOR] p
  [COLOR="Navy"]Next[/COLOR] k
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Last edited:
Upvote 0
Or using Conditional Formatting
Use this formula
=SUMPRODUCT(($A$2:$A$9=$A2)*($C$2:$C$9=$C2*-1))>0
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Chance Existing Excel formula to provide positive or negative result
Replies
1
Views
196
iggydarsa
iggydarsa
R
  • Question
Matching positive and negative values
2
Replies
14
Views
274
Akuini
Akuini
J
  • Question
Formula to match negative and positive numbers
Replies
1
Views
1K
KRice
K
J
  • Solved
SUMIF Positive, then Negative numbers at each Group change
Replies
3
Views
2K
Fluff
Fluff
R
  • Question
Finding matching positive and negative numbers
2 3
Replies
21
Views
2K
Joe4
Joe4

Forum statistics

Threads
1,214,920
Messages
6,122,269
Members
449,075
Latest member
staticfluids

Share this page

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