Need help with team lists

[sparky]

Hello All,

I am stuck since a few days on this workbook where I am trying automate the reverse engineering of existing team lists. Without confusing anyone further, here are the steps of what I need to accomplish.

Context -- The workbook contains team lists, it can have any number of teams and participants in each team. My task is to be able to automate the action of dividing the participants of each team equally across the other teams.

As an example,

Team 1Team 2Team 3Team 4
SamGeorgeJaneMartin
JackIanCarlYvette
HollyJohnRickAndrew


Team 1Team 2Team 3Team 4
SamJackHollyGeorge
IanJohnJaneCarl
RickMartinYvetteAndrew


There can be any number of teams and any number of participants (including uneven participants in each team).
I want my formula to lookup and return the participant names and number of teams, following this it must automatically populate the new list. Is this asking too much? Is it possible?

I tried to use transpose to reverse a major chunk of the list and then shift the last few names manually but it is not the best process.

Sorry I'm new to excel, please help me!

 

[Hui]

Sparky

Firstly, Welcome to the Chandoo.org Forums

Can you please upload a sample file , use the Upload a File Button below

 

[ismailzkhan]

Hi Sparky,

I face same problem earlier and taken help from this following link.

http://www.get-digital-help.com/2010/09/30/team-generator-in-excel/

Hope this will solve your purpose.

Regards,
I Khan

 

[sparky]

Thank you Hui! Here is the file, please refer to Sheet 1 for the example team lists I need to create.
There can be any number of participants and any number of teams.. logically I think first I would need to perform the following steps,
Count the number of teams and participants.
Create a master list of the participants
From the master list, drop names in the teams (first name in team 1, second name in team 2 and so on..)
Assume there are only 4 teams. So when the 4th name goes into team 4, the formula needs to recognise that there is no 5th team, so the 5th name goes into team 1 again.
And so on...

But I'm not sure what formulae I need to use to get this done.

It would be awesome if you could help me out with this.

 

[SirJB7]

Hi, sparky!

That sounds like a dog trying to bit his tail.

Please specify, indicating worksheet and range where are:
a) The input data, either if it is a team members distribution or a full member list to be distributed among the teams.
b) The team list, if the input data is the master member list.
c) The criteria for building the master member list, if not the input data.

Regards!