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 )
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
We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel
Which adblocker are you using?
Disable AdBlock
Follow these easy steps to disable AdBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the icon in the browser’s toolbar. 2)Click on the "Pause on this site" option.
Go back
Disable AdBlock Plus
Follow these easy steps to disable AdBlock Plus
1)Click on the icon in the browser’s toolbar. 2)Click on the toggle to disable it for "mrexcel.com".
Go back
Disable uBlock Origin
Follow these easy steps to disable uBlock Origin
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.
Go back
Disable uBlock
Follow these easy steps to disable uBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.