Remember Me?

#1
October 4th 09, 07:59 PM posted to microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Oct 2006 Posts: 113
Random generation of employee assignments

We have 65 work assignments and must randomly re-assign them to our
employees each month. We have given each work assignment a number 1-65.
How do we randomly re-shuffle a list of 45 whole numbers at the beginning of
each month?

Once we have learned how to perform the random re-shuffle above, we would
also like to learn how to tailor the random assignments as follows:

First, how do we prevent an employee from being re-assigned the same
assignment they performed last month or perhaps the last 3 months?

Second, how do we prevent employees from receiving certain random numbers
that represent tasks they are not capable of performing? In other words,
how do we prohibit an employee from being assigned a subset of certain tasks

Thank you for any help with this question.

#2
October 4th 09, 08:50 PM posted to microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jan 2007 Posts: 2,059
Random generation of employee assignments

"Blue Max" wrote:
We have 65 work assignments and must randomly re-assign
them to our employees each month. We have given each
work assignment a number 1-65. How do we randomly re-shuffle
a list of 45 whole numbers at the beginning of each month?

I'm confused. Do you have the same number of work assignments as employees?
Or do you have 45 employees that you need to assign randomly to 65 work
assignments?

The first (same number) is easy.

Ostensibly, create a column of 65 cells, say A1:A65, with the formula
=RAND(). Then create a column of 65 cells, say B1:B65, with the following
formula in B1 and copy it down:

=RANK(A1,\$A\$1:\$A\$65)

Pay close attention to the use of relative and absolute references.

Gotcha!.... The RAND() values will change every time you modify any(!) cell
in the workbook and every time you open the file, normally. Presumably that
is not what you want.

One simple way to avoid that is use the following macro and replace =RAND()
with =myrand():

Function myrand(Optional arg)
Dim first
If first = 0 Then Randomize: first = 1
myrand = Rnd
End Function

The optional arg makes it easy to generate a new set of random values.
Simply use =myrand(\$C\$1). Then, a new set of random values are generated
whenever C1 is changed (e.g. pressing Delete).

Once we have learned how to perform the random
re-shuffle above, we would also like to learn
how to tailor the random assignments as follows:

Applying these constraints are feasible. But they require, or at least are
best implemented by, a macro. A good design of that macro is non-trivial
and goes beyond the scope of what I can deal with here. My suggestion is
that you higher an experienced Excel/VB programmer to implement it.

FYI, a "bad design" is certainly easier. A "bad" design will iterate the
random selection until the necessary constraints are met. Such a design is
not guaranteed to work in a finite amount of time, and if it does work, it
may or may not take a very long time, depending on stochastic properties.
Caveat emptor!

----- original message -----

"Blue Max" wrote in message
news
We have 65 work assignments and must randomly re-assign them to our
employees each month. We have given each work assignment a number 1-65.
How do we randomly re-shuffle a list of 45 whole numbers at the beginning
of each month?

Once we have learned how to perform the random re-shuffle above, we would
also like to learn how to tailor the random assignments as follows:

First, how do we prevent an employee from being re-assigned the same
assignment they performed last month or perhaps the last 3 months?

Second, how do we prevent employees from receiving certain random numbers
that represent tasks they are not capable of performing? In other words,
how do we prohibit an employee from being assigned a subset of certain

Thank you for any help with this question.

#3
October 4th 09, 09:39 PM posted to microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Oct 2006 Posts: 113
Random generation of employee assignments

Thanks Joe. In our case, we have 65 employees and 65 unique schedule

using the 'Data Analysis' pack included with Excel? Does it include any

Thanks

*********************
"JoeU2004" wrote in message
...
"Blue Max" wrote:
We have 65 work assignments and must randomly re-assign
them to our employees each month. We have given each
work assignment a number 1-65. How do we randomly re-shuffle
a list of 45 whole numbers at the beginning of each month?

I'm confused. Do you have the same number of work assignments as
employees? Or do you have 45 employees that you need to assign randomly to
65 work assignments?

The first (same number) is easy.

Ostensibly, create a column of 65 cells, say A1:A65, with the formula
=RAND(). Then create a column of 65 cells, say B1:B65, with the following
formula in B1 and copy it down:

=RANK(A1,\$A\$1:\$A\$65)

Pay close attention to the use of relative and absolute references.

Gotcha!.... The RAND() values will change every time you modify any(!)
cell in the workbook and every time you open the file, normally.
Presumably that is not what you want.

One simple way to avoid that is use the following macro and replace
=RAND() with =myrand():

Function myrand(Optional arg)
Dim first
If first = 0 Then Randomize: first = 1
myrand = Rnd
End Function

The optional arg makes it easy to generate a new set of random values.
Simply use =myrand(\$C\$1). Then, a new set of random values are generated
whenever C1 is changed (e.g. pressing Delete).

Once we have learned how to perform the random
re-shuffle above, we would also like to learn
how to tailor the random assignments as follows:

