Match the positive numeric value with negative

R

Rajneesh Rawat

New Member
Joined
Mar 31, 2017
Messages
36
Hi All,

I have one query related to matching the numeric values. I have data set where in column N have numeric values which can be positive and negative. Need to identify same numeric values e.g. -23 should match with 23. My code which is working fine on small set of data like 1000 rows however most of the time we have data more than 50000 rows or 1,000,000 rows. In large data set my code is freezing the whole system may be beacuse i am using two loops in my cod. Does anyone have any quick solution or anykind of excel formula for sorting my issue on large data.

Here is small scenario: my values 34,45,23,-34,43,34,34,-34. As here, 34 is repeating 5 times so any 4 values should be clear and leave the one as it is.

Thanks
Rajneesh
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
Hi, Rajneesh
1. Can you show us your code?

Here is small scenario: my values 34,45,23,-34,43,34,34,-34. As here, 34 is repeating 5 times so any 4 values should be clear and leave the one as it is.
Click to expand...
2. Which one do you keep? 34 or -34? Or should it be the first one?
 
Upvote 0
Hi Akuini,

Sure , Here is my code and for second point, 34,45,23,-34,43,34,34,-34. It should be like this any positiv 34 can be cleared for any -34 highlightd in bold & italic.

Code: 
    Public wkb As Workbook
    Public sh As Worksheet

Sub Combined_UK_IRE_Amount()

    Dim lrow As Long
    Dim i As Long
    Dim rng As Range

'Set wkb = Workbooks.Open(Application.GetOpenFilename)
Set wkb = ThisWorkbook
Set sh = wkb.Sheets("Data")
lrow = sh.Cells(Rows.Count, 14).End(xlUp).Row

Set rng = sh.Range("N2:N" & lrow)
sh.Activate

With sh
.Range("BU2") = "=ABS(N2))"
.Range("BU2:BU" & lrow).FillDown
.Range("BU2:BU" & lrow).Copy
.Range("BU2:BU" & lrow).PasteSpecial xlPasteValues

sh.Range("BU2:BU" & lrow).AdvancedFilter xlFilterCopy, copytorange:=sh.Range("CA1"), unique:=True

a = 1
Do While .Range("CA" & a) <> ""
.Range("A1:BU1").AutoFilter field:=73, Criteria1:=.Range("CA" & a)
For Each cell In rng

For i = 1 To lrow

If cell.Offset(i, 58) <> "Cleared" Then
If cell.Offset(0, 58) = "Cleared" Then Exit For
If cell.Offset(0, 58) <> "Cleared" Then

aa = cell.Value
End If
If cell.Offset(i, 58) <> "Cleared" Then
bb = cell.Offset(i)
End If
If aa + bb = 0 Then

cell.Offset(0, 58) = "Cleared"
cell.Offset(i, 58) = "Cleared"
Exit For
End If
End If
Next i

Next cell
a = a + 1
Loop
.AutoFilterMode = False


.Columns("CA:CA").Delete
.Range("A1").Select

End With

MsgBox "Done", vbInformation
End Sub
 
Upvote 0
Sure , Here is my code and for second point, 34,45,23,-34,43,34,34,-34. It should be like this any positiv 34 can be cleared for any -34 highlightd in bold & italic.
Click to expand...
Not quite understand the criteria.
So if the data: 34,34,34,34,34,-34,-34
There are five 34 & two -34, then you will keep 34,34,34, & the rest will be deleted,is it right?
 
Upvote 0
OK, try this on a small sample first:


Code: 
[B][COLOR=Royalblue]Sub[/COLOR][/B] a1075525b[B]()[/B]
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1075525-match-positive-numeric-value-negative.html[/COLOR][/I]
 
