Day and Shift wise allocation from Roster / employee shift schedule

Hi Excel Experts,
We have different separate worksheets for Roster / employee shift schedule of different categories like..SEAL CHECKERS - Supervisor, SEAL CHECKERS, GEAR BOX MAN, MEPL - RMG/ICD LASHERS etc., wherein D = Day, N = Night, Off = Weekly Off etc.and for day allocation we require one more summary sheet which will for allocation, in which after selecting specific Date & Shift from drop-down lists, names are to be displayed on that allocation sheet. How can it be automated with the help of vba or formula? (ref. attached file for the same)
Kindly help me in the same
Ravi

Please revert

Kindly help me in the same

Thanks a lot SIR:,
It's working as desired.

You are really champ...

Sir,
Your reply is really good, however, due to following challenges, it is incomplete, can you please look into the same, and if it will complete, it will really help me a lot...
While sending file and writing details and I missed following...
There are 2 columns in between name and date i.e. Designation and Sequence/priority
Apart from existing criteria following is additional requirement (Criterion), what I missed in our previous correspondences, really sorry for that…
1. Basis column naming “Sequence/priority”, list of name(s) and code sequence ( ascending order) is to be appearing in to allocation i.e. 1st reflects all 1s then 2s, then 3s and then blank.
2. For 2 specific categories i.e. Seal Checkers & RMG/ICD LASHERS, relievers are there.i.e on random basis from list to avoid fix relievers.
a. From seal checkers list (for specific Date and shift) randomly 2 names to be reflected into segment “ 2 Relievers for Seal Checkers” for example : out of 10 seal checkers for specific Date and shift, 8 to be reflected into SEAL CHECKERS column and 2 to be reflected to 2 Relievers for Seal Checkers.
b. From RMG/ICD LASHERS list (for specific Date and shift) randomly 1 name to be reflected into segment “1 Reliever for ICD lashers” for example : out of 4 seal checkers for specific Date and shift, 3 to be reflected into MEPL - RMG/ICD LASHERS column and 1 to be reflected to 1 Reliever for ICD lashers .
3. Lastly, for SEAL CHECKERS – Supervisor, if there is No Supervisor allocated for specific Date and shift, remarks to be appeared, “No Supervisor allocated, kindly manage it using ACTING Supervisor etc.”

Apologize / sorry for inconvenience caused to you due to my mistake in previous correspondences.
Can you please help me in this .

-- Ravi

Sir,
AS PER REQUIREMENT,
a. The RELIEVERS for Seal Checkers should be ANY TWO 'Seal Checkers' on RANDOM BASIS from listed Seal Checkers to avoid fix relievers
example : out of 12 seal checkers for specific Date and shift, 10 to be reflected into SEAL CHECKERS column and 2 (RANDOM BASIS from 12) to be reflected to 2 Relievers for Seal Checkers.

b. The RELIEVERS for RMG/ICD LASHERS should be ANY ONE 'RMG/ICD LASHERS' on RANDOM BASIS from listed RMG/ICD LASHERS to avoid fix relievers
example : out of 04 RMG/ICD LASHERS for specific Date and shift, 03 to be reflected into RMG/ICD LASHER column and 01 (RANDOM BASIS from 04) to be reflected to 1 Reliever for ICD lashers.

- Ravi

Please revert

Sir,

A. Is it OK,if i define/allocate manually Reliever in "Sequence/priority" column?

B. As we are adjusting this, we out to categorized SEAL CHECKERS into 4 teams for that i have defined 1,2,3 and 4
all 1s to be segregated into different teams.

Team1 Team2 Team3 Team4 Team5
1 1 1 1 1
2 2 2 2 2
3 3 3 3 3
4 4 4 4 4

Kindly ref. attached file for more details

Thanks in advance

- Ravi

In Team 1 1 ,2,3, 4 & 5
In Team 2 next 1 ,2,3, 4 & 5
In Team 3 next 1 ,2,3, 4 & 5
In Team 4 next 1 ,2,3, 4 & 5
In Team 5 next 1 ,2,3, 4 & 5
If any digit is missing in Sequence/priority" column for specific date and shift, it should be continued with next numbers,

Sir,
1. Allocation for MEPL - RMG/ICD LASHERS including relievers working fine (Thanks for the same),
2. As i requested in my previous correspondence, my requirement is as follows...
(As you mentioned that even sometimes or several times teams allocation/roster CONCEPT looks like ILLOGICAL, however, my requirement will REMAINS SAME as gradually we will streamline the roster, which will be logically OK )
A. I will define/allocate manually 1, 2, 3, 4, 5 , Reliever1 & Reliever2 in "Sequence/priority" column
B. SEAL CHECKERS are to be categorized into 4 teams for that i have defined 1,2,3, 4 and 5 (in ascending order) ; all 1s to be segregated into different teams and likewise..
Team1 Team2 Team3 Team4 Team5
1 1 1 1 1
2 2 2 2 2
3 3 3 3 3
4 4 4 4 4
5 5 5 5 5
Reliver1
Reliver2

If any digit is missing in Sequence/priority" column for specific date and shift, it should be continued with next numbers (in ascending order) and if Relievers not available, it should be blank.

Please help me.

- Ravi Kadu

Sir,
Please help me to resolve it. Thank you for your help in advance.

Ravi

Sir,
It can be adjusted if, following criteria to be fulfilled
Only in case of ICD lashers, there are 4 different Point of work (POW), WHICH IS REQUIRED TO BE ROTATIONAL TYPE
For example, for 18 Day Shift, following 4 employees to be deployed at different 4 POWs
POW1 : MEPL_R_03 Durgesh Chikwa
POW2 : MEPL_R_04 Santosh Sen
POW3 : MEPL_R_05 Nagendra Jaiswal
POW4 : MEPL_R_06 Suraj Sen
However,
• in next shift (either D or N; not OFF) of Durgesh Chikwa, he should be deployed to POW2,
• in next shift (either D or N; not OFF) he should be deployed to POW3,
• and finally next shift (either D or N; not OFF) he should be deployed to POW4,
(Likewise he and every ICD lasher to be deployed rotational basis; POW1, POW2, POW3 & POW4, so that everyone will have opportunity to work all the POWs to avoid further dispute.

Please help, thanks in advance
-
Ravi

Yes Sir,

It's done thanks a lot

- Ravi

Hello,

I have a query. There are 100 unit of box placed at 5 different locations and the same has to be distributed to 4 different Category of people. Then how we will select that which box of which location need to be given to a particular person of a category.

Please provide suggestion on my mail id