excel extra data and match with different row

[phooirv]

1 Date​ Batch 1​ 1st time Batch 2 2nd Time Hour 28/01/2019​ AA103​ BB100​ 6:09 AM​ 0.75hrs 28/01/2019​ AA104​ BB101​ 6:15 AM​ 1.23 hrs 28/01/2019​ AA107​ BB102​ 6:25 AM​ 1.18 hrs 28/01/2019​ AA106​ BB103​ 6:31 AM​ 1.43hrs 28/01/2019​ AA106​ BB104​ 6:46 AM​ 0.13hrs 28/01/2019​ AA105​ BB105​ 6:52 AM​ 0.03hrs 28/01/2019​ BB100​ 6:54 AM​ 28/01/2019​ AA102​ BB106​ 7:09 AM​ 1.83 hrs 28/01/2019​ BB101​ 7:29 AM​ 28/01/2019​ ​ 7:36 AM​ 28/01/2019​ AA110​ BB107​ 7:37 AM​ 28/01/2019​ AA100​ BB108​ 7:45 AM​ 28/01/2019​ AA101​ BB109​ 7:51 AM​ 28/01/2019​ ​ 7:57 AM​ 28/01/2019​ AA101​ BB110​ 7:58 AM​ 28/01/2019​ AA103​ BB111​ 8:05 AM​ 28/01/2019​ AA107​ BB112​ 8:19 AM​ 28/01/2019​ AA106​ BB113​ 8:27 AM​ 28/01/2019​ AA106​ BB114​ 8:51 AM​ 28/01/2019​ ​ 8:59 AM​ Base on Column E (2nd Time, will search for Column B (batch 1) which is Blank Only, if the Hour is different less than 2.0 Hours (1st time - 2nd Time) Then allocate Batch 2 Number to Batch 1 Number for Example 1st record , 6:54am - 6:09am = 0.75 Hours (meet the criteria), then Assign batch 2 number BB100 to Batch 1 (blank cell) B8 in red 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 in red As for Row 6 and 5 , no record found to allocate as all batch 1 record is fully allocated (1st come 1 serve base on 2nd time ) <tbody> </tbody>

<tbody>
</tbody>

 

[Gerald Higgins]

Your post is very confusing, at least to me.
What exactly do you need help with ?

 

[phooirv]

A B C D E F 1 Date Batch 1 1st Time batch 2 2nd Time Hour 2 28/1/19 AA103 BB100 6:09am 0.75hrs 3 28/1/19 AA104 BB101 6:15am 1.23hrs 4 28/1/19 AA107 BB102 6:25am 1.18hrs 5 28/1/19 AA106 BB103 6:31am 1.43hrs 6 28/1/19 6:54am 7 28/1/19 AA102 BB106 7:09am 1.83hrs 8 28/1/19 7:29am 9 28/1/19 7:36am 10 28/1/19 AA110 BB107 7:37am 11 28/1/19 AA100 BB108 7:45am 12 28/1/19 AA101 BB109 7:51am 13 28/1/19 7:57am 14 28/1/19 AA101 BB110 7:58am 15 28/1/19 AA103 BB111 8:05am 16 28/1/19 AA107 BB112 8:19am 17 28/1/19 AA106 BB113 8:27am 18 28/1/19 AA106 BB114 8:51am 19 28/1/19 8:59am 20 21 22 <tbody> </tbody>

<tbody>
</tbody>

Basically, I want to fill the blank cell for batch1 (Column B-B6,B8,B9,B13,B19 which is hightlighed in yellow) 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