Hi Experts
I need some urgent help
I have an order number-Col1 (159003) which have sequential item numbers (1,2,3,4 etc.) for each line in Col2 . I need to retrieve only those rows which have different batch numbers (col 4). Since, two consecutive line item numbers represent a movement of batch from source to destination (col7&8) in the business. E.g. If I have 10 line items (1to 10 item number), I want to consider every two line item numbers (1&2, 3&4, 5&6, etc) as one set and see if their batch number changes in that set and if yes retrieve these.
Which means if n= 1st row, match n+1 row (second row) batch number (col4)with n, retrieve these two line only if differ. Now I don’t want system to check between second and third row, but should check only 3 and 4th row for a batch number match.
While I was trying to formulate this, as I thought It can work, if I treat the first row as line item “1” as odd number and match with even number. This will avoid system from checking even to odd but will only match odd to even sequentially.
Now another problem I found that my system from which the report is extracted, has ignored a line item number “19” and used the next sequence as the source batch line item number for unknown reason and now I think my logic will not work. Or now I am doubting if I am thinking in right direction at all. Please help. How can I achieve this. I hope I am clear. If not please ask questions.
Below is the sample data
Order Number-Col1
Item Number-Col2
Product-Col3
Batch-Col4
Stock Type--Col5
Description-Col6
Source--Col7
Destination-Col8
159003
1
HNZZNH
2000052794
A1
200GX6
ABC
159003
2
HNZZNH
2000052794
Y5
200GX6
ABC
159003
3
HNZZNH
2000052794
A1
200GX6
XYZ
159003
4
HNZZNH
2000052794
Y5
200GX6
XYZ
159003
5
HNZZNH
2000052794
A1
200GX6
SDF
159003
6
HNZZNH
2000052794
Y5
200GX6
SDF
159003
7
HNZZNH
2000052794
A1
200GX6
ASDF
159003
8
HNZZNH
2000052794
Y5
200GX6
ASDF
159003
9
HNZZNH
2000052794
A1
200GX6
SDFE
159003
10
HNZZNH
2000052795
Y5
200GX6
SDFE
159003
11
HNZZNH
2000052794
A1
200GX6
FGH
159003
12
HNZZNH
2000052794
Y5
200GX6
FGH
159003
13
HNZZNH
2000052794
A1
200GX6
RTY
159003
14
HNZZNH
2000052794
Y5
200GX6
RTY
159003
15
HNZZNH
2000052794
A1
200GX6
YUI
159003
16
HNZZNH
2000052794
Y5
200GX6
YUI
159003
17
HNZZNH
2000052794
A1
200GX6
WER
159003
18
HNZZNH
2000052794
Y5
200GX6
WER
159003
20
HNZZNH
2000053006
A1
200GX6
FGJ
159003
21
HNZZNH
2000053006
Y5
200GX6
FGJ
159003
22
HNZZNH
2000053006
A1
200GX6
UIO
159003
23
HNZZNH
2000053006
Y5
200GX6
UIO
159003
24
HNZZNH
2000053007
A1
200GX6
QWE
159003
25
HNZZNH
2000053006
Y5
200GX6
QWE
159003
26
HNZZNH
2000053006
A1
200GX6
BM
159003
27
HNZZNH
2000053006
Y5
200GX6
BM
Regards,
Srini
I need some urgent help
I have an order number-Col1 (159003) which have sequential item numbers (1,2,3,4 etc.) for each line in Col2 . I need to retrieve only those rows which have different batch numbers (col 4). Since, two consecutive line item numbers represent a movement of batch from source to destination (col7&8) in the business. E.g. If I have 10 line items (1to 10 item number), I want to consider every two line item numbers (1&2, 3&4, 5&6, etc) as one set and see if their batch number changes in that set and if yes retrieve these.
Which means if n= 1st row, match n+1 row (second row) batch number (col4)with n, retrieve these two line only if differ. Now I don’t want system to check between second and third row, but should check only 3 and 4th row for a batch number match.
While I was trying to formulate this, as I thought It can work, if I treat the first row as line item “1” as odd number and match with even number. This will avoid system from checking even to odd but will only match odd to even sequentially.
Now another problem I found that my system from which the report is extracted, has ignored a line item number “19” and used the next sequence as the source batch line item number for unknown reason and now I think my logic will not work. Or now I am doubting if I am thinking in right direction at all. Please help. How can I achieve this. I hope I am clear. If not please ask questions.
Below is the sample data
Order Number-Col1
Item Number-Col2
Product-Col3
Batch-Col4
Stock Type--Col5
Description-Col6
Source--Col7
Destination-Col8
159003
1
HNZZNH
2000052794
A1
200GX6
ABC
159003
2
HNZZNH
2000052794
Y5
200GX6
ABC
159003
3
HNZZNH
2000052794
A1
200GX6
XYZ
159003
4
HNZZNH
2000052794
Y5
200GX6
XYZ
159003
5
HNZZNH
2000052794
A1
200GX6
SDF
159003
6
HNZZNH
2000052794
Y5
200GX6
SDF
159003
7
HNZZNH
2000052794
A1
200GX6
ASDF
159003
8
HNZZNH
2000052794
Y5
200GX6
ASDF
159003
9
HNZZNH
2000052794
A1
200GX6
SDFE
159003
10
HNZZNH
2000052795
Y5
200GX6
SDFE
159003
11
HNZZNH
2000052794
A1
200GX6
FGH
159003
12
HNZZNH
2000052794
Y5
200GX6
FGH
159003
13
HNZZNH
2000052794
A1
200GX6
RTY
159003
14
HNZZNH
2000052794
Y5
200GX6
RTY
159003
15
HNZZNH
2000052794
A1
200GX6
YUI
159003
16
HNZZNH
2000052794
Y5
200GX6
YUI
159003
17
HNZZNH
2000052794
A1
200GX6
WER
159003
18
HNZZNH
2000052794
Y5
200GX6
WER
159003
20
HNZZNH
2000053006
A1
200GX6
FGJ
159003
21
HNZZNH
2000053006
Y5
200GX6
FGJ
159003
22
HNZZNH
2000053006
A1
200GX6
UIO
159003
23
HNZZNH
2000053006
Y5
200GX6
UIO
159003
24
HNZZNH
2000053007
A1
200GX6
QWE
159003
25
HNZZNH
2000053006
Y5
200GX6
QWE
159003
26
HNZZNH
2000053006
A1
200GX6
BM
159003
27
HNZZNH
2000053006
Y5
200GX6
BM
Regards,
Srini
