PDA

View Full Version : Need code added to a button to list some numbers

daniels012
12-11-2008, 01:55 PM
I need code to attach to a button!

In cell A1 I enter number of teams.
In Cell A2 I enter number of games to play.
Is there a way for me to click a button and...
In columns A and B it will give me a list of teams to play each other
based on the criteria above.
In Column A will be home team and B is Visitor.

Example I enter 9 teams with 12 games to play, hit the button and it will list the games in columns A and B.

If I need to explain more, I can.
Any help on this would be great!

Michael

nst1107
12-11-2008, 02:54 PM
If I knew the first thing about how to do this manually, I'm sure I could come up with the code. Are there rules about how teams can be matched, or is it just random? And where is your list of teams?

daniels012
12-12-2008, 07:49 AM
The list of teams can be just the numbers needed.

For example:
If I enter in A1 "9" (the number of teams) and in A2 "12" (the number of games).
Also, I did not clarify! Column A represents home games and Column B is Away games.
So....

Code would somehow come up with something like:
Column A Column B
1 2
3 4
5 6
7 8
9 1
2 8
3 7

etc. etc.
It would evenly match up the teams for home and away games up to 12 games.

Does this make more sense?
Michael

MaximS
12-12-2008, 08:53 AM
Just to clarify:

Is that kind of the league creator??

Number of games - is that total of games played by each team, number of times played against each opponent, number of times played against each opponent divided by two (one game at home, one away)?

daniels012
12-12-2008, 09:15 AM
Is that kind of the league creator??

Yes it IS!

Number of games - is that total of games played by each team
Yes, each team must play 12 games.

number of times played against each opponent divided by two (one game at home, one away)?
All I want here is to even out home and away in column A and Column B respectfully.

Michael

MaximS
12-12-2008, 09:33 AM
How many teams will play in your league??

Is it always only 12 games (6 home / 6 away)

daniels012
12-12-2008, 09:59 AM
No, The value in A2 will reflect the outcome.
So if it comes out to 7 home and 6 away, that is fine. I just don't want something like 8 home and 5 away

Also, the number of teams will vary as well. The value in A1 will determine this.
Michael

daniels012
12-15-2008, 06:51 AM
Try to make them as even as possible

daniels012
12-19-2008, 01:17 PM
bump

daniels012
12-22-2008, 11:43 AM
I guess I need a sort of Do/Until it reaches the number of games.
Ideas?

Micahel

MaximS
12-23-2008, 12:19 AM
I'm working on it but it's pretty hard to find enough time before christmas :)

daniels012
12-29-2008, 07:05 AM
ok,
My appologies

daniels012
01-14-2009, 08:04 AM
ideas?

daniels012
01-14-2009, 08:40 AM
I tried this code with 4 teams in A1 and 12 games in A2:

Dim TEAM(10000) As Single
With Range("A3:B290")
.ClearContents
End With
Range("A3").Select
MY_TEAMS = Range("A1").Value
MY_GAMES = Range("A2").Value

MY_TEAM = 1
MY_TEAM_2 = 2

Do Until MY_TEAM = MY_TEAMS And MY_TEAM_2 >= MY_TEAMS

Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = MY_TEAM
Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = MY_TEAM_2

TEAM(MY_TEAM) = TEAM(MY_TEAM) + 1
TEAM(MY_TEAM_2) = TEAM(MY_TEAM_2) + 1

If MY_TEAM_2 = MY_TEAMS Then
MY_TEAM_2 = MY_TEAM + 1
Else
MY_TEAM_2 = MY_TEAM_2 + 1
End If

If TEAM(MY_TEAM_2) = MY_TEAMS Then
MY_TEAM_2 = MY_TEAM + 1
End If

If TEAM(MY_TEAM) = MY_GAMES Then
MY_TEAM = MY_TEAM + 1
MY_TEAM_2 = MY_TEAM + 1
End If

If MY_TEAM_2 = MY_TEAM Then MY_TEAM_2 = MY_TEAM + 1

Loop

But it does not even the teams games between Column A and Column B, also it doesn't stop it goes on forever.??

Ideas?
Michael

daniels012
01-14-2009, 02:17 PM
Ideas?

