How to count and group items in a list depending on size of list?

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 say pasted on to the worksheet starting with the groups of 3 (so paste a group of 3 then skip 3 rows then paste groups of 4 skip 2 rows, the row skipping is to allow seperation and manual entry of extra data).

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

I don't get it. What is Players, where is it, why do you allocate a random
number, what is Draw Order, Results, etc., etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...
>
> 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 say pasted on to the
> worksheet starting with the groups of 3 (so paste a group of 3 then
> skip 3 rows then paste groups of 4 skip 2 rows, the row skipping is to
> allow seperation and manual entry of extra data).
>
> 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
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=571041
>

Yeah sure, just note my signature.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...
>
> Sorry Bob, you should know me by now for my confused statements, is it
> possible to send you the workbook so the confusion is cleared? its not
> very large!
>
> Regards,
> Simon
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=571041
>