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
 
Sort by date Sort by votes
Rajneesh Rawat said:
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.
Click to expand...

Sorry, I don't quite understand what you mean.
1. Do you mean the table has already filtered or you want the macro to filter it?
2. The filter criteria, is it only by one vendor name?
3. By 'matching' you mean highlight the cells using the same criteria as the above code?

Can you post some sample to describe what you mean?
 
Upvote 0

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Hi Akuini,

Apologies. Here is the sample data, there are multiple vendor names in one columns and I need to filter one by one on each vendor names and match the numeric values only for that particular vendor.
For example first I filter the vendor XXX and match the value then YYY & ZZZ. I tried to amend the code which u have provided but m not able to fix this issue.

Please let me know if you want more example.

Vendor Name Amount
XXX -15
XXX -17
ZZZ 11
ZZZ 6
YYY 8
YYY -8
ZZZ -11
ZZZ -8
ZZZ -19
XXX -6
XXX 15
ZZZ -17
ZZZ 17
YYY 7
YYY -1
XXX 17
XXX -9
ZZZ -7
ZZZ -17
YYY 7
YYY -1
 
Upvote 0
Rajneesh Rawat said:
Hi Akuini,

Apologies. Here is the sample data, there are multiple vendor names in one columns and I need to filter one by one on each vendor names and match the numeric values only for that particular vendor.
For example first I filter the vendor XXX and match the value then YYY & ZZZ. I tried to amend the code which u have provided but m not able to fix this issue.
Click to expand...

Ok, try this:

Code: 
[B][color=Royalblue]Sub[/color][/B] a1075525c()
[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], 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], vb [B][color=Royalblue]As[/color][/B] [B][color=Royalblue]Variant[/color][/B], vc [B][color=Royalblue]As[/color][/B] [B][color=Royalblue]Variant[/color][/B], Q [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], da [B][color=Royalblue]As[/color][/B] [B][color=Royalblue]Object[/color][/B]

Application.ScreenUpdating = [B][color=Royalblue]False[/color][/B]
vb = Range([color=brown]"M1:N"[/color] & Cells(Rows.count, [color=brown]"M"[/color]).[B][color=Royalblue]End[/color][/B](xlUp).row)
vc = Range([color=brown]"N1:N"[/color] & Cells(Rows.count, [color=brown]"M"[/color]).[B][color=Royalblue]End[/color][/B](xlUp).row)

[B][color=Royalblue]Set[/color][/B] da = CreateObject([color=brown]"scripting.dictionary"[/color])
    [B][color=Royalblue]For[/color][/B] i = [color=crimson]2[/color] [B][color=Royalblue]To[/color][/B] UBound(vb, [color=crimson]1[/color])
        da(vb(i, [color=crimson]1[/color])) = [color=brown]""[/color]
    [B][color=Royalblue]Next[/color][/B]
    
