Macro or Formula to match positive and negative numbers

[Madzark]

Dear community,

I need to match up a negative number with a positive number that would make the value zero. I tried to use the COUNTIF formula (e.g. =COUNTIF($Q$2:$Q$30210;Q2)<>COUNTIF($Q$2:$Q$30210;-Q2)) but it doesn't work at all.

Any suggestions? I would like to run a macro, because i have files with over 30.000 rows and filters are running late.

Thank you very much!

 

[Akuini]

What should happen when you find positive and negative numbers that match?
Could you post an example?

 

[Madzark]

The macro should eliminate the rows it has identified as perfect pairs of positives and negatives or , at least, assign a value that allows me to filter the results

 

[Akuini]

Try this:
Data at column Q, the result in col R, the ones with "xx" is positive and negative numbers that match.

[FONT=lucida console][color=Royalblue]Sub[/color] positive_negative_match1c()
[i][color=seagreen]'use this[/color][/i]
[i][color=seagreen]'positive-negative match pair[/color][/i]
[i][color=seagreen]'without grouping[/color][/i]

[color=Royalblue]Dim[/color] i [color=Royalblue]As[/color] [color=Royalblue]Long[/color]
[color=Royalblue]Dim[/color] va, z, q, ary1, ary2
[color=Royalblue]Dim[/color] d [color=Royalblue]As[/color] [color=Royalblue]Object[/color]
 

[i][color=seagreen]'data start at Q2[/color][/i]
va = Range([color=brown]"Q2"[/color], Cells(Rows.count, [color=brown]"Q"[/color]).[color=Royalblue]End[/color](xlUp))

[color=Royalblue]Set[/color] d = CreateObject([color=brown]"scripting.dictionary"[/color])

[color=Royalblue]For[/color] i = [color=crimson]1[/color] [color=Royalblue]To[/color] UBound(va, [color=crimson]1[/color])
    z = va(i, [color=crimson]1[/color]):  d(z) = d(z) & [color=brown]","[/color] & i
[color=Royalblue]Next[/color]


[color=Royalblue]For[/color] [color=Royalblue]Each[/color] q [color=Royalblue]In[/color] d.Keys
    [color=Royalblue]If[/color] q > [color=crimson]0[/color] [color=Royalblue]And[/color] d.Exists(-q) [color=Royalblue]Then[/color]
        ary1 = Split(d(q), [color=brown]","[/color]):  ary2 = Split(d(-q), [color=brown]","[/color])
    
        [color=Royalblue]If[/color] UBound(ary1) < UBound(ary2) [color=Royalblue]Then[/color] P = UBound(ary1) [color=Royalblue]Else[/color] P = UBound(ary2)
            [color=Royalblue]For[/color] i = [color=crimson]1[/color] [color=Royalblue]To[/color] P
                va(ary1(i), [color=crimson]1[/color]) = [color=brown]"xx"[/color]: va(ary2(i), [color=crimson]1[/color]) = [color=brown]"xx"[/color]
            [color=Royalblue]Next[/color]
    [color=Royalblue]End[/color] [color=Royalblue]If[/color]
[color=Royalblue]Next[/color]

[i][color=seagreen]'put the result in col R[/color][/i]
Range([color=brown]"R2"[/color]).Resize(UBound(va, [color=crimson]1[/color]), [color=crimson]1[/color]) = va

[color=Royalblue]End[/color] [color=Royalblue]Sub[/color][/FONT]

Excel 2013
	Q	R
1	No
2	2	xx
3	3	xx
4	4	xx
5	5	5
6	6	6
7	7	xx
8	8	8
9	9	9
10	10	10
11	1	xx
12	2	2
13	3	3
14	4	4
15	5	5
16	6	6
17	7	7
18	8	8
19	9	9
20	10	10
21	-1	xx
22	-2	xx
23	-3	xx
24	-4	xx
25	-7	xx
Sheet2

Edited:
wrong code:
va = Range("Q2", Cells(Rows.count, "A").End(xlUp))

 

[Madzark]

It works perfectly! Thanks a lot mate

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.