# Office 365 >  >  Is there any ways to randomize an entire chart with specifications?

## Nightmarer26

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

----------


## CAntosh

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:

----------


## teylyn

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

----------


## Nightmarer26

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.

----------


## Nightmarer26

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

----------


## CAntosh

I'm glad I could help, good luck!

----------