Applying these constraints are feasible. But they require, or at least
are best implemented by, a macro. A good design of that macro is
non-trivial and goes beyond the scope of what I can deal with here. My
suggestion is that you higher an experienced Excel/VB programmer to
implement it.

FYI, a "bad design" is certainly easier. A "bad" design will iterate the
random selection until the necessary constraints are met. Such a design
is not guaranteed to work in a finite amount of time, and if it does work,
it may or may not take a very long time, depending on stochastic
properties. Caveat emptor!

----- original message -----

"Blue Max" wrote in message
news
We have 65 work assignments and must randomly re-assign them to our
employees each month. We have given each work assignment a number 1-65.
How do we randomly re-shuffle a list of 45 whole numbers at the beginning
of each month?

Once we have learned how to perform the random re-shuffle above, we would
also like to learn how to tailor the random assignments as follows:

First, how do we prevent an employee from being re-assigned the same
assignment they performed last month or perhaps the last 3 months?

Second, how do we prevent employees from receiving certain random numbers
that represent tasks they are not capable of performing? In other words,
how do we prohibit an employee from being assigned a subset of certain

Thank you for any help with this question.

#4
October 4th 09, 10:36 PM posted to microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jan 2007 Posts: 2,059
Random generation of employee assignments

"Blue Max" wrote:
How about using the 'Data Analysis' pack included
with Excel? Does it include any additional functionality?

Sorry, I'm not familiar with that.

we have 65 employees and 65 unique schedule configurations (not tasks)
that combine specific

I provided a solution for exactly what you asked for initially, namely:
"How do we randomly re-shuffle a list of 45 whole numbers [...]?"

But I suspect the following might be more useful.

Suppose you have a column of configurations in A1:A65 and a column of
employees in C1:C65. Fill D165 with the formula =myrand() or =RAND().
Then put the following formula into B1 and copy down through B65:

=INDEX(\$C\$1:\$C\$65,RANK(D1,\$D\$1:\$D\$65))

Again, pay close attention the use of relative and absolute references.

This randomly assigns employees in B1:B65 to respective configurations in
A1:A65.

----- original message -----

"Blue Max" wrote in message
...
Thanks Joe. In our case, we have 65 employees and 65 unique schedule

I appreciate your suggestions, especially the controllable macro. How
about using the 'Data Analysis' pack included with Excel? Does it include

Thanks

*********************
"JoeU2004" wrote in message
...
"Blue Max" wrote:
We have 65 work assignments and must randomly re-assign
them to our employees each month. We have given each
work assignment a number 1-65. How do we randomly re-shuffle
a list of 45 whole numbers at the beginning of each month?

I'm confused. Do you have the same number of work assignments as
employees? Or do you have 45 employees that you need to assign randomly
to 65 work assignments?

The first (same number) is easy.

Ostensibly, create a column of 65 cells, say A1:A65, with the formula
=RAND(). Then create a column of 65 cells, say B1:B65, with the
following formula in B1 and copy it down:

=RANK(A1,\$A\$1:\$A\$65)

Pay close attention to the use of relative and absolute references.

Gotcha!.... The RAND() values will change every time you modify any(!)
cell in the workbook and every time you open the file, normally.
Presumably that is not what you want.

One simple way to avoid that is use the following macro and replace
=RAND() with =myrand():

Function myrand(Optional arg)
Dim first
If first = 0 Then Randomize: first = 1
myrand = Rnd
End Function

The optional arg makes it easy to generate a new set of random values.
Simply use =myrand(\$C\$1). Then, a new set of random values are generated
whenever C1 is changed (e.g. pressing Delete).

Once we have learned how to perform the random
re-shuffle above, we would also like to learn
how to tailor the random assignments as follows:

Applying these constraints are feasible. But they require, or at least
are best implemented by, a macro. A good design of that macro is
non-trivial and goes beyond the scope of what I can deal with here. My
suggestion is that you higher an experienced Excel/VB programmer to
implement it.

FYI, a "bad design" is certainly easier. A "bad" design will iterate the
random selection until the necessary constraints are met. Such a design
is not guaranteed to work in a finite amount of time, and if it does
work, it may or may not take a very long time, depending on stochastic
properties. Caveat emptor!

----- original message -----

"Blue Max" wrote in message
news
We have 65 work assignments and must randomly re-assign them to our
employees each month. We have given each work assignment a number 1-65.
How do we randomly re-shuffle a list of 45 whole numbers at the
beginning of each month?

Once we have learned how to perform the random re-shuffle above, we
would also like to learn how to tailor the random assignments as
follows:

First, how do we prevent an employee from being re-assigned the same
assignment they performed last month or perhaps the last 3 months?

Second, how do we prevent employees from receiving certain random
numbers that represent tasks they are not capable of performing? In
other words, how do we prohibit an employee from being assigned a subset

Thank you for any help with this question.

