# Same Positive & Negative Quantities will cancel each other but it will based on SKU

#### srehman

##### Board Regular
Hi Team,

As i am checking Positive & Negative quantities should be cancelled if( +10/-10) or zero which is worked fine based only on qty level.

Now condition is based on SKU , Positive & Negative will match on same SKU not any.

Like SKU A qty 10 in line 3 will cancel SKU A qty -10 in line 7.

But not in Line 8 & 11 SKUs are different

Positive Negative Match.xlsx
ABCDEFGH
2SKUQtyCount Positive qtyCount Negative qty C > DCombine C & D Condition as reqQty Checked by SKU Only same SKU QTY
3A1011FALSEFALSEZERO
4B2011FALSEFALSEZERO
5C3011FALSEFALSEZERO
6D4010TRUETRUENO
7A-1011FALSEFALSEZERO
8B-4511FALSEFALSEZERO
9C9010TRUETRUENO
10D-2011FALSEFALSEZERO
11A4511FALSEFALSEZERO
12B12010TRUETRUENO
13C-3011FALSEFALSEZERO
14D12020TRUETRUENO
Sheet1
Cell Formulas
RangeFormula
C3:C14C3=COUNTIF(\$B\$3:B3,B3)
D3:D14D3=COUNTIF(\$B\$3:B14,-B3)
E3:E14E3=C3>D3
F3:F14F3=COUNTIF(\$B\$3:B3,B3)>COUNTIF(\$B\$3:B12,-B3)
G3:G14G3=IF(COUNTIF(\$B\$3:B3,B3)>COUNTIF(\$B\$3:B12,-B3),"NO","ZERO")

#### Attachments

• Positive Negatve of same SKU should be cancel.PNG
25.9 KB · Views: 1

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

#### Fluff

##### MrExcel MVP, Moderator
=COUNTIFS(A:A,A2,B:B,-B2)>0

#### srehman

##### Board Regular

I tried but no luck, basically i aim is for reconciliation of Same SKU Positive & Negative qty.
Line 3 & Line 7 should be reconcile , While Line 8 & Line 11 will not because 2 different SKUs.

I am trying as well

Positive Negative Match.xlsx
ABCDEFGH
2SKUQtyCount Positive qtyCount Negative qty C > DCombine C & D Condition as reqQty Checked by SKU Only same SKU QTY
3A1011FALSEFALSEZEROFALSE
4B2012FALSEFALSEZEROTRUE
5C3011FALSEFALSEZEROTRUE
6D4010TRUETRUENOTRUE
7A-1011FALSEFALSEZEROFALSE
8B-4511FALSEFALSEZEROTRUE
9C9010TRUETRUENOFALSE
10D-2011FALSEFALSEZEROFALSE
11A4511FALSEFALSEZEROFALSE
12B-2021TRUETRUENOFALSE
13C-3011FALSEFALSEZEROTRUE
14D12010TRUETRUENOTRUE
Sheet1
Cell Formulas
RangeFormula
C3:C14C3=COUNTIF(\$B\$3:B3,B3)
D3:D14D3=COUNTIF(\$B\$3:B14,-B3)
E3:E14E3=C3>D3
F3:F14F3=COUNTIF(\$B\$3:B3,B3)>COUNTIF(\$B\$3:B12,-B3)
G3:G14G3=IF(COUNTIF(\$B\$3:B3,B3)>COUNTIF(\$B\$3:B12,-B3),"NO","ZERO")
H3:H14H3=COUNTIFS(A:A,A2,B:B,-B2)>0
Named Ranges
NameRefers ToCells
_FilterDatabase=Sheet1!\$A\$2:\$H\$14H3:H14

#### srehman

##### Board Regular
yes it is working Fluff my fault i need to change A3 & B3 many thanks for your support

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
3
Views
158
Replies
4
Views
110
Replies
6
Views
126
Replies
3
Views
133
Replies
20
Views
303

1,127,070
Messages
5,622,512
Members
415,904
Latest member
jchi2210

### 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.

### Which adblocker are you using?

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

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