[B][color=Royalblue]For[/color][/B] [B][color=Royalblue]Each[/color][/B] Q [B][color=Royalblue]In[/color][/B] da.Keys
    
    [B][color=Royalblue]ReDim[/color][/B] va([color=crimson]1[/color] [B][color=Royalblue]To[/color][/B] UBound(vb, [color=crimson]1[/color]), [color=crimson]1[/color] [B][color=Royalblue]To[/color][/B] [color=crimson]1[/color])
    
    [B][color=Royalblue]For[/color][/B] i = [color=crimson]2[/color] [B][color=Royalblue]To[/color][/B] UBound(vb, [color=crimson]1[/color])
        [B][color=Royalblue]If[/color][/B] vb(i, [color=crimson]1[/color]) = Q [B][color=Royalblue]Then[/color][/B] va(i, [color=crimson]1[/color]) = vb(i, [color=crimson]2[/color])
    [B][color=Royalblue]Next[/color][/B]
       
       [B][color=Royalblue]Set[/color][/B] d = CreateObject([color=brown]"scripting.dictionary"[/color])

        [B][color=Royalblue]For[/color][/B] i = [color=crimson]2[/color] [B][color=Royalblue]To[/color][/B] UBound(va, [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]
       
    [B][color=Royalblue]For[/color][/B] i = [color=crimson]2[/color] [B][color=Royalblue]To[/color][/B] UBound(va, [color=crimson]1[/color])
        [B][color=Royalblue]If[/color][/B] InStr([color=crimson]1[/color], va(i, [color=crimson]1[/color]), [color=brown]"#"[/color]) [B][color=Royalblue]Then[/color][/B] vc(i, [color=crimson]1[/color]) = va(i, [color=crimson]1[/color])
    [B][color=Royalblue]Next[/color][/B]

[B][color=Royalblue]Next[/color][/B] Q

[B][color=Royalblue]Set[/color][/B] rng = Range([color=brown]"N1"[/color]).Resize(UBound(vc, [color=crimson]1[/color]), [color=crimson]1[/color])
rng = vc
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.ScreenUpdating = [B][color=Royalblue]True[/color][/B]
[B][color=Royalblue]End[/color][/B] [B][color=Royalblue]Sub[/color][/B]

RESULT:

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
2​
[/td][td]XXX[/td][td=bgcolor:#FFFF00]
-15​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
3​
[/td][td]XXX[/td][td=bgcolor:#FFFF00]
-17​
[/td][/tr]

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

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

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

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

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
9​
[/td][td]ZZZ[/td][td]
-8​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
10​
[/td][td]ZZZ[/td][td]
-19​
[/td][/tr]

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
12​
[/td][td]XXX[/td][td=bgcolor:#FFFF00]
15​
[/td][/tr]

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

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

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
16​
[/td][td]YYY[/td][td]
-1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
17​
[/td][td]XXX[/td][td=bgcolor:#FFFF00]
17​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
18​
[/td][td]XXX[/td][td]
-9​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
19​
[/td][td]ZZZ[/td][td]
-7​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
20​
[/td][td]ZZZ[/td][td]
-17​
[/td][/tr]

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
22​
[/td][td]YYY[/td][td]
-1​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet2[/td][/tr][/table]

And if you filter by XXX:

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
2​
[/td][td]XXX[/td][td=bgcolor:#FFFF00]
-15​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
3​
[/td][td]XXX[/td][td=bgcolor:#FFFF00]
-17​
[/td][/tr]

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
12​
[/td][td]XXX[/td][td=bgcolor:#FFFF00]
15​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
17​
[/td][td]XXX[/td][td=bgcolor:#FFFF00]
17​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]
18​
[/td][td]XXX[/td][td]
-9​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet2[/td][/tr][/table]
 
Upvote 0
Is it possible that your real data in column 'vendor name' has trailing space or inconsistent letter case like "Google" and "google".
If yes then we need to modify the code to handle that problem.
 
Upvote 0
Thanks Akuini for the code. It is very helpful for me.

I have two questions here: (1) If in my vendor list, there is some blank values in vendor columns but amount is there in amount columns. In this case, code should skip these values where we have no vendor names for those amounts.

(2) Vendor columns can be in AZ or W columns however amount column remain the same in column N. What are the changes needs to be done in this code if my vendor columns change.

regards
Rajneesh
 
Upvote 0
Got it..I have made the amendments in the code..thats working fine. Its taking apprx 15 minutes in 100000 rows data and thats fine for me.

I really appreciate your efforts and help sir...you are a genius coder...:):):)
 
Upvote 0
Rajneesh Rawat said:
Got it..I have made the amendments in the code..thats working fine. Its taking apprx 15 minutes in 100000 rows data and thats fine for me.

I really appreciate your efforts and help sir...you are a genius coder...
Click to expand...

Ok, glad it works for you.
But 15 minutes, hm.., I guess the vendors are plenty enough.
Actually if it's ok to sort the data by vendor I think I can modify the code to make it faster. But you said earlier that you don't want to sort the data. Can I ask why you can't sort the data?
 
Last edited:
Upvote 0
Rajneesh Rawat said:
I think sorting on large data will take time otherwise there is no prob to sort the data if it can be work more faster.
Click to expand...

Ok, try this:
I use 3 helper column (F:H), the sort will only be done in this helper column, so it should be fast enough. The helper column will be deleted in the end of the code.

The code also will ignore the row if vendor is blank

Col A is vendor, col B is number, and 3 helper column is F:H, to change these columns you just need to change this part:

Code: 
cv = "A" 'vendor column
cn = "B" 'number column
ch = 6 'the first column of the helper column, so if the helper column is X:Z then ch = 24

Here's the code:

Code: 
[B][color=Royalblue]Sub[/color][/B] a1075525f()
[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], z [B][color=Royalblue]As[/color][/B] [B][color=Royalblue]Long[/color][/B], ch [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]
[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], cv [B][color=Royalblue]As[/color][/B] [B][color=Royalblue]String[/color][/B], cn [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], vb [B][color=Royalblue]As[/color][/B] [B][color=Royalblue]Variant[/color][/B], vc [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]
[B][color=Royalblue]Dim[/color][/B] d [B][color=Royalblue]As[/color][/B] [B][color=Royalblue]Object[/color][/B], da [B][color=Royalblue]As[/color][/B] [B][color=Royalblue]Object[/color][/B]

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

cv = [color=brown]"A"[/color] [i][color=seagreen]'vendor column[/color][/i]
cn = [color=brown]"B"[/color] [i][color=seagreen]'number column[/color][/i]
ch = [color=crimson]6[/color] [i][color=seagreen]'the first column of the helper column, so if the helper column is X:Z then ch = 24[/color][/i]


rr = Range(cv & Rows.count).[B][color=Royalblue]End[/color][/B](xlUp).row
Range(Cells([color=crimson]1[/color], ch + [color=crimson]1[/color]), Cells(rr, ch + [color=crimson]1[/color])).Value = Range(Cells([color=crimson]1[/color], cv), Cells(rr, cv)).Value

Range(Cells([color=crimson]1[/color], ch + [color=crimson]2[/color]), Cells(rr, ch + [color=crimson]2[/color])).Value = Range(Cells([color=crimson]1[/color], cn), Cells(rr, cn)).Value
Cells([color=crimson]1[/color], ch).Value = [color=crimson]1[/color]
Cells([color=crimson]1[/color], ch).Resize(rr, [color=crimson]1[/color]).DataSeries

Range(Cells([color=crimson]1[/color], ch), Cells(rr, ch + [color=crimson]2[/color])).Sort key1:=Cells([color=crimson]1[/color], ch + [color=crimson]1[/color]), order1:=xlAscending, Header:=xlYes
vx = Range(Cells([color=crimson]1[/color], ch + [color=crimson]1[/color]), Cells(rr + [color=crimson]1[/color], ch + [color=crimson]1[/color])).Value
    
[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]If[/color][/B] vx(f, [color=crimson]1[/color]) = [color=brown]""[/color] [B][color=Royalblue]Then[/color][/B] [B][color=Royalblue]GoTo[/color][/B] [B][color=Royalblue]skip[/color][/B]
    
    [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, ch + [color=crimson]2[/color]), Cells(f, ch + [color=crimson]2[/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
        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(Cells([color=crimson]1[/color], ch + [color=crimson]2[/color]), Cells(rr, ch + [color=crimson]2[/color]))
 
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]
     
Range(Cells([color=crimson]1[/color], ch), Cells(rr, ch + [color=crimson]2[/color])).Sort key1:=Cells([color=crimson]1[/color], ch), order1:=xlAscending, Header:=xlYes
Range(Cells([color=crimson]2[/color], ch + [color=crimson]2[/color]), Cells(rr, ch + [color=crimson]2[/color])).Copy
Cells([color=crimson]2[/color], cn).PasteSpecial paste:=xlPasteFormats
[i][color=seagreen]'deleting helper column[/color][/i]
Range(Cells([color=crimson]1[/color], ch), Cells([color=crimson]1[/color], ch + [color=crimson]2[/color])).EntireColumn.Delete

Application.ScreenUpdating = [B][color=Royalblue]True[/color][/B]
[B][color=Royalblue]End[/color][/B] [B][color=Royalblue]Sub[/color][/B]
 
Last edited:
Upvote 0
THE RESULT:
the helper column F:H will be deleted in the end of the code.

Excel 2007 32 bit
[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][th]
D​
[/th][th]
E​
[/th][th]
F​
[/th][th]
G​
[/th][th]
H​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
1​
[/td][td=bgcolor:#E6B9B8]VENDOR[/td][td=bgcolor:#E6B9B8]NUMBER[/td][td][/td][td][/td][td][/td][td]
1​
[/td][td]VENDOR[/td][td]NUMBER[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
2​
[/td][td=bgcolor:#FFFFFF]XXX[/td][td=bgcolor:#FFFF00]
-15​
[/td][td][/td][td=bgcolor:#FFFFFF][/td][td]
​
[/td][td]
2​
[/td][td]XXX[/td][td=bgcolor:#FFFF00]-15#[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
3​
[/td][td=bgcolor:#FFFFFF]XXX[/td][td=bgcolor:#FFFF00]
-17​
[/td][td][/td][td=bgcolor:#FFFFFF][/td][td]
​
[/td][td]
3​
[/td][td]XXX[/td][td=bgcolor:#FFFF00]-17#[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
4​
[/td][td=bgcolor:#FFFFFF]ZZZ[/td][td=bgcolor:#FFFF00]
11​
[/td][td][/td][td=bgcolor:#FFFFFF][/td][td]
​
[/td][td]
4​
[/td][td]ZZZ[/td][td=bgcolor:#FFFF00]11#[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
5​
[/td][td=bgcolor:#FFFFFF]ZZZ[/td][td]
6​
[/td][td][/td][td=bgcolor:#FFFFFF][/td][td]
​
[/td][td]
5​
[/td][td]ZZZ[/td][td]
6​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
6​
[/td][td=bgcolor:#FFFFFF][/td][td]
8​
[/td][td][/td][td=bgcolor:#FFFFFF][/td][td]
​
[/td][td]
6​
[/td][td][/td][td]
8​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
7​
[/td][td=bgcolor:#FFFFFF]YYY[/td][td]
-8​
[/td][td][/td][td=bgcolor:#FFFFFF][/td][td]
​
[/td][td]
7​
[/td][td]YYY[/td][td]
-8​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
8​
[/td][td=bgcolor:#FFFFFF]ZZZ[/td][td=bgcolor:#FFFF00]
-11​
[/td][td][/td][td=bgcolor:#FFFFFF][/td][td]
​
[/td][td]
8​
[/td][td]ZZZ[/td][td=bgcolor:#FFFF00]-11#[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
9​
[/td][td=bgcolor:#FFFFFF]ZZZ[/td][td]
-8​
[/td][td][/td][td=bgcolor:#FFFFFF][/td][td]
​
[/td][td]
9​
[/td][td]ZZZ[/td][td]
-8​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
10​
[/td][td=bgcolor:#FFFFFF]ZZZ[/td][td]
-19​
[/td][td][/td][td=bgcolor:#FFFFFF][/td][td]
​
[/td][td]
10​
[/td][td]ZZZ[/td][td]
-19​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
11​
[/td][td=bgcolor:#FFFFFF]XXX[/td][td]
-6​
[/td][td][/td][td=bgcolor:#FFFFFF][/td][td]
​
[/td][td]
11​
[/td][td]XXX[/td][td]
-6​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
12​
[/td][td=bgcolor:#FFFFFF]XXX[/td][td=bgcolor:#FFFF00]
15​
[/td][td][/td][td=bgcolor:#FFFFFF][/td][td]
​
[/td][td]
12​
[/td][td]XXX[/td][td=bgcolor:#FFFF00]15#[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
13​
[/td][td=bgcolor:#FFFFFF]ZZZ[/td][td=bgcolor:#FFFF00]
-17​
[/td][td][/td][td=bgcolor:#FFFFFF][/td][td]
​
[/td][td]
13​
[/td][td]ZZZ[/td][td=bgcolor:#FFFF00]-17#[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
14​
[/td][td=bgcolor:#FFFFFF]ZZZ[/td][td=bgcolor:#FFFF00]
17​
[/td][td][/td][td=bgcolor:#FFFFFF][/td][td]
​
[/td][td]
14​
[/td][td]ZZZ[/td][td=bgcolor:#FFFF00]17#[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
15​
[/td][td=bgcolor:#FFFFFF]YYY[/td][td]
7​
[/td][td][/td][td=bgcolor:#FFFFFF][/td][td]
​
[/td][td]
15​
[/td][td]YYY[/td][td]
7​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
16​
[/td][td=bgcolor:#FFFFFF]YYY[/td][td]
-1​
[/td][td][/td][td=bgcolor:#FFFFFF][/td][td]
​
[/td][td]
16​
[/td][td]YYY[/td][td]
-1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
17​
[/td][td=bgcolor:#FFFFFF]XXX[/td][td=bgcolor:#FFFF00]
17​
[/td][td][/td][td=bgcolor:#FFFFFF][/td][td]
​
[/td][td]
17​
[/td][td]XXX[/td][td=bgcolor:#FFFF00]17#[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
18​
[/td][td=bgcolor:#FFFFFF]XXX[/td][td]
-9​
[/td][td][/td][td=bgcolor:#FFFFFF][/td][td]
​
[/td][td]
18​
[/td][td]XXX[/td][td]
-9​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
19​
[/td][td=bgcolor:#FFFFFF]ZZZ[/td][td]
-7​
[/td][td][/td][td=bgcolor:#FFFFFF][/td][td]
​
[/td][td]
19​
[/td][td]ZZZ[/td][td]
-7​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
20​
[/td][td=bgcolor:#FFFFFF]ZZZ[/td][td]
-17​
[/td][td][/td][td=bgcolor:#FFFFFF][/td][td]
​
[/td][td]
20​
[/td][td]ZZZ[/td][td]
-17​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
21​
[/td][td=bgcolor:#FFFFFF]YYY[/td][td]
7​
[/td][td][/td][td=bgcolor:#FFFFFF][/td][td]
​
[/td][td]
21​
[/td][td]YYY[/td][td]
7​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
22​
[/td][td=bgcolor:#FFFFFF]YYY[/td][td]
-1​
[/td][td][/td][td=bgcolor:#FFFFFF][/td][td]
​
[/td][td]
22​
[/td][td]YYY[/td][td]
-1​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet4[/td][/tr][/table]
 
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