#5
October 6th 09, 03:43 PM posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
 external usenet poster First recorded activity by ExcelBanter: Oct 2009 Posts: 12
Random generation of employee assignments

I have a solution that should work for you, and implements all the features
you wanted. Hopefully these steps are easy to follow. If not, let me know.

We need to first build the table that holds the tasks the the employees
cannot do. In a new workbook, on Sheet2, enter "EE - Task", "EE ID", and
"Task ID" into A1:C1. In A2, enter the formula "=B2&" - "&C2". In B2 and C2,
enter the Employee unique identifier (ee id, or SSN or possibly name), and a
SINGLE task id that that employee cannot complete. Fill out the rest of the
table by copying down the formula in column A and entering the EE ID and Task
ID. Name this whole table TaskTable (just select the whole table and type

Now for the task assignment page.
1. On Sheet1, type the following column headers into A1:H1 : Employee, Rand,
2. Type "=Rand()" into B2
3. Type "=RANK(B2,\$B\$2:\$B\$66)" into C3
4. Type "=AND(C2<D2,C2<E2,C2<F2)" into G2.
6. Copy row 2 down to row 66.
7. Cells A2:A66 need to uniquely identify the Employee. You can use EE IDs
or SSNs or whatever works for you. The values you entered in the TaskTable
must be a subset of these values.
8. Cells D2:F66 track the employees' three months history of task IDs. These
should be integers from 1 to 65.

Now we'll add the row that checks for a valid solution.
9. In A68, type "Found Solution?"
10. In C68, enter the formula "=SUM(C2:C66)=COUNT(C2:C66)*(COUNT(C2:C66)+1)/2"
11. In G68, enter the formula "=AND(G2:G66)"
12. In H68, enter the formula "=AND(H2:H66)"
13. In I68, enter the formula "=AND(C68,G68,H68)"

In order to generate a solution, hit the F9 key (which recaluates the
formulas, generating new random numbers). Watch cell I68. When this cell
shows TRUE, you have a valid solution. You may want to turn on Manual
Calculation so that the workbook doesn't automatically recalculate and reset

--
Rob Jordan

"Blue Max" wrote:

We have 65 work assignments and must randomly re-assign them to our
employees each month. We have given each work assignment a number 1-65.
How do we randomly re-shuffle a list of 45 whole numbers at the beginning of
each month?

Once we have learned how to perform the random re-shuffle above, we would
also like to learn how to tailor the random assignments as follows:

First, how do we prevent an employee from being re-assigned the same
assignment they performed last month or perhaps the last 3 months?

Second, how do we prevent employees from receiving certain random numbers
that represent tasks they are not capable of performing? In other words,
how do we prohibit an employee from being assigned a subset of certain tasks

Thank you for any help with this question.

#6
June 23rd 20, 04:11 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Oct 2003 Posts: 517
Random generation of employee assignments

Mukayi Mutsago wrote:

I have a similar problem to what you posted here except that all my
employees can do any task. Did you ever got this to work. If so can you
send me a copy of how you wrote your excel formulas. In Rob's example I
can find out how Sheet1 is linked to sheet2. I typed everything as he
said but my employees and tasks in sheet2 are not connected to sheet1 at
all. Let me know.

Dude. The original post was 11 years ago.

On Tuesday, October 6, 2009 at 11:41:23 PM UTC-4, Blue Max wrote:

^^^^^^^^^^^^^^^^^^^^^^^^

question? I for one am not will to go dig through the Google archives to read
the original posts.

--
A wise man distrusts his neighbor.
A wiser man distrusts both his neighbor and himself.
The wisest man of all distrusts his government.
#7
June 23rd 20, 04:13 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Oct 2003 Posts: 517
Random generation of employee assignments

Auric__ wrote:

Mukayi Mutsago wrote:

I have a similar problem to what you posted here except that all my
employees can do any task. Did you ever got this to work. If so can you
send me a copy of how you wrote your excel formulas. In Rob's example I
can find out how Sheet1 is linked to sheet2. I typed everything as he
said but my employees and tasks in sheet2 are not connected to sheet1
at all. Let me know.

Dude. The original post was 11 years ago.

On Tuesday, October 6, 2009 at 11:41:23 PM UTC-4, Blue Max wrote:

^^^^^^^^^^^^^^^^^^^^^^^^

question? I for one am not will to go dig through the Google archives to

figure it out for yourself. Replying after 11 years is silly.

--
If you can write out an idea in English,
you're halfway to writing it in C.

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Blue Max New Users to Excel 4 October 6th 09 03:43 PM Ann Excel Worksheet Functions 5 May 30th 08 06:52 PM Dawn Bjork Buzbee Excel Worksheet Functions 3 February 21st 07 04:17 PM carl Excel Worksheet Functions 4 May 23rd 06 02:18 PM pkbro Excel Worksheet Functions 1 June 21st 05 02:03 AM

All times are GMT +1. The time now is 02:12 AM.