+ Reply to Thread
Results 1 to 4 of 4

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

  2. #2
    Bob Phillips
    Guest

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

    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" <Simon.Lloyd.2cfp1g_1155397811.1138@excelforum-nospam.com>
    wrote in message
    news:Simon.Lloyd.2cfp1g_1155397811.1138@excelforum-nospam.com...
    >
    > 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
    >




  3. #3
    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
    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
    e-mail:
    simonwlloydATmsnDOTcom
    Last edited by Simon Lloyd; 08-12-2006 at 03:48 PM.

  4. #4
    Bob Phillips
    Guest

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

    Yeah sure, just note my signature.

    --
    HTH

    Bob Phillips

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

    "Simon Lloyd" <Simon.Lloyd.2cfzx5_1155411909.6351@excelforum-nospam.com>
    wrote in message
    news:Simon.Lloyd.2cfzx5_1155411909.6351@excelforum-nospam.com...
    >
    > 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
    >




+ 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