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

[Simon Lloyd]

Hi all,

I would like to be able to count the amount of entries in column C an
depending on the amount group in either groups of 3 or 4, all name
would be unique......so if there are 14 names in the list they woul
need to be grouped in to two groups of 4 and two groups of 3, if ther
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 an
display the result (i have only used up to 24 in this test, the name
come from sheet2 in my workbook)

Hope someone can help.......it seems very complex to group all th
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 Su

 

[Guest]

This function returns the number of groups of 3, given your number:

Function GetGroup(ByVal Num As Integer) As Integer
If 0 = Num Mod 4 Then Exit Function
GetGroup = 1
Do Until 0 = (Num - 3) Mod 4
GetGroup = GetGroup + 1
Num = Num - 3
Loop
End Function


e.g if it returns 2 as with GetGroup(19), you have 2 groups of 3 and the
remainder is in groups of 4

 

[Guest]

Only a part of the solution here, but there is a formula that can figure for
you how many groups of 3 and 4 you need for any number of players:

Public Function PGroups(ByVal NumPlayers As Integer) As Variant
' NumPlayers is the number of players to put into groups of 3 and 4
' Return value is an array where the first element is the number of groups
of 3
' The second element is the number of groups of 4
Dim PlayerGroups(2) As Integer
Dim PMod As Integer

PMod = NumPlayers - 4 * Int(NumPlayers / 4)
PlayerGroups(1) = Choose(PMod + 1, 0, 3, 2, 1)
PlayerGroups(2) = (NumPlayers - PlayerGroups(1) * 3) / 4

PGroups = PlayerGroups
End Function

Example:
P = 49
? PGroups(P)(1)
3
? PGroups(P)(2)
10

3 groups of 3 = 9 players and 10 groups of 4 = 40 players, so the 49 players
fit in 3 groups of 3 and 10 groups of 4

Knowing this you should be able to develop a routine to choose the groups
from your list and copy them to a new worksheet, I think.

 

[Guest]

This:

"e.g if it returns 2 as with GetGroup(19), you have 2 groups of 3 and the
remainder is in groups of 4"

should have read

"e.g if it returns 2 as with GetGroup(14), you have 2 groups of 3 and the
remainder is in groups of 4"

 

[Simon Lloyd]

Guys!

Thanks for your responses, i'm not at work for a couple of days bu
will try to adapt your coding when i get back and let you know th
results.....or beg more help

Once again thanks............post soon!.

Simo

 

[Simon Lloyd]

Well i tried but couldnt integrate your functions it must be something
im missing, when the functions have performed their task i would like
the groups to be copied and pasted in to a seperate sheet each group
titled Group n where n would be the number of the group i.e group1,
group2 etc.....so if there were 3 groups of 4 and 2 groups of 3, the
first group of 4 would be copied and pasted and titled group 1 and then
the second group and so on....Anyone any ideas?

Simon.