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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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
435
Cubist
Cubist
C
  • Question Question
Subtotal positive and negative values on visible rows
Replies
6
Views
158
Peter_SSs
Peter_SSs
R
  • Question Question
Matching positive and negative values
2
Replies
14
Views
1K
Akuini
Akuini
P
  • Question Question
Formula to locate and remove outlying values from a large data set
Replies
3
Views
170
hagia_sofia
hagia_sofia
A
  • Question Question
Index match match not using second set of match criteria
Replies
3
Views
76
Fluff
Fluff

Forum statistics

Threads
1,223,911
Messages
6,175,325
Members
452,635
Latest member
laura12345

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