[B][COLOR=Royalblue]Dim[/COLOR][/B] i [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B][B],[/B] z [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B]
[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]
[B][COLOR=Royalblue]Dim[/COLOR][/B] d [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Object[/COLOR][/B]
 
Application.ScreenUpdating [B]=[/B] [B][COLOR=Royalblue]False[/COLOR][/B]
[B][COLOR=Royalblue]Set[/COLOR][/B] rng [B]=[/B] Range[B]([/B][COLOR=brown]"N1"[/COLOR][B],[/B] Cells[B]([/B]Rows.count[B],[/B] [COLOR=brown]"N"[/COLOR][B]).[/B][B][COLOR=Royalblue]End[/COLOR][/B][B]([/B]xlUp[B]))[/B]
va [B]=[/B] rng
[B][COLOR=Royalblue]Set[/COLOR][/B] d [B]=[/B] CreateObject[B]([/B][COLOR=brown]"scripting.dictionary"[/COLOR][B])[/B]
[B][COLOR=Royalblue]For[/COLOR][/B] i [B]=[/B] [B][COLOR=crimson]2[/COLOR][/B] [B][COLOR=Royalblue]To[/COLOR][/B] UBound[B]([/B]va[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B])[/B]
z [B]=[/B] va[B]([/B]i[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B])[/B]
       
        [B][COLOR=Royalblue]If[/COLOR][/B] d.Exists[B]([/B]z[B])[/B] [B][COLOR=Royalblue]Then[/COLOR][/B]
            d[B]([/B]z[B])[/B] [B]=[/B] d[B]([/B]z[B])[/B] [B]&[/B] [COLOR=brown]","[/COLOR] [B]&[/B] i
        [B][COLOR=Royalblue]ElseIf[/COLOR][/B] d.Exists[B](-[/B]z[B])[/B] [B][COLOR=Royalblue]Then[/COLOR][/B]
            s [B]=[/B] Split[B]([/B]d[B](-[/B]z[B]),[/B] [COLOR=brown]","[/COLOR][B])[/B]
            m [B]=[/B] s[B]([/B]UBound[B]([/B]s[B]))[/B]
            va[B]([/B]i[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B])[/B] [B]=[/B] va[B]([/B]i[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B])[/B] [B]&[/B] [COLOR=brown]"#"[/COLOR]
            va[B]([/B]m[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B])[/B] [B]=[/B] va[B]([/B]m[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B])[/B] [B]&[/B] [COLOR=brown]"#"[/COLOR]
                [B][COLOR=Royalblue]If[/COLOR][/B] UBound[B]([/B]s[B])[/B] [B]=[/B] [B][COLOR=crimson]0[/COLOR][/B] [B][COLOR=Royalblue]Then[/COLOR][/B]
                    d.Remove [B]-[/B]z
                    [B][COLOR=Royalblue]Else[/COLOR][/B]
                    d[B](-[/B]z[B])[/B] [B]=[/B] Left[B]([/B]d[B](-[/B]z[B]),[/B] Len[B]([/B]d[B](-[/B]z[B]))[/B] [B]-[/B] Len[B]([/B]m[B])[/B] [B]-[/B] [B][COLOR=crimson]1[/COLOR][/B][B])[/B]
                [B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]If[/COLOR][/B]
        [B][COLOR=Royalblue]Else[/COLOR][/B]
            d[B]([/B]z[B])[/B] [B]=[/B] i
        [B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]If[/COLOR][/B]
[B][COLOR=Royalblue]Next[/COLOR][/B]
rng [B]=[/B] va
 
Application.ReplaceFormat.Interior.Color [B]=[/B] vbYellow
    rng.Replace What[B]:=[/B][COLOR=brown]"#"[/COLOR][B],[/B] Replacement[B]:=[/B][COLOR=brown]""[/COLOR][B],[/B] _
          LookAt[B]:=[/B]xlPart[B],[/B] SearchOrder[B]:=[/B]xlByRows[B],[/B] MatchCase[B]:=[/B][B][COLOR=Royalblue]True[/COLOR][/B][B],[/B] _
          SearchFormat[B]:=[/B][B][COLOR=Royalblue]False[/COLOR][/B][B],[/B] ReplaceFormat[B]:=[/B][B][COLOR=Royalblue]True[/COLOR][/B]
 
    rng.Replace What[B]:=[/B][COLOR=brown]"#"[/COLOR][B],[/B] Replacement[B]:=[/B][COLOR=brown]""[/COLOR][B],[/B] _
          LookAt[B]:=[/B]xlPart[B],[/B] SearchOrder[B]:=[/B]xlByRows[B],[/B] MatchCase[B]:=[/B][B][COLOR=Royalblue]True[/COLOR][/B][B],[/B] _
          SearchFormat[B]:=[/B][B][COLOR=Royalblue]False[/COLOR][/B][B],[/B] ReplaceFormat[B]:=[/B][B][COLOR=Royalblue]False[/COLOR][/B]
 
Application.ScreenUpdating [B]=[/B] [B][COLOR=Royalblue]True[/COLOR][/B]
[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]
 
Upvote 0
I used this sample to test the code:

Excel 2007 32 bit
[Table="width:, class:head"][tr=bgcolor:#008B8B][th] [/th][th]
N​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
1​
[/td][td]header[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
2​
[/td][td]
3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
3​
[/td][td]
8​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
4​
[/td][td=bgcolor:#FFFF00]
8​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
5​
[/td][td=bgcolor:#FFFF00]
-8​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
6​
[/td][td]
22​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
7​
[/td][td]
22​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
8​
[/td][td=bgcolor:#FFFF00]
22​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
9​
[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
10​
[/td][td]
12​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
11​
[/td][td=bgcolor:#FFFF00]
-22​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
12​
[/td][td]
-6​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
13​
[/td][td=bgcolor:#FFFF00]
-6​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
14​
[/td][td=bgcolor:#FFFF00]
6​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
15​
[/td][td]
7​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
16​
[/td][td]
8​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet2[/td][/tr][/table]
 
Upvote 0
Thanksss a lot Akuini...you solved my problem. I am trying to fix this issue from last 2 weeks.
Its taking just few seconds on large data(100000 row entres) as well.

Thanks a lot..:):):)
 
Upvote 0
Hi Akuini,

Need some help on above code. Supose i have few vendor names in column M and i filter the vendor name and want to do matching without sorting the data.

Please help.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Counting Number of Consecutive Negative/Positive Numbers in a Row
Replies
6
Views
492
Cubist
Cubist
C
  • Question Question
Subtotal positive and negative values on visible rows
Replies
6
Views
257
Peter_SSs
Peter_SSs
R
  • Question Question
Matching positive and negative values
2
Replies
14
Views
2K
Akuini
Akuini
P
  • Question Question
Formula to locate and remove outlying values from a large data set
Replies
3
Views
178
hagia_sofia
hagia_sofia
W
  • Solved
VBA - Match two values from Table1 to two in Table2 and move/align
2
Replies
15
Views
377
Welshy1491
W

Forum statistics

Threads
1,224,823
Messages
6,181,183
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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