mikerickson
01-14-2009, 06:28 PM
This is a demo of a permuations class that I wrote.
The button TEGWAR (The Exiciting Game Without Any Rules), randomly assigns players to teams and then schedules those teams against each other. The number of games is not determined by the user (per se) but by the number of games needed for each team to play each opponent once.

It's not the most efficient routine. Its designed to show off a generalized permutation class and generalized methods are not as efficient as specific ones.

To get a 12 team schedule, tell the first input box that 12 people want to play and tell the second box that there is 1 person per team. An 11 week round robin schedule will be produced.

mikerickson
01-15-2009, 12:58 AM
This is a more focused routine.

The notion is:
Consider a table with people sitting across from each other
A B C
D E F
if everyone shifts clockwise one seat, except for one person (F in this example)
D A B
E C F
and follows that pattern
E D A
C B F

C E D
B A F

B C E
A D F

eventualy everyone will have sat across the table from everyone else only once. (Played against everyone.)
Futhermore, everyone will have spent (approx) half their time on one side of the table and half on the other. (50% +/- home, 50% away).

(If F switches sides of the table each time, F also has the 50/50)

That's the approach of the attached routine.

Sheet 1 shows a spreadsheet approach. Each block of formulas is copy pasted to the right.

The button on Sheet 2 calls an input box which asks how many teams and a schedule is drawn. If there are an odd number of teams, a team named "bye" is added to the league.

daniels012
01-15-2009, 08:12 AM
This is a great start. I need them to have 12 games. I used your ScheduleTeamSS file, It only provided 8 games, is there something we can incorporate to give the 12 games?
Michael

mikerickson
01-15-2009, 01:16 PM
Navigate to sheet 2, press the button and enter 12.

If you want the fixed team count spreadsheet version (sheet1) to be increased to 12, I can do that after work.
(He says as he plots a fiendishly un-editable way to make it variable sized rather than fixed team count :) )

mikerickson
01-15-2009, 05:56 PM
To do that, with Sheet1, copy F1:J8 and paste it, leaving one blank column between pasting blocks, into AD1, AJ1, AP1, etc.

Note: 12 teams with 12 games means that each team plays another team twice, but only one other team.

mikerickson
01-15-2009, 06:50 PM
I came up with a spreadsheet solution to the problem of scheduling a variable number of teams. It uses dynamic named ranges and array formulas.

Put all the team names in column A of sheet Sched, starting in row 1, no header and no blank rows.

Name : teamList
RefersTo: =OFFSET(Sched!\$A\$1,0,0,COUNTA(Sched!\$A:\$A),1)

Select C1 and define
Name: LastWeeksListH
RefersTo: =OFFSET(Sched!A\$1,0,0,ROWS(teamList),1)

Select D1 and define
Name: LastWeeksListV
ReferstTo: =OFFSET(Sched!A\$1,0,0,ROWS(teamList),1)

(LastWeeksListH and LastWeeksListV look the same, but since they use relative referencing and different cells were active when they were defined, they refer to different ranges.)

Then in C1:C20, this array formula will give you a list of the home teams, for week 1
=IF(ROW(teamList)<=ROWS(teamList)/2,LastWeeksListH,"")

In D1: D20, this will give you the Visitor teams
=IF(ROW(teamList)<=ROWS(teamList)/2,INDEX(LastWeeksListV,ROWS(teamList)-ROW(teamList)+1,1),"")

(Array formulas are confirmed with Ctrl-Shift-Enter or Cmd+Return on Mac.)

The ranges LastWeeksListH and V both refer to column A in that postion. What these formulas do is select Home / Visitor from the list of all teams 2 (or 3) columns to the left.

To get next week's games, put
=A1 in cell F1
=INDEX(A:A,ROWS(teamList),1) in F2
=A2&"" in F3 and drag down to F40.

This is the equivalent of every one sitting at the table shifting clockwise once (except for Team 1)

Copying C1: D20 and pasting into H1:I20 will pull data from column F to show the Home vs. Away for Week 2.

Copy F1:I40 and paste into K1 to get week 3 and continue for as many weeks as desired.

You can see the attached.
VB is better for the variable number of teams situation, but Excel2008 users will be glad to know that it can be done with native Excel.