Finding matching positive and negative numbers

[starheartbeam]

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:

[TABLE="width: 144"]
<colgroup><col width="64" style="width: 48pt;" span="3"> <tbody>[TR]
[TD="width: 64, bgcolor: #EBEBEB"]ID[/TD]
[TD="width: 64, bgcolor: #EBEBEB"]Other[/TD]
[TD="width: 64, bgcolor: #EBEBEB"]Amount[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: white"]213980[/TD]
[TD="width: 64, bgcolor: white"]ON-HAND[/TD]
[TD="bgcolor: transparent, align: right"]-55[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: white"]174975[/TD]
[TD="width: 64, bgcolor: white"]ON-HAND[/TD]
[TD="bgcolor: transparent, align: right"]12[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: white"]999998[/TD]
[TD="width: 64, bgcolor: white"]ON-HAND[/TD]
[TD="bgcolor: transparent, align: right"]55[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: white"]999998[/TD]
[TD="width: 64, bgcolor: white"]ON-HAND[/TD]
[TD="bgcolor: transparent, align: right"]-55[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: white"]110081[/TD]
[TD="width: 64, bgcolor: white"]ON-HAND[/TD]
[TD="bgcolor: transparent, align: right"]101[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: white"]110081[/TD]
[TD="width: 64, bgcolor: white"]ON-HAND[/TD]
[TD="bgcolor: transparent, align: right"]-101[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: white"]110081[/TD]
[TD="width: 64, bgcolor: white"]ON-HAND[/TD]
[TD="bgcolor: transparent, align: right"]23[/TD]
[/TR]
</tbody>[/TABLE]

 

[Col Delane]

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.

 

[Akuini]

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.

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

[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
[Table="width:, class:head"][tr=bgcolor:#008B8B][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

1​

[/td][td=bgcolor:#EBEBEB]D[/td][td=bgcolor:#EBEBEB]Other[/td][td=bgcolor:#EBEBEB]Amount[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

2​

[/td][td]

123​

[/td][td][/td][td=bgcolor:#FFFF00]

-8​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

3​

[/td][td]

123​

[/td][td][/td][td=bgcolor:#FFFF00]

8​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

4​

[/td][td]

123​

[/td][td][/td][td]

8​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

5​

[/td][td]

123​

[/td][td][/td][td]

8​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

6​

[/td][td]

123​

[/td][td][/td][td]

-12​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

7​

[/td][td]

110081​

[/td][td]ON-HAND[/td][td=bgcolor:#FFFF00]

101​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

8​

[/td][td]

110081​

[/td][td]ON-HAND[/td][td=bgcolor:#FFFF00]

-101​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

9​

[/td][td]

110081​

[/td][td]ON-HAND[/td][td]

23​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

10​

[/td][td]

174975​

[/td][td]ON-HAND[/td][td]

12​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

11​

[/td][td]

213980​

[/td][td]ON-HAND[/td][td]

-55​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

12​

[/td][td]

999998​

[/td][td]ON-HAND[/td][td=bgcolor:#FFFF00]

55​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

13​

[/td][td]

999998​

[/td][td]ON-HAND[/td][td=bgcolor:#FFFF00]

-55​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

 

[MickG]

Another option to turn Matching cells to Vbyellow.

[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

 

[Fluff]

Or using Conditional Formatting
Use this formula
=SUMPRODUCT(($A$2:$A$9=$A2)*($C$2:$C$9=$C2*-1))>0

 

[starheartbeam]

This works! Thank you.