Hi everyone.

I'm new around here, so forgive me if this or something similar has
been asked before (I would have done a search, but wasn't sure what to
search on!)

Please have a read of what I am trying to accomplish, and if you can
help - please do, if I'm in totally the worng area to ask this, please
point me in the correct direction.

This is what I am trying to do:

I have been asked by the powers that be to create a new work rota - the
work rota is made up from 15 people (numbered 1 - 15 for simplicity) and
the rota has to be made up a specific way, as there are 15 people, there
are 15 different work patterns (we call them cycles 1 - 15 with 15 days
in each cycle) - now this work rota has to be laid out in such a
fashion so that there employee numbers don't follow on i.e. (1, 2, 3
etc.) and also so that there is no patterm to the work rota (so a cycle
would be 1 followed by 4 then 7 and 14 then 2 etc) and no two work rotas
are the same - for example:

(emp = employee number)

Code:
--------------------

Cycle 1 Cycle 2

Day 1 emp 1 emp 4
Day 2 emp 9 emp 12
Day 3 emp 5 emp 3
--------------------



I know this sounds very complicated and I think it can be - but
what I want to know and I have little experience with Excel is can I do
this in excel and is there some sort of formula to be able to work it
out or can this sort of thing only be done manually?

If there is a formula or if anybody has a solution on how to do this, I
would be extremely grateful to them to help me solve this little
problem.

I hope I have explained what I am trying to do well enough for anybody
else to understand, if not, let me know and I'll try and explain it a
little better.

Thanks to anybody in advance who can help!


--
dataheadache
------------------------------------------------------------------------
dataheadache's Profile: http://www.excelforum.com/member.php...o&userid=27734
View this thread: http://www.excelforum.com/showthread...hreadid=472444

I'm not familiar with the term "rota". Is it a Britishism? From the
context it seems to mean "work schedule" and yet it seems to be randomly
generated (or that's how I read your message). That seems a bit odd to me
in that it doesn't take into account people's availability. So is that
really what you want - 15 unique combinations of the numbers 1 to 15? Excel
can produce random numbers (pseodo-random anyway).

--
Jim
"dataheadache"
wrote in message
news:dataheadache.1wagqc_1128279902.2949@excelforu m-nospam.com...

Hi everyone.

I'm new around here, so forgive me if this or something similar has
been asked before (I would have done a search, but wasn't sure what to
search on!)

Please have a read of what I am trying to accomplish, and if you can
help - please do, if I'm in totally the worng area to ask this, please
point me in the correct direction.

This is what I am trying to do:

I have been asked by the powers that be to create a new work rota - the
work rota is made up from 15 people (numbered 1 - 15 for simplicity) and
the rota has to be made up a specific way, as there are 15 people, there
are 15 different work patterns (we call them cycles 1 - 15 with 15 days
in each cycle) - now this work rota has to be laid out in such a
fashion so that there employee numbers don't follow on i.e. (1, 2, 3
etc.) and also so that there is no patterm to the work rota (so a cycle
would be 1 followed by 4 then 7 and 14 then 2 etc) and no two work rotas
are the same - for example:

(emp = employee number)

Code:
--------------------

Cycle 1 Cycle 2

Day 1 emp 1 emp 4
Day 2 emp 9 emp 12
Day 3 emp 5 emp 3
--------------------



I know this sounds very complicated and I think it can be - but
what I want to know and I have little experience with Excel is can I do
this in excel and is there some sort of formula to be able to work it
out or can this sort of thing only be done manually?

If there is a formula or if anybody has a solution on how to do this, I
would be extremely grateful to them to help me solve this little
problem.

I hope I have explained what I am trying to do well enough for anybody
else to understand, if not, let me know and I'll try and explain it a
little better.

Thanks to anybody in advance who can help!


--
dataheadache
------------------------------------------------------------------------
dataheadache's Profile:
http://www.excelforum.com/member.php...o&userid=27734
View this thread: http://www.excelforum.com/showthread...hreadid=472444

