Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 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 say pasted on to th worksheet starting with the groups of 3 (so paste a group of 3 the skip 3 rows then paste groups of 4 skip 2 rows, the row skipping is t allow seperation and manual entry of extra data). 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 -- Simon Lloy ----------------------------------------------------------------------- Simon Lloyd's Profile: http://www.excelforum.com/member.php...nfo&userid=670 View this thread: http://www.excelforum.com/showthread.php?threadid=57104 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Group and count a list of dates | Excel Worksheet Functions | |||
How to count items in a list and group depending on size of list? | Excel Programming | |||
Count items on the list | Excel Discussion (Misc queries) | |||
is it possible to re-size or format list items in dropdown box? | Excel Discussion (Misc queries) | |||
How can i change the font size of the items in a list | Excel Discussion (Misc queries) |