Hi all,
I have a challenge that has me stumped.
I have a sheet with the entrants into an event. The rules say that the entrants order of competition must be randomly drawn.
I have this nearly nailed. I've got one step left....
If once all the entries are in I have a list that looks like this:
Currently I have procedure which generates a random number in each line in column M.
If I don't have enough entries to fill the last squad (eg. Squad 3 here) I place a filler in the remaining boxes to make a complete squad, then I sort the list by the random numbers down to the last squad filled.
I may have to repeat the routine a number of times, until I have an even spread of "Filler". Eg in this case ideally only one to each squad.
I guess my worst case scenario above would be that squad 3 only has 2 in it, and 4 fillers. Then I'd need 2 squads of 5 & one of 4, instead of three squads of 5.
5 fillers and 13 shooters isn't out of the question but would be a pain.
Currently I manually repeat the macro below until one of the outcomes meets my criteria.
I know it's a tall ask but if someone is up to a weekend challenge I'd really like to know how to do the following:
1. Create the optimum mix of squads eg 1 filler in each squad in scenario in image OR 1 in each of first 2 squads and 2 in the third if I had another filler. (ie. run the sort, test to see if there's one filler in each squad, repeat the sort if not until the criteria is met - however it can't crash if the mix means that an extra filler is needed in one or two squads because entry numbers dictate it.)
2. I'd really like to end up with the fillers last in each squad. By giving them athlete number 9999 I'm figuring just sort at the end by number. I guess ultimately I'll need to select a range and run a sort on squad 1, move down 6 cells to select a new range to sort squad 2 and so on down to squad 15.
The current routine I'm using to randomise the list is:
' Select all competitors on qualification sheet for random sort.
' Requires last squad to contain "Filler" in each vacant cell.
Sub Select_Qual_athletes_for_sort()
Range("M7:M96").Formula = "=RAND()"
Range(Range("M7"), Range("M7").End(xlDown)).Select
Selection.Copy
Range("M7").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range(Range("D7"), Range("D7").End(xlDown)).Select
' Worksheets("Sheet1").Activate
numRows = Selection.Rows.Count
numColumns = Selection.Columns.Count
On Error Resume Next
Selection.Resize(, Selection.Columns.Count + 10).Offset(, -1).Select
With ActiveSheet.Sort
.SortFields.Clear
.SortFields.Add Key:=Selection.Columns(11), Order:=xlAscending
.Header = xlNo
.SetRange Selection
.Apply
End With
Range("A1").Select
Application.CutCopyMode = False
End Sub
Any ideas?
Thanks,
Dave
I have a challenge that has me stumped.
I have a sheet with the entrants into an event. The rules say that the entrants order of competition must be randomly drawn.
I have this nearly nailed. I've got one step left....
If once all the entries are in I have a list that looks like this:
Currently I have procedure which generates a random number in each line in column M.
If I don't have enough entries to fill the last squad (eg. Squad 3 here) I place a filler in the remaining boxes to make a complete squad, then I sort the list by the random numbers down to the last squad filled.
I may have to repeat the routine a number of times, until I have an even spread of "Filler". Eg in this case ideally only one to each squad.
I guess my worst case scenario above would be that squad 3 only has 2 in it, and 4 fillers. Then I'd need 2 squads of 5 & one of 4, instead of three squads of 5.
5 fillers and 13 shooters isn't out of the question but would be a pain.
Currently I manually repeat the macro below until one of the outcomes meets my criteria.
I know it's a tall ask but if someone is up to a weekend challenge I'd really like to know how to do the following:
1. Create the optimum mix of squads eg 1 filler in each squad in scenario in image OR 1 in each of first 2 squads and 2 in the third if I had another filler. (ie. run the sort, test to see if there's one filler in each squad, repeat the sort if not until the criteria is met - however it can't crash if the mix means that an extra filler is needed in one or two squads because entry numbers dictate it.)
2. I'd really like to end up with the fillers last in each squad. By giving them athlete number 9999 I'm figuring just sort at the end by number. I guess ultimately I'll need to select a range and run a sort on squad 1, move down 6 cells to select a new range to sort squad 2 and so on down to squad 15.
The current routine I'm using to randomise the list is:
' Select all competitors on qualification sheet for random sort.
' Requires last squad to contain "Filler" in each vacant cell.
Sub Select_Qual_athletes_for_sort()
Range("M7:M96").Formula = "=RAND()"
Range(Range("M7"), Range("M7").End(xlDown)).Select
Selection.Copy
Range("M7").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range(Range("D7"), Range("D7").End(xlDown)).Select
' Worksheets("Sheet1").Activate
numRows = Selection.Rows.Count
numColumns = Selection.Columns.Count
On Error Resume Next
Selection.Resize(, Selection.Columns.Count + 10).Offset(, -1).Select
With ActiveSheet.Sort
.SortFields.Clear
.SortFields.Add Key:=Selection.Columns(11), Order:=xlAscending
.Header = xlNo
.SetRange Selection
.Apply
End With
Range("A1").Select
Application.CutCopyMode = False
End Sub
Any ideas?
Thanks,
Dave
Last edited:
