+ Reply to Thread
Results 1 to 21 of 21

Day and Shift wise allocation from Roster / employee shift schedule

  1. #1
    Registered User
    Join Date
    03-15-2017
    Location
    Mumbai
    MS-Off Ver
    2013
    Posts
    96

    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
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    03-15-2017
    Location
    Mumbai
    MS-Off Ver
    2013
    Posts
    96

    Re: Day and Shift wise allocation from Roster / employee shift schedule

    Please revert

  3. #3
    Registered User
    Join Date
    03-15-2017
    Location
    Mumbai
    MS-Off Ver
    2013
    Posts
    96

    Re: Day and Shift wise allocation from Roster / employee shift schedule

    Kindly help me in the same

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,705

    Re: Day and Shift wise allocation from Roster / employee shift schedule

    This is along the lines of 'help' rather than claiming to be a solution, as it does not fill in the Seal Checkers field in the same manner as the file attached to post #1. It does however fill the other fields as desired and gets desired information from the Seal Checkers sheet, but not the additional sr.no. and names that were listed.
    In all fields the array entered formulas* that get the sr.no.'s are similar to:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    In all fields the formulas that get the names are similar to:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The counts for all fields are calculated using a formula similar to:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    *Array entered formulas are activated by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    03-15-2017
    Location
    Mumbai
    MS-Off Ver
    2013
    Posts
    96

    Re: Day and Shift wise allocation from Roster / employee shift schedule

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

    You are really champ...

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,705

    Re: Day and Shift wise allocation from Roster / employee shift schedule

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.

  7. #7
    Registered User
    Join Date
    03-15-2017
    Location
    Mumbai
    MS-Off Ver
    2013
    Posts
    96

    Re: Day and Shift wise allocation from Roster / employee shift schedule

    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

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,705

    Re: Day and Shift wise allocation from Roster / employee shift schedule

    This update addresses points 1 and 3.
    To address point 1 helper columns are employed (XFC:XFD). The formulas for the helper columns are similar to:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The formulas that populate the columns with the employee code are similar to:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    To address point 3, the formula in Allocation!D10 has been modified to read:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    I will have to give further consideration as to addressing point 2. If it were the last two 'Seal Checkers' listed that were going to be assigned the role of 'Relievers' that might be easier.
    Let us know if you have any questions.

  9. #9
    Registered User
    Join Date
    03-15-2017
    Location
    Mumbai
    MS-Off Ver
    2013
    Posts
    96

    Re: Day and Shift wise allocation from Roster / employee shift schedule

    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

  10. #10
    Registered User
    Join Date
    03-15-2017
    Location
    Mumbai
    MS-Off Ver
    2013
    Posts
    96

    Re: Day and Shift wise allocation from Roster / employee shift schedule

    Please revert

  11. #11
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,705

    Re: Day and Shift wise allocation from Roster / employee shift schedule

    I still have not thought of a way to do this given the constraints in post #9 and what I see in the spreadsheets attached to post #7. My best advice would be to start a new thread attaching a file with what works so far by formula, Seal Checkers Supervisors; Gear Box Man etc. Manually fill in what you expect to see in the two 'Reliever' fields along with explanations of how those names were randomly picked.
    I wish you the best.

  12. #12
    Registered User
    Join Date
    03-15-2017
    Location
    Mumbai
    MS-Off Ver
    2013
    Posts
    96

    Re: Day and Shift wise allocation from Roster / employee shift schedule

    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

  13. #13
    Registered User
    Join Date
    03-15-2017
    Location
    Mumbai
    MS-Off Ver
    2013
    Posts
    96

    Re: Day and Shift wise allocation from Roster / employee shift schedule

    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,

  14. #14
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,705

    Re: Day and Shift wise allocation from Roster / employee shift schedule

    I believe that I have the Lasher and Relief Lasher rosters working. The logic of the Seal Checkers escapes me. I started assigning Seal Checkers to teams (Roster_Seal Checkers sheet column A) based on my understanding of post #13 however there is only one #4 and one #5 I don't understand how the team concept should be applied. I will also point out that there are only two Seal Checker Relievers so that on the day shift for the 18th there would not be any relievers available and on the 20th both are listed as OFF so there will be none available for either shift. The Seal Checker roster attached to post #7 would be easier to work with providing the Relievers were identified in the Sequence/priority column.
    Let us know if you have any questions.

  15. #15
    Registered User
    Join Date
    03-15-2017
    Location
    Mumbai
    MS-Off Ver
    2013
    Posts
    96

    Re: Day and Shift wise allocation from Roster / employee shift schedule

    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

  16. #16
    Registered User
    Join Date
    03-15-2017
    Location
    Mumbai
    MS-Off Ver
    2013
    Posts
    96

    Re: Day and Shift wise allocation from Roster / employee shift schedule

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

    Ravi

  17. #17
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,705

    Re: Day and Shift wise allocation from Roster / employee shift schedule

    This may get you a bit closer to the solution that you are looking for.
    The array entered formula* to display Seal Checker codes by team is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Other formulas on the Allocation sheet are similar to those given in previous posts.
    Let us know if you have any questions.

  18. #18
    Registered User
    Join Date
    03-15-2017
    Location
    Mumbai
    MS-Off Ver
    2013
    Posts
    96

    Re: Day and Shift wise allocation from Roster / employee shift schedule

    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

  19. #19
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,705

    Re: Day and Shift wise allocation from Roster / employee shift schedule

    The array entered formula* that retrieves the code is similar to this one which is in cell O17:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The formula that retrieves the Name is similar to this one that is in P17:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If you need to add additional functionality to the workbook it would probably be seen sooner and get more replies if you would start a new thread.
    Let us know if you have any questions.

  20. #20
    Registered User
    Join Date
    03-15-2017
    Location
    Mumbai
    MS-Off Ver
    2013
    Posts
    96

    Re: Day and Shift wise allocation from Roster / employee shift schedule

    Yes Sir,

    It's done thanks a lot

    - Ravi

  21. #21
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,705

    Re: Day and Shift wise allocation from Roster / employee shift schedule

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Employee shift roster template
    By AussieExcelUser in forum Excel General
    Replies: 13
    Last Post: 06-21-2019, 08:40 AM
  2. Building an Employee Shift Schedule that Automatically Deducts Breaks
    By derdoktor in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-11-2016, 04:09 AM
  3. Add employee name to schedule based on Shift and date
    By BARBIEE in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-18-2016, 12:44 AM
  4. Replies: 0
    Last Post: 10-27-2014, 11:54 PM
  5. Replies: 1
    Last Post: 09-29-2013, 04:33 AM
  6. Shift Schedule - 3 Shift Coverage - Auto Populate Roll-up Summary Schedule
    By chips1256 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-31-2012, 09:32 AM
  7. Help with setting up a shift schedule/roster!
    By target.aquired in forum Excel General
    Replies: 3
    Last Post: 08-07-2012, 05:29 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1