- Excel match record from different position of row

  • [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 500"]

    [tr]


    [td]

    [TABLE="align: center, border: 0, cellpadding: 0, width: 342"]

    [tr]


    [td][/td]


    [td]

    A

    [/td]


    [td]

    B

    [/td]


    [td]

    C

    [/td]


    [td]

    D

    [/td]


    [td]

    E

    [/td]


    [td]

    F

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td]

    Date

    [/td]


    [td]

    Batch 1

    [/td]


    [td]

    1st Time

    [/td]


    [td]

    batch 2

    [/td]


    [td]

    2nd Time

    [/td]


    [td]

    Hour

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td]

    28/1/19

    [/td]


    [td]

    AA103

    [/td]


    [td][/td]


    [td]

    BB100

    [/td]


    [td]

    6:09am

    [/td]


    [td]

    0.75hrs

    [/td]


    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td]

    28/1/19

    [/td]


    [td]

    AA104

    [/td]


    [td][/td]


    [td]

    BB101

    [/td]


    [td]

    6:15am

    [/td]


    [td]

    1.23hrs

    [/td]


    [/tr]


    [tr]


    [td]

    4

    [/td]


    [td]

    28/1/19

    [/td]


    [td]

    AA107

    [/td]


    [td][/td]


    [td]

    BB102

    [/td]


    [td]

    6:25am

    [/td]


    [td]

    1.18hrs

    [/td]


    [/tr]


    [tr]


    [td]

    5

    [/td]


    [td]

    28/1/19

    [/td]


    [td]

    AA106

    [/td]


    [td][/td]


    [td]

    BB103

    [/td]


    [td]

    6:31am

    [/td]


    [td]

    1.43hrs

    [/td]


    [/tr]


    [tr]


    [td]

    6

    [/td]


    [td]

    28/1/19

    [/td]


    [td][/td]


    [td]

    6:54am

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    7

    [/td]


    [td]

    28/1/19

    [/td]


    [td]

    AA102

    [/td]


    [td][/td]


    [td]

    BB106

    [/td]


    [td]

    7:09am

    [/td]


    [td]

    1.83hrs

    [/td]


    [/tr]


    [tr]


    [td]

    8

    [/td]


    [td]

    28/1/19

    [/td]


    [td][/td]


    [td]

    7:29am

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    9

    [/td]


    [td]

    28/1/19

    [/td]


    [td][/td]


    [td]

    7:36am

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    10

    [/td]


    [td]

    28/1/19

    [/td]


    [td]

    AA110

    [/td]


    [td][/td]


    [td]

    BB107

    [/td]


    [td]

    7:37am

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    11

    [/td]


    [td]

    28/1/19

    [/td]


    [td]

    AA100

    [/td]


    [td][/td]


    [td]

    BB108

    [/td]


    [td]

    7:45am

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    12

    [/td]


    [td]

    28/1/19

    [/td]


    [td]

    AA101

    [/td]


    [td][/td]


    [td]

    BB109

    [/td]


    [td]

    7:51am

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    13

    [/td]


    [td]

    28/1/19

    [/td]


    [td][/td]


    [td]

    7:57am

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    14

    [/td]


    [td]

    28/1/19

    [/td]


    [td]

    AA101

    [/td]


    [td][/td]


    [td]

    BB110

    [/td]


    [td]

    7:58am

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    15

    [/td]


    [td]

    28/1/19

    [/td]


    [td]

    AA103

    [/td]


    [td][/td]


    [td]

    BB111

    [/td]


    [td]

    8:05am

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    16

    [/td]


    [td]

    28/1/19

    [/td]


    [td]

    AA107

    [/td]


    [td][/td]


    [td]

    BB112

    [/td]


    [td]

    8:19am

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    17

    [/td]


    [td]

    28/1/19

    [/td]


    [td]

    AA106

    [/td]


    [td][/td]


    [td]

    BB113

    [/td]


    [td]

    8:27am

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    18

    [/td]


    [td]

    28/1/19

    [/td]


    [td]

    AA106

    [/td]


    [td][/td]


    [td]

    BB114

    [/td]


    [td]

    8:51am

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    19

    [/td]


    [td]

    28/1/19

    [/td]


    [td][/td]


    [td]

    8:59am

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]

    [/td]


    [/tr]


    [/TABLE]


    Basically, I want to fill the blank cell for batch1 (Column B-B6,B8,B9,B13,B19 ) from Batch 2 (Column D-D2,D3,D4,D5,D7)
    base on criteria (Hour between 1st time (Col C) and 2nd time(Col E) must less than 2 Hours)

    for Example 1st record , 6:54am - 6:09am = 0.75 Hours (meet the criteria), then Assign batch 2 number BB100 to Batch 1 (which is blank) B8

    Subsequent for Example 2nd record , 7:29am - 6:15am = 1.23 Hours (meet the criteria), then Assign batch 2 number BB101 to Batch 1 (blank cell) B10