Random Allocation of Halls

[awanak]

I have the following data on excel workbook

• Employee Data is at sheet namely CompleteList.
• Name of Employees at Column B
• BS of Employee at Column D
• Station of Employee at Column G
• Rating of Employee at Column H
• Roles of Employee at Column B
• Halls should be allocated to Employee at Column J
• Halls data is at sheet HallsList
• Halls for Roles as Chief Supervisors at Column A
• Halls for Roles as Chief/Supervisors at Column B
• Halls for Roles as Supervisor or Dy. Supervisor/Invigilator or Invigilator/Clerk or Daftry at Column C
• Halls for Roles as Driver at Column
• I want that Halls should be allocated to employees randomly by specifying the range of employees and Halls, with the following conditions:
• Same hall should not be allocated to two or more employees with Week rating. Week Supervisor should have strong one Dy. Supervisor/Invigilator or Invigilator/Clerk and one Daftry.
• No hall should have more than one employee with the same role ensuring that each hall must have a supervisor.
• No employee of higher BS be assigned a Hall under employee of lower BS as Dy. Supervisor/Invigilator
• Where Station is specified, halls should be allocated against that station only.
• No employee under BS-17 should be allocated first hall of any station at any station