Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 2nd 10, 02:27 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 10
Default Creating a fixture list

I have used a macro to gererate fixtures for a league but it doesn't
calculate properly. All teams should play each other once, (ie 10 teams - 90
matches). I have used the following macro. Thanks for your help in advance.

Sub leagueMatch()
Dim i, j As Long
Dim datacolumn As String
Dim resultColumn As String
Dim firstRow, lastRow, currRow As Long

datacolumn = "A"
resultColumn = "C"
firstRow = 1
currRow = 1
lastRow = Range(datacolumn & Rows.Count).End(xlUp).Row

For i = firstRow To lastRow - 1
For j = i + 1 To lastRow
Range(resultColumn & Format(currRow)) = Range(datacolumn &
Format(i)) & " vs " & Range(datacolumn & Format(j))
currRow = currRow + 1
Next
Next
End Sub


  #2   Report Post  
Old January 2nd 10, 02:47 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2008
Posts: 1,533
Default Creating a fixture list

Hi

This will give you 90 matches. Each team will play against each other twice
(as host and as guest).

Sub leagueMatch()
Dim i, j As Long
Dim datacolumn As String
Dim resultColumn As String
Dim firstRow, lastRow, currRow As Long

datacolumn = "A"
resultColumn = "C"
firstRow = 1
currRow = 1
lastRow = Range(datacolumn & Rows.Count).End(xlUp).Row

For i = firstRow To lastRow
For j = firstRow To lastRow
If Cells(i, datacolumn) < Cells(j, datacolumn) Then
Range(resultColumn & Format(currRow)) = Range(datacolumn &
Format(i)) & " vs " & Range(datacolumn & Format(j))
currRow = currRow + 1
End If
Next
Next
End Sub

Regards,
Per


"Gilbo" skrev i meddelelsen
...
I have used a macro to gererate fixtures for a league but it doesn't
calculate properly. All teams should play each other once, (ie 10 teams -
90
matches). I have used the following macro. Thanks for your help in
advance.

Sub leagueMatch()
Dim i, j As Long
Dim datacolumn As String
Dim resultColumn As String
Dim firstRow, lastRow, currRow As Long

datacolumn = "A"
resultColumn = "C"
firstRow = 1
currRow = 1
lastRow = Range(datacolumn & Rows.Count).End(xlUp).Row

For i = firstRow To lastRow - 1
For j = i + 1 To lastRow
Range(resultColumn & Format(currRow)) = Range(datacolumn &
Format(i)) & " vs " & Range(datacolumn & Format(j))
currRow = currRow + 1
Next
Next
End Sub


  #3   Report Post  
Old January 2nd 10, 03:36 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 10
Default Creating a fixture list

Thanks, It does exactly what I want.

"Gilbo" wrote:

I have used a macro to gererate fixtures for a league but it doesn't
calculate properly. All teams should play each other once, (ie 10 teams - 90
matches). I have used the following macro. Thanks for your help in advance.

Sub leagueMatch()
Dim i, j As Long
Dim datacolumn As String
Dim resultColumn As String
Dim firstRow, lastRow, currRow As Long

datacolumn = "A"
resultColumn = "C"
firstRow = 1
currRow = 1
lastRow = Range(datacolumn & Rows.Count).End(xlUp).Row

For i = firstRow To lastRow - 1
For j = i + 1 To lastRow
Range(resultColumn & Format(currRow)) = Range(datacolumn &
Format(i)) & " vs " & Range(datacolumn & Format(j))
currRow = currRow + 1
Next
Next
End Sub

  #4   Report Post  
Old January 2nd 10, 03:51 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 10
Default Creating a fixture list

Now you have work out the fixtures, would it be possible to work out the
matches for each week with another macro,
ie 12 teams 6 matches
Team1 v Team2
Team3 v Team4
Team5 v Team6
Team7 v Team8
Team9 v Team10
Team11 v Team12
So that every team plays every other team twice, and all teams play every week

"Gilbo" wrote:

Thanks, It does exactly what I want.

"Gilbo" wrote:

I have used a macro to gererate fixtures for a league but it doesn't
calculate properly. All teams should play each other once, (ie 10 teams - 90
matches). I have used the following macro. Thanks for your help in advance.

Sub leagueMatch()
Dim i, j As Long
Dim datacolumn As String
Dim resultColumn As String
Dim firstRow, lastRow, currRow As Long

datacolumn = "A"
resultColumn = "C"
firstRow = 1
currRow = 1
lastRow = Range(datacolumn & Rows.Count).End(xlUp).Row

For i = firstRow To lastRow - 1
For j = i + 1 To lastRow
Range(resultColumn & Format(currRow)) = Range(datacolumn &
Format(i)) & " vs " & Range(datacolumn & Format(j))
currRow = currRow + 1
Next
Next
End Sub



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
making a fixture list [email protected] Excel Discussion (Misc queries) 1 September 19th 06 01:58 PM
Random Grouping/Fixture List?? Paul Mc Excel Worksheet Functions 0 March 22nd 06 02:31 PM
Fixture list in excel? Paul Mc Excel Discussion (Misc queries) 0 March 22nd 06 02:31 PM
HOW DO I SET UP A 12 TEAM FIXTURE TABLE PLAYING EACH OTHER ONCE BRIAN MURPHY Excel Discussion (Misc queries) 0 May 30th 05 07:20 PM
Creating fixture lists Frazer Snowdon Excel Worksheet Functions 11 April 23rd 05 03:16 PM


All times are GMT +1. The time now is 01:56 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017