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

srehman

Board Regular
Joined
Jan 4, 2020
Messages
210
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: 2

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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
 
Upvote 0
yes it is working Fluff my fault i need to change A3 & B3 many thanks for your support
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

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