How would I group a list of inputted names into groups of five ?

[palotz]

I'm going to be coaching kids hockey team and I would like to insure fair play for all. I would like to create a shift by shift print out for each game. I would input a list of players attending and I would like them automatically grouped into five by just going down the list of names in order and repeating when getting to end of list. Amount of players would vary but never more then 15.



Input this:

Players:

Jim
Tony
Fred
Bobby
Tyler
Seth
Bill
Carl

and end up with something like this:

Jim
Tony
Fred
Bobby
Tyler

Seth
Bill
Carl
Jim
Tony

Fred
Bobby
Tyler
Seth
Bill etc................


Simple but I'm a newbie so please help me!!!!!


Cheers!


Anthony

[amotto11]

Anthony, your headline does not fit the forum rules. very simple does not tell us what your problem is. You will probably need to change the name of your post to something more descriptive.

On another note, how many groups of five people would you like?

[protonLeah]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

[amotto11]

here is the code that i believe that you will need.

Please Login or Register  to view this content.

If A1 is the heading players and column A is all of your players listed. Then C1 is the heading Group. this will look in column a and separate your players into groups of 5. To put this in as a module press alt+F11 and insert a module and copy this code in there, then just run. Let me know if this is what you are looking for

[palotz]

Thanks amotto, there will be 15 groups of five.

[palotz]

what if I wanted 3 groups of five in column C, 3 in column F, 3 in column I, 3 in column L, and 3 in column O. and give each group a title "Shift 1" "Shift 2" " Shift 3" "Shift 4" etc.......

[amotto11]

This is what you will want then

Please Login or Register  to view this content.

Still Put Players as a header in A1 and list your players in Column A after that. then as a Header in C, F, I, L, O... put Group 1, Group 2... or whatever header you would like. If you run this code it should give you what you want.

[palotz]

Once again thanks amotto,

How would I be able to keep 15 groups in these 5 columns ?

C F I L O
5 5 5 5 5 <<<< group 6 starts back at C
5 5 5 5 5 <<<<< group 11 starts back at C
5 5 5 5 5

Hope I explained it clearly above.

Cheers!

Anthony

[amotto11]

would you like a blank row in between group 1 and 6 in column C?

[palotz]

Yes, maybe 2. Also between 6 and 11.

---------- Post added at 09:24 AM ---------- Previous post was at 09:21 AM ----------

Thanks for being understanding of my lack of knowledge in excel. I'm slowly working through each line of the code and trying to understand what they do.

[JBeaucaire]

This little macro assumes all the names are in column A starting at A1, and the rest of the sheet is blank. It will randomly create groups of members based on the answer to the popup that occurs at the beginning, the default is 5 so you can just press ENTER.

The groups will be created in rows starting at D1.

Please Login or Register  to view this content.

Each time you run the macro you will get a completely different set of names.

[amotto11]

This should be what you want. Let me know

Please Login or Register  to view this content.

[palotz]

Exactly what I wanted!!! What would I change to add another row in between each five groups ?

Cheers!

Anthony

[amotto11]

where i have gr*7 you could do gr*8 or so on and so forth

---------- Post added at 08:50 AM ---------- Previous post was at 08:50 AM ----------

Don't forget to mark the thread as solved, if that is what you are looking for, the instructions are in my signature.

[palotz]

Excellent!

Big help amotto.

Anthony