Team Role Rotation (number of team members is variable)

[Guest]

In our company we have team meetings in each workgroup, and have a team role
rotation for those meetings. The number of members on each team is different.

What I'm trying to do is build a formula that looks at the list of members,
determines the length of the member list (counta) and then builds a rotation
list. The rotation list is just the list of members repeated say five times
over in the same sequence. I'll use that list to populate a matrix. Would
like to do this with worksheet functions if possible.

Any suggestions would be helpful.

Thanks in advance!

Scott

Example: member list
John Doe
Bill Smith
Susie Brown
Jack White

Example: rotation list
John Doe
Bill Smith
Susie Brown
Jack White
John Doe
Bill Smith
Susie Brown
Jack White
John Doe
Bill Smith
Susie Brown
Jack White

 

[Pete_UK]

I'm not sure what you are getting at, but here is another approach you
might like to consider:

Role_1 John Doe Jack White Susie Brown
Role_2 Bill Smith John Doe Jack White
Role_3 Susie Brown Bill Smith John Doe
Role_4 Jack White Susie Brown Bill Smith

After each meeting the members adopt their role plus one, with the
person in Role_max adopting Role_1.

Hope this helps.

Pete

 

[Guest]

That's exactally what I'm trying to do. The only issue is that the number of
members in the workgroup may be 4, 9, 11, or any number. (less then 20
usually). The number of team roles is always 4.

How to I get that rotation, with the variable number of group members?

Can you help?

Thanks,

Scott

 

[Pete_UK]

Ok, well imagine you have the following covering A1:B11

Role_1 Person_A
Role_2 Person_B
Role_3 Person_C
Role_4 Person_D
Role_1 Person_E
Role_2 Person_F
Role_3 Person_G
Role_4 Person_H
Role_1 Person_I
Role_2 Person_J
Role_3 Person_K

This is for a team of 11 members. In C1 enter the formula:

=B11

and in C2:

=B1

Copy the formula in C2 down into C3:C11 by double-clicking the fill
handle (the small black square in the bottom right corner of the cursor
with C2 selected).

Now highlight cells C1 to C11, click <copy>, then highlight cells D1:H1
(or however many rotations you need) and press <Enter>.

There is your grid. The only difference for smaller groups is what you
enter in C1 initially (i.e. it should point to the last person in
column B for that size of group. You could have different groups in
different sheets, or leave a few blank rows between the groups and have
them arranged under one another.

Is this what you want?

Pete