hi team,
i have a datasheet. I need ecel to validate my data and pick up the wrong record 's invoice no and put it in another sheet for record. the logic will be below:
1) If Mode = FCL, the FullBox must be Y, or else, pick the invoice no (e..g ZF002) and put to sheet2 at cell A1. The logic works for next record (i.e row), in next one, it will pick the ZF003 to sheet2 A2, but this time ZF003 happen 3 times, i only need ZF003 at sheet2 A2 for one time only.
2) the next logic is :
if Mode = MCC, the Full box must be N, so ZF006 and ZF007 is wrong,excel will identify and put the invoice ZF006 and ZF007 to sheet2 at A3 & A4.
In my actual piece of work, i will apply similar logic to many different data check, i just want the idea how to write that and develop myself to complete the full validation, pls help. Thannk you
i have a datasheet. I need ecel to validate my data and pick up the wrong record 's invoice no and put it in another sheet for record. the logic will be below:
1) If Mode = FCL, the FullBox must be Y, or else, pick the invoice no (e..g ZF002) and put to sheet2 at cell A1. The logic works for next record (i.e row), in next one, it will pick the ZF003 to sheet2 A2, but this time ZF003 happen 3 times, i only need ZF003 at sheet2 A2 for one time only.
2) the next logic is :
if Mode = MCC, the Full box must be N, so ZF006 and ZF007 is wrong,excel will identify and put the invoice ZF006 and ZF007 to sheet2 at A3 & A4.
In my actual piece of work, i will apply similar logic to many different data check, i just want the idea how to write that and develop myself to complete the full validation, pls help. Thannk you
Book2 | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | Invoice_no | FullBox | Mode | |||
2 | ZF001 | Y | FCL | |||
3 | ZF002 | N | FCL | |||
4 | ZF003 | N | FCL | |||
5 | ZF003 | N | FCL | |||
6 | ZF003 | N | FCL | |||
7 | ZF004 | Y | FCL | |||
8 | ZF005 | N | MCC | |||
9 | ZF006 | Y | MCC | |||
10 | ZF007 | Y | MCC | |||
Sheet1 |