Hi all,

I would like to be able to count the amount of entries in column C and depending on the amount group in either groups of 3 or 4, all names would be unique......so if there are 14 names in the list they would need to be grouped in to two groups of 4 and two groups of 3, if there were 19 then 4 groups of 4 and 1 group of 3 etc to a maximum 50 people, the results could appear on a seperate worksheet.

Below is how i generate the list of people and then randomise them and display the result (i have only used up to 24 in this test, the names come from sheet2 in my workbook)

Hope someone can help.......it seems very complex to group all the permutations!

Simon.

Option Explicit

Sub numberrand()

Call Players

Range("a1").Formula = "1"

Range("a2").Formula = "2"

Range("A1:A2").AutoFill Range("A1:A24")

Range("b1").Formula = "=RAND()"

Range("b1").AutoFill Range("B1:B24")

Range("B1:B24").Select

Columns("B:B").Select

Selection.Copy

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _

False, Transpose:=False

Rows("1:24").Select

Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Columns("b:b").Delete

Range("a1").Select

Call ListShow

End Sub

Sub Players()

Application.Goto Reference:="Players"

Selection.Copy

Sheets("Draw Order").Select

Range("D1").Select

ActiveSheet.Paste

End Sub

Sub ListShow()

Range("A1:E40").Select

Selection.Copy

Sheets("Results").Select

ActiveSheet.Paste

Application.CutCopyMode = False

Call SkipBlanks

End Sub

Sub SkipBlanks()

Selection.AutoFilter

Selection.AutoFilter Field:=3, Criteria1:="<>"

Call ClearOrder

End Sub

Sub ClearOrder()

Sheets("Draw Order").Select

Range("A1:E40").Select

Selection.ClearContents

Range("A1").Select

Sheets("Results").Select

End Sub

## Bookmarks