Non-repeating sports league schedule generator

Right now I have a sports league with 8 teams, I'd like to create a random 7-game schedule where each team plays the other team only once. Essentially this will be a 7-game round robin. However, I'd like to be able to use this for any number of teams and games.

I'd like to do this in Excel, but I can't figure out how to have a randomly generating non-repeating macro with text values in cells.

So right now I have a column of 8 values and need matrix of 7 columns by 8 rows next to it.

I've found this thread that has one for numbers, but I can't figure out how to do it for text values...

http://www.excelforum.com/excel-prog...m-numbers.html

Thanks all!

Attached is a 2-dimensional matrix random generator macro which sponges off of the link you provided. But the trouble is that it doesn't work like you would like. As the numbers get generated & placed in the table, the macro gets stuck where, for the latter cells, all the numbers are "taken" (if you look in both the column & row directions) and no valid number can be used. Run this macro and study the results and you will see what I mean. I would scrap this approach.

On the next tab (Option2), I setup a table that uses formulas to generate a tourney line-up. The formulas can be used for any size matrix. I have a 10x9 (rxc) matrix populated, but you only see the 8x7 matrix for ths 8 team option. The outcome of these formulas follow a basic ordered pattern: Team1 plays Team2 - Team8 in games 1-7, and Team2 would play Team3 - Team8, then play Team1 at the end, and Team3 would play Team4 - Team8, then play Team1 - Team2 at the end, and so forth. I would use this same pattern for every tournament. But, then just scramble the order of the teams in the 'A' column and you will have a random generated line-up.

Note, that the formulas in row1 of the matrix are different then the other rows. This top row formulas only look at the cell column. The rows below then reference the values in their preceeding row above.

The TeamName() and TeamNum() functions only serve the purpose of converting the Team Number (sequential number as listed in column 'A') to its corresponding Team Name, or visa-versa.

Note: The Opt2 macro requires setting up a Range Name ("Team.Rng") . This Range Name can be defined from A2 down to as far as you want to go (for whatever max size roster you'd expect). If the Team roster will always be in column 'A', then you can replace this "Team.Rng" with "A:A". ... sauerj

shg's worksheet points out some glaring errors in my thinking. My structure set up a team to play two games in the same round (boo!). Thanks shg for your clear thinking. kabnt2005, neglect my entry. shg's wkb should work well. It even accommodates an ODD number of teams. Play Ball! ... kabnt2005, If you need further help, holler!

Thanks everyone for the help.

While I would have preferred a truly random schedule generator instead of just a rotating one, this will work.

I Noticed how well these programs work for only 2 teams per match... Is there anyhting that can make a schedule that has 3 teams per match and nobody ever plays each other more than once.

cms541, I see you are new to the forum. Take a moment and glance at the forum rules (click the Forum Rules at very top). See rule #2; you'll get a better response if you follow that rule. To start a new thread. Go to "Forum / Excel General" and click the "Post New Thread" button. Ask your question this way and in that post, include a LINK to this thread.
Addressing your question: I don't understand it. Can you not use SHG's worksheet and type '3' in cell B2 (for a 3 team tourney)? Or, do you mean something else?
In your new post, attach a worksheet with an example tourney scheduled approximately with the structure that you mean. That will help others understand and get you an answer faster. ... good luck, sauerj