This particular schedule (or rota) does not need to take in people's
availablility, and yes, the randomness is what I need, but how can you
get Excel to pick 15 numbers and put them randomly on every line of a
15x15 grid, so that no number (sequentially) is next to another?


--
dataheadache
------------------------------------------------------------------------
dataheadache's Profile: http://www.excelforum.com/member.php...o&userid=27734
View this thread: http://www.excelforum.com/showthread...hreadid=472444

how can you get Excel to pick 15 numbers and put them randomly on every
line of a
15x15 grid, so that no number (sequentially) is next to another?

I don't know. There is no way to do this by formula but it could be done by
macro, except for the "no number (sequentially) is next to another"
requirement. You see, the macro could randomly pick the first number and
then a second (excluding the first and the next number after it) but I don't
see how you can be sure at some point you do not have only sequential
numbers to pick from like 3,4 and 5.

--
Jim
"dataheadache"
wrote in message
news:dataheadache.1wbjmi_1128330331.5265@excelforu m-nospam.com...
|
| This particular schedule (or rota) does not need to take in people's
| availablility, and yes, the randomness is what I need, but how can you
| get Excel to pick 15 numbers and put them randomly on every line of a
| 15x15 grid, so that no number (sequentially) is next to another?
|
|
| --
| dataheadache
| ------------------------------------------------------------------------
| dataheadache's Profile:
http://www.excelforum.com/member.php...o&userid=27734
| View this thread: http://www.excelforum.com/showthread...hreadid=472444
|

Thank you, Jim for your valuble input, but I have now done it by using
pencil and paper alone. It took about 60 attempts and working out a
sort of pattern to follow to ensure that no sequential number was next
to it - but in the end it worked.


Thanks again. :)


--
dataheadache
------------------------------------------------------------------------
dataheadache's Profile: http://www.excelforum.com/member.php...o&userid=27734
View this thread: http://www.excelforum.com/showthread...hreadid=472444

Hi. If you would like a possible macro that Jim mentioned, here is one of a
few ways.
Note that there are =FACT(15), or 1,307,674,368,000 possible permutations
of 15.
I note that 12,13 is not good, but 13,12 is good because it's not an
increasing sequence.
This uses a helper column to check if two adjacent numbers are sequential,
and randomly sorts the numbers 1-15. It took less than 1 second.
This doesn't technically insure there are no duplicates, but the odds are
low. You could adjust the output from 15 to say 20 if you wish.

Sub Demo()
'// Dana DeLouis
Dim R As Long

[C1] = 1
[C2] = 2
[C1:C2].AutoFill Destination:=Range("C1:C15"), Type:=xlFillDefault

[D1].Formula = "=RAND()"
[D1].AutoFill Destination:=Range("D1:D15"), Type:=xlFillDefault

[A1].FormulaR1C1 = "=--(RC[2]+1=R[1]C[2])"
[A1].AutoFill Destination:=Range("A1:A14"), Type:=xlFillDefault

[A16].FormulaR1C1 = "=SUM(R[-15]C:R[-2]C )"

For R = 1 To 15
Do While [A16] 0
[C1:D15].Sort Key1:=Range("D1")
Loop
[C1:C15].Copy
Cells(R, 6).PasteSpecial Transpose:=True
[C1:D15].Sort Key1:=Range("D1")
Next R
End Sub


--
Dana DeLouis
Win XP & Office 2003


"dataheadache"
wrote in message
news:dataheadache.1wbxid_1128348311.3967@excelforu m-nospam.com...

Thank you, Jim for your valuble input, but I have now done it by using
pencil and paper alone. It took about 60 attempts and working out a
sort of pattern to follow to ensure that no sequential number was next
to it - but in the end it worked.


Thanks again. :)


--
dataheadache
------------------------------------------------------------------------
dataheadache's Profile:
http://www.excelforum.com/member.php...o&userid=27734
View this thread: http://www.excelforum.com/showthread...hreadid=472444