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
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 | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
2 | SKU | Qty | Count Positive qty | Count Negative qty | C > D | Combine C & D | Condition as req | Qty Checked by SKU Only same SKU QTY | ||
3 | A | 10 | 1 | 1 | FALSE | FALSE | ZERO | |||
4 | B | 20 | 1 | 1 | FALSE | FALSE | ZERO | |||
5 | C | 30 | 1 | 1 | FALSE | FALSE | ZERO | |||
6 | D | 40 | 1 | 0 | TRUE | TRUE | NO | |||
7 | A | -10 | 1 | 1 | FALSE | FALSE | ZERO | |||
8 | B | -45 | 1 | 1 | FALSE | FALSE | ZERO | |||
9 | C | 90 | 1 | 0 | TRUE | TRUE | NO | |||
10 | D | -20 | 1 | 1 | FALSE | FALSE | ZERO | |||
11 | A | 45 | 1 | 1 | FALSE | FALSE | ZERO | |||
12 | B | 120 | 1 | 0 | TRUE | TRUE | NO | |||
13 | C | -30 | 1 | 1 | FALSE | FALSE | ZERO | |||
14 | D | 120 | 2 | 0 | TRUE | TRUE | NO | |||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C3:C14 | C3 | =COUNTIF($B$3:B3,B3) |
D3:D14 | D3 | =COUNTIF($B$3:B14,-B3) |
E3:E14 | E3 | =C3>D3 |
F3:F14 | F3 | =COUNTIF($B$3:B3,B3)>COUNTIF($B$3:B12,-B3) |
G3:G14 | G3 | =IF(COUNTIF($B$3:B3,B3)>COUNTIF($B$3:B12,-B3),"NO","ZERO") |