abdo meghari
Well-known Member
- Joined
- Aug 3, 2021
- Messages
- 573
- Office Version
- 2019
Hi
I want comparing the items in column B for sheet STOCK with columns B:D for sheet REPORT and subtract the values in column C for sheet STOCK from column G for sheet REPORT . if the values in column G for sheet REPORT bigger than values where is existed in column C for sheet STOCK , then should be plus as in column PLUS and should mark wrong as in column MARK , if the values in column G for sheet REPORT smaller, than values where is existed in column C for sheet STOCK , then should be deficit as in column DEFICIT and should mark wrong as in column MARK , if the values in column G for sheet REPORT are equal values where is existed in column C for sheet STOCK then should be hyphen (-) for both columns DEFICIT & PLUS and mark right as in column MARK.finally if there are new items in sheet STOCK but not is existed at all in sheet REPORT , then should be as deficit & highlighted by red and mark wrong as in column MARK .
I put the expected result should be from column F: G in sheet STOCK
result
attached picture because the XL2BB doesn't allow shows mark right & wrong
also posted here and no answering so far
compare between two sheets and shows difference values
I hope finding here the solution .
thanks
I want comparing the items in column B for sheet STOCK with columns B:D for sheet REPORT and subtract the values in column C for sheet STOCK from column G for sheet REPORT . if the values in column G for sheet REPORT bigger than values where is existed in column C for sheet STOCK , then should be plus as in column PLUS and should mark wrong as in column MARK , if the values in column G for sheet REPORT smaller, than values where is existed in column C for sheet STOCK , then should be deficit as in column DEFICIT and should mark wrong as in column MARK , if the values in column G for sheet REPORT are equal values where is existed in column C for sheet STOCK then should be hyphen (-) for both columns DEFICIT & PLUS and mark right as in column MARK.finally if there are new items in sheet STOCK but not is existed at all in sheet REPORT , then should be as deficit & highlighted by red and mark wrong as in column MARK .
I put the expected result should be from column F: G in sheet STOCK
report.xlsm | |||||
---|---|---|---|---|---|
A | B | C | |||
1 | ITEM | BRAND | QTY | ||
2 | 1 | BS 315/80R22 R184 THI | 10 | ||
3 | 2 | BS195R15C R623 THI | 300 | ||
4 | 3 | BS195R15C R623 JAP | 500 | ||
5 | 4 | BS 315/80R22 R184 JAP | 100 | ||
6 | 5 | BS 1200R20 G580 THI | - | ||
7 | 6 | BS 1200R24 G580 THI | 200 | ||
8 | 7 | BS 1400R20 18PR VSJ JAP | - | ||
9 | 8 | BS 1200R20 G580 JAP | 120 | ||
10 | 9 | BS 315/80R22 R152 JAP | - | ||
11 | 10 | BS 315/80R22 R152 THI | - | ||
12 | 11 | BS1400R20 R180 JAP | 20 | ||
13 | 12 | BS1400R20 R180BZ JAP | 10 | ||
14 | 13 | BS11R22.5 R187 JAP | 22 | ||
15 | 14 | BS 12R22.5 R187 JAP | 5 | ||
STOCK |
report.xlsm | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | ITEM | BRAND | TYPE | TYPE | ARRIVE | SALE | BALANCE | ||
2 | 2 | BS 1200R20 | G580 | JAP | 130 | 10 | 120 | ||
3 | 3 | BS 1200R20 | G580 | THI | - | - | 0 | ||
4 | 4 | BS 1200R24 | G580 | THI | 190 | 190 | |||
5 | 5 | BS 1400R20 18PR | VSJ | JAP | 12 | 2 | 10 | ||
6 | 6 | BS195R15C | R623 | THI | 400 | 100 | 300 | ||
7 | 7 | BS195R15C | R623 | JAP | 510 | 5 | 505 | ||
8 | 8 | 315/80R22 | R184 | JAP | 100 | 2 | 98 | ||
9 | 9 | 315/80R22 | R184 | THI | - | - | 0 | ||
10 | 10 | 315/80R22 | R152 | JAP | - | - | 0 | ||
REPORT |
Cell Formulas | ||
---|---|---|
Range | Formula | |
G2:G10 | G2 | =E2-F2 |
result
report.xlsm | |||||||
---|---|---|---|---|---|---|---|
F | G | H | I | J | |||
1 | ITEM | MARK | BRAND | DEFICIT | PLUS | ||
2 | 1 | O | BS 315/80R22 R184 THI | -10 | - | ||
3 | 2 | P | BS195R15C R623 THI | - | - | ||
4 | 3 | O | BS195R15C R623 JAP | - | 5 | ||
5 | 4 | O | BS 315/80R22 R184 JAP | -2 | - | ||
6 | 5 | P | BS 1200R20 G580 THI | - | - | ||
7 | 6 | O | BS 1200R24 G580 THI | -10 | - | ||
8 | 7 | O | BS 1400R20 18PR VSJ JAP | - | 10 | ||
9 | 8 | P | BS 1200R20 G580 JAP | - | - | ||
10 | 9 | P | BS 315/80R22 R152 JAP | - | - | ||
11 | 10 | P | BS 315/80R22 R152 THI | - | - | ||
12 | 11 | O | BS1400R20 R180 JAP | -20 | - | ||
13 | 12 | O | BS1400R20 R180BZ JAP | -10 | - | ||
14 | 13 | O | BS11R22.5 R187 JAP | -22 | - | ||
15 | 14 | O | BS 12R22.5 R187 JAP | -5 | - | ||
STOCK |
attached picture because the XL2BB doesn't allow shows mark right & wrong
also posted here and no answering so far
compare between two sheets and shows difference values
I hope finding here the solution .
thanks