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

srehman

Board Regular
Joined
Jan 4, 2020
Messages
189
Office Version
  1. 2016
Platform
  1. Windows
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
    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
Joined
Jun 12, 2014
Messages
56,614
Office Version
  1. 365
Platform
  1. Windows
How about
=COUNTIFS(A:A,A2,B:B,-B2)>0
 

srehman

Board Regular
Joined
Jan 4, 2020
Messages
189
Office Version
  1. 2016
Platform
  1. Windows
Hi thanks for your comments.

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
Joined
Jan 4, 2020
Messages
189
Office Version
  1. 2016
Platform
  1. Windows
yes it is working Fluff my fault i need to change A3 & B3 many thanks for your support
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,614
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

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

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
Top