# 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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

#### 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
165
Replies
4
Views
119
Replies
6
Views
147
Replies
3
Views
190
Replies
20
Views
343

1,129,690
Messages
5,637,841
Members
416,985
Latest member
mrindira

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

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