Randomize the teams

[Quicksnot]

Hi again all

I can't even begin to figure out a schedule for the 20 teams I have in my pool league.
I just can't seem to figure it out, they place each of the teams twice, once at home and once away.

any ideas how to randomize that?

Thanks
Keith

[mehmetcik]

list all 20 teams in a column A in rows 1 to 20

enter a random number in the next column

Please Login or Register  to view this content.

sort by the random number

Delete the list of random numbers

copy your list across 18 columns offsetting by one row in each column.

this formula will do that for you/

Please Login or Register  to view this content.

You have a table that shows the order of matches for the team listed in column A.

Lets say that that is the teams home matches.

Create another table following the same process for the away matches,

[Speshul]

Not sure if this solves anything, but it makes random numbers!

Random Even number between 1-20

Please Login or Register  to view this content.

Random Odd Number between 1-20

Please Login or Register  to view this content.

[FDibbins]

Not sure if I understand what you want, but try this...

I created a table off to the side like this...

	AH	AI
2	10647	1
3	36047	2
4	35969	3
5	54681	4
6	34829	5
7	22588	6
8	64175	7
9	91013	8
10	87483	9
11	30058	10

AH2=RAND()*100000
I only copied down to 10, but it goes down to 20

Then in A10, copied down...
=VLOOKUP(LARGE($AH$2:$AH$21,MOD(ROW(A1),13)+MOD(ROW(A1),13)*1-2+1),$AH$2:$AI$21,2,0)
Then copy this to C10, and copy down...
=VLOOKUP(LARGE($AH$2:$AH$21,MOD(ROW(A1),13)+MOD(ROW(A1),13)*1-2+1),$AH$2:$AI$21,2,0)

Once you have the 1st 2 sets of columns done, you can copy that "set" to all weeks

[Quicksnot]

sorry guys I was gone in a fishing tournament, but so far were close.

its probably the way I'm explaining it, 20 TEAMS, they have to play each other twice, once at HOME and once AWAY.

I can't find any team generators for excel, so am hoping someone could make some type of formula that will work,
below is the current schedule we use, but its not made from an excel based program, I know there is only 32 weeks
shown, its because we cut off the weeks we didn't need at the end, its not quite a fair schedule since some teams don't
play some of the teams twice. but we ended up with extra teams and didn't have the heart to tell them they couldn't get
in, and for the program I use for the standings made from excel, I need to have a schedule made from excel to reference
back to, to show who beat who that week without manually entering it in.

If I can solve this problem 1st, then I have to repost on how to automatically sort who beat who, and by how many games.
but that will be a huge challenge, later.

Thanks
Keith