+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Forum Expert Simon Lloyd's Avatar
    Join Date
    03-02-2004
    Location
    locked in the cage
    MS-Off Ver
    All the ones my homepage shows
    Posts
    3,161

    How to count items in a list and group 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.

    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

  2. #2
    AA2e72E
    Guest

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

    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


    "Simon Lloyd" wrote:

    >
    > 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
    >
    >
    > --
    > Simon Lloyd
    > ------------------------------------------------------------------------
    > Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
    > View this thread: http://www.excelforum.com/showthread...hreadid=535463
    >
    >


  3. #3
    K Dales
    Guest

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

    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.
    --
    - K Dales


    "Simon Lloyd" wrote:

    >
    > 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
    >
    >
    > --
    > Simon Lloyd
    > ------------------------------------------------------------------------
    > Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
    > View this thread: http://www.excelforum.com/showthread...hreadid=535463
    >
    >


  4. #4
    AA2e72E
    Guest

    RE: How to count items in a list and group depending on size of li

    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"





    "AA2e72E" wrote:

    > 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
    >
    >
    > "Simon Lloyd" wrote:
    >
    > >
    > > 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
    > >
    > >
    > > --
    > > Simon Lloyd
    > > ------------------------------------------------------------------------
    > > Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
    > > View this thread: http://www.excelforum.com/showthread...hreadid=535463
    > >
    > >


  5. #5
    Forum Expert Simon Lloyd's Avatar
    Join Date
    03-02-2004
    Location
    locked in the cage
    MS-Off Ver
    All the ones my homepage shows
    Posts
    3,161
    Guys!

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

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

    Simon

  6. #6
    Forum Expert Simon Lloyd's Avatar
    Join Date
    03-02-2004
    Location
    locked in the cage
    MS-Off Ver
    All the ones my homepage shows
    Posts
    3,161
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1