+ Reply to Thread
Results 1 to 6 of 6

Is there any ways to randomize an entire chart with specifications?

  1. #1
    Registered User
    Join Date
    05-19-2017
    Location
    Spain
    MS-Off Ver
    Excel 2007
    Posts
    3

    Question Is there any ways to randomize an entire chart with specifications?

    Hello everyone! I found this forum while looking for tips on Excel and I saw this one so I said to myself: "why not?".

    So here is the issue: I have to fill an entire chart with every day on this year with different working shifts. Also I have to give them holidays and stuff. I used formulas to randomize it all, but it has one problem: I canīt have a "Morning" shift if the previous day I had an "Afternoon" shift. I'll try my best to explain this: I have four workers (let's call them Worker1 and stuff, for the sake of simplicity) and all of them must work 1748 hours or less during the year. This is, of course, couting holidays (they must have at least 1 free weekend a month and 30 days of holidays, splitted in a period of 15 days each). Again, I have no problem with the holidays, the main issue is that I must have two workers that have a "Morning" shift, one worker with an "Afternoon" shift and the remaining worker has a free day. But, as I said earlier, if a worker had an "Afternoon" shift the previous day, they can't have a "Morning" one the next day, in which case they must have either a free day (labeled as "F" in the chart) or work another "Afternoon" shift. You must be getting the point, and that point is that I can't randomize the chart and have everything perfect because it'll keep putting "Mornings" after "Afternoons"... So I had to ask, is there any way that I can randomize the chart with that specification in mind? I'm not asking to do my job, I just want to know if there's a simpler way for this because I honestly don't have the time and will to go through everyday in the year putting shifts randomly, it'd take a very long time to complete everything.

    Thanks beforehand, hope you guys can help me out!

    PD: If this is not the place to ask this kind of things, please move this post :DD

  2. #2
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2010
    Posts
    1,782

    Re: Is there any ways to randomize an entire chart with specifications?

    It's hard to tell exactly how your chart looks, but my advice would be to disregard your plan to randomize the shift schedule and randomize the name attribution instead. Make a shift schedule that works using unnamed workers 1 through 4, then randomize which actual employee gets which worker slot. In the attachment, I've created a shift scheduler in which every worker follows the same daily progression: morning-morning-afternoon-off. This satisfies your rules and seems fair. The schedule is designed with numbers 1 through 4 instead of workers. The initial design is on sheet 2. It uses the following formula in B2:

    =IF(MOD(ROW()-COLUMN(A:A)+1,4)=0,4,MOD(ROW()-COLUMN(A:A)+1,4))

    Fill right through E2 and down through row 366 and you'll have a shift setup. I then use RAND() and RANK in G2:H5 to randomly determine which actual name is attached to worker 1, 2, 3, and 4. The full setup with names is viewable on sheet one using the following in B2:

    =INDEX($I$2:$I$5,IF(MOD(ROW()-COLUMN(A:A)+1,4)=0,4,MOD(ROW()-COLUMN(A:A)+1,4)))

    Each time you recalculate you'll get a new random order, so do it once to get your names, then copy and paste values in G2:G5 so it doesn't keep re-randomizing. This method should let you develop a perfect schedule while still adding a randomness to the determination of who works when on what day. Take a look at the attachment to see if it helps:
    Attached Files Attached Files
    If your problem has been solved, please use "Thread Tools" to mark the thread as "Solved".

    If you're grateful for this site's existence and would like to contribute to the community, please consider posting something hilarious in our joke thread:
    https://www.excelforum.com/the-water...ke-thread.html

  3. #3
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Win 2010-2016
    Posts
    11,109

    Re: Is there any ways to randomize an entire chart with specifications?

    If your Excel version is 2007, why did you post this in the Office 365 forum?

  4. #4
    Registered User
    Join Date
    05-19-2017
    Location
    Spain
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Is there any ways to randomize an entire chart with specifications?

    Wow... it's amazing. This is what I wanted, but I feel like you did the whole job for me... anyway, thank you a lot! I'm really glad with this forums.

  5. #5
    Registered User
    Join Date
    05-19-2017
    Location
    Spain
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Is there any ways to randomize an entire chart with specifications?

    Sorry, I use Excel 2007 in one place and Excel 2010-13 in others, so I didn't know what exactly to put

  6. #6
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2010
    Posts
    1,782

    Re: Is there any ways to randomize an entire chart with specifications?

    I'm glad I could help, good luck!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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