Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Jimmy
 
Posts: n/a
Default split out drivers in matches

Can anyone help me build a Table with this data:
16 speedway racing teams in Y axis.
40 mathces in the X axis.
They all only meet 2 times.
There is 4 teams in every match.
They all have to drive 10 matches.
- if it possible?
  #2   Report Post  
Otto Moehrbach
 
Posts: n/a
Default

Jimmy
You can't meet all the conditions you specify. The condition that any 2
teams can meet no more than 2 times in the 40 matches is the primary driver.
Secondary to that is that all matches have no more than 4 teams. Mind
you, that says that all matches have no more than 4 teams, not that all
matches have 4 teams. Finally comes that each team races no more than 10
matches. Again, not 10 matches but no more than 10 matches.
This produces 33 matches with 4 teams, 6 matches with 3 teams, and one
match with 2 teams.
Seven teams race 10 times, 2 teams race 9 times, 6 teams race 8 times,
and 1 team races 7 times.
The macros that produce these numbers are shown below.
If you send me a good email address for you, I'll send you the file I built
to do this. My email address is . Remove the
"Cobia97 from this address. HTH Otto

Dim MatchRng As Range ' All 16 cells in each match
Dim TeamRng As Range 'The 16 teams in Column A
Dim i As Range 'Each team in TeamRng
Dim j As Range 'Each team in MatchRng
Dim ChkRngForDupPairs As Range
Dim c As Long 'Match counter
Dim d As Long 'Match counter for equivalent pairs
Dim e As Long 'Counter for equivalent pairs
Sub SetupTeams()
Set TeamRng = [A2:A17]
'Loop through all 40 matches
For c = 1 To 40
Set MatchRng = Range(Cells(2, c + 1), Cells(17, c + 1))
'Loop through all the teams
For Each i In TeamRng
If Application.CountIf(Range("B2:AO17"), i.Value) 9 Then _
GoTo NextTeam
e = 0

'If team appears twice in same match, go to next team
If Application.CountIf(MatchRng, i.Value) 1 Then GoTo NextTeam

'If # of teams in match 0 then chk for duplicate pairs in all
'matches, max is 2
If Application.CountA(MatchRng) 0 Then
For Each j In MatchRng
If j.Value = "" Then GoTo NextjCell
For d = 1 To 40
Set ChkRngForDupPairs = Range(Cells(2, d + 1),
Cells(17, d + 1))
'MsgBox ChkRngForDupPairs.Address
If Not ChkRngForDupPairs.Find(i.Value) Is Nothing
Then _
If Not ChkRngForDupPairs.Find(j.Value) Is
Nothing Then _
e = e + 1
If e 1 Then GoTo NextTeam
Next d
NextjCell:
Next j
End If
Cells(i.Row, c + 1).Value = i.Value
If Application.CountA(MatchRng) = 4 Then GoTo NextMatch
NextTeam:
Next i
NextMatch:
Next c
End Sub


"Jimmy" wrote in message
...
Can anyone help me build a Table with this data:
16 speedway racing teams in Y axis.
40 mathces in the X axis.
They all only meet 2 times.
There is 4 teams in every match.
They all have to drive 10 matches.
- if it possible?



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
How can I split a cell in Excel? LI Associate Excel Discussion (Misc queries) 1 December 15th 04 12:35 AM
Split large bar in a graph? Neronimo Charts and Charting in Excel 4 December 9th 04 03:41 PM
Split combined date time data Mark Ada Excel Discussion (Misc queries) 1 December 1st 04 06:55 AM
Split combined date time data Mark Ada Excel Discussion (Misc queries) 1 December 1st 04 04:52 AM
split combined Time Date cells Mark Ada Excel Discussion (Misc queries) 2 December 1st 04 04:06 AM


All times are GMT +1. The time now is 06:49 PM.

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

About Us

"It's about Microsoft Excel"