Hello,
I have a table in which the result from the other column is shown here (column I).
Actually there is supposed to be only one of the 4 columns (M to P) that show the result. But I'm just worried that there'll be 2 of the columns show a value in it.
If that's the case, the formula that I'm using won't be able to show the result from both columns.
Have anyone any idea what kind of function I can use in the situation like this?
Thank you in advance.
I have a table in which the result from the other column is shown here (column I).
Actually there is supposed to be only one of the 4 columns (M to P) that show the result. But I'm just worried that there'll be 2 of the columns show a value in it.
If that's the case, the formula that I'm using won't be able to show the result from both columns.
Have anyone any idea what kind of function I can use in the situation like this?
Thank you in advance.
| check.xlsx | ||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | |||
| 1 | Invoice No | Customer code | Customer name | Customer address | Qty | Total weight | warehouse+code (manual) | warehouse+code (system) | Check | PIC | Cond. 1 | Cond. 2 | Cond. 3 | Cond. 4 | ||||
| 2 | 866771 total | SBD03031 | A | AAA | 2 | 76.5 | 40 | 43 | check | John | check | delivery code check | ||||||
| 3 | 866772 total | SBD01T78 | B | BBB | 5 | 94.4 | 40 | 40 | John | |||||||||
| 4 | 866773 total | ASG02007 | C | CCC | 2 | 32.3 | 43 | 43 | John | |||||||||
| 5 | 866774 total | TNS07030 | D | DDD | 1 | 18.3 | 43 | 43 | John | |||||||||
| 6 | 866775 total | TNS07038 | E | EEE | 4 | 66.7 | 40 | 40 | John | |||||||||
| 7 | 866776 total | TNS07035 | F | FFF | 2 | 36.6 | 43 | 43 | John | |||||||||
| 8 | 866777 total | NPK01072 | G | GGG | 1 | 16.2 | 43 | 43 | John | |||||||||
| 9 | 866778 total | SBD01ABM | H | HHH | 6 | 87.3 | 40 | 40 | John | |||||||||
| 10 | 866779 total | SBD01Q49 | I | III | 12 | 145.0 | 40 | 40 | John | |||||||||
| 11 | 866780 total | OZX01305 | J | JJJ | 4 | 59.3 | 40 | 40 | John | |||||||||
| 12 | 866781 total | OZX01034 | K | KKK | 1 | 14.8 | 43 | 43 | John | |||||||||
| 13 | 866782 total | OZX01042 | L | LLL | 5 | 74.9 | 40 | 40 | John | |||||||||
| 14 | 866783 total | OZX01120 | M | MMM | 2 | 29.8 | 43 | 43 | John | |||||||||
| 15 | 866784 total | OZX01254 | N | NNN | 4 | 59.3 | 40 | 40 | John | |||||||||
| 16 | 866785 total | OZX01210 | O | OOO | 2 | 29.9 | 43 | 43 | John | |||||||||
| 17 | 866950 total | FPC01005 | P | PPP | 5 | 85.9 | 43 | 43 | PPP | John | PPP | |||||||
Sheet1 | ||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| M2:M17 | M2 | =IF(J2="","",IF(G2<>H2,"check","")) |
| N2:N17 | N2 | =IF(J2="","",IF(AND(E2<=2,VALUE(RIGHT(G2,1))=0),"delivery code check","")) |
| O2:O17 | O2 | =IF(J2="","",IF(AND(E2>=4,VALUE(RIGHT(G2,1))<>0),LEFT(D2,3),"")) |
| P2:P17 | P2 | =IF(J2="","",IF(AND(G2=90,E2<=2),"warehouse check","")) |
| I2:I17 | I2 | =IF(M2<>"",M2,IF(N2<>"",N2,IF(O2<>"",O2,IF(P2<>"",P2,"")))) |
