Match every two sequential rows

[Yorker]

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 "20" 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
Sri

[zbor]

Here, try this (CF from row 2):

Please Login or Register  to view this content.

[Yorker]

Hi I tried it, But it gave positive results two time and negative result once. Am I attaching, the sheet highlight the problem in red.

[estige]

Hi, try
I2: =IF(AND(MOD(B2,2)=1,B3=B2+1,D2<>D3),1,IF(AND(MOD(B2,2)=0,L1=1),1;""))

[zbor]

Try this but it also doesn't work for your example so will need a clarification:

Please Login or Register  to view this content.

You paired rows (blue) 23 and 24 but I think it should be paired (per mine formula) 24th and 25th because they are same source (col7) and destination (col8)

[Yorker]

Hi Estige and Zbor,

Thank you very much for helping me..

I tried both the formulas you have given. Zbor, my apologies of previous highlight. You are right. This time I have used my real set of data. With Estige formula it always returned the correct line item, but only returned the first line item and ignores the second line that is my destination movement line item. With Zbor formula when I used this on real data set it fails always. It is only working on the sample data.

Attaching the real data set.

Thank you.

[zbor]

Here,
I've modify estige's formula:

Please Login or Register  to view this content.

[Yorker]

Hi Zbor,

Apologies for responding so late. Thank you very much for the new formula. This works and helps me resolve my problem.

Thank you estige for proving the intial formula.


Regards
Sri

[Yorker]

Hi Zbor,
Can you please explain me this formula that you gave me. more so with MOD(B2,2). How does this allow be to check 2 consecutive lines as one set of data. This has solved my problem but want to understand.

=IF(MOD(B2,2)=1,AND(B3=B2+1,D2<>D3),AND(B2=B1+1,D2<>D1))
Thank you.

[zbor]

Sure.
MOD (modulo) operation finds the remainder of division of one number by another number.
MOD(B2,2) will return 0 or 1 depending what is in B2 (2:2 = 1 (0), 3:2 = 1 (1), 4:2 = 2 (0), 5:2 = 2 (1) etc)
Note that MOD(B2, 3) can return 0, 1 or 2... Etc.

In consecutive MOD returns 1,0,1,0,1,0,1,0,...
Therefore MOD(B2,2)=1 (or MOD(B2,2)=0 only swap criteria) will alternate IF answers:

IF B3 = B2 +1 means if next row value (i.e. 18) is same as current value (17) +1.
If yes then it's consecutive number.
And then check is D3 different than previous D2 (D2<>D3).
This will return TRUE or FALSe depending are both criteria are met.

If IF function return FALSE (that means mod(B2, 2) = 0) it will look previous row (is 17 = 16+1)

[Yorker]

Thank you very much. Now it is very clear to me.