Arranging Into Groups

skoz55 · #1 09-06-2011, 10:23 PM

I have a spreadsheet with 48 different students with they're grades ranging from 10 - 0. I would like to place the students in 8 groups with a top scorer in each, second top scorer in each, third...

So the groups will consist of the following students in each
Group #1 - 1,7,13,19,25,31,37
Group #2 - 2,8,14,20,26,32,38
Group #3 - 3,9,15,21,27,33,39
etc.

I have to do this with 24 spreadsheets of students, thus hoping there is a method that Excel can automate this task.

Thanks for any help.

Catalin.B · #2 09-07-2011, 12:02 AM

you can try this method, but it was easier if you uploaded a sample to see how is data organised... the same code is in test workbook attached..

Code:

Sub createGroups()
'first, we will sort all worksheets
Dim wks As Worksheet, i As Integer
For Each wks In ThisWorkbook.Worksheets
wks.Activate
  ActiveSheet.Sort.SortFields.Clear
  ActiveSheet.Sort.SortFields.Add Key:=Range("C1:C51"), _
  SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("B1:C51")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    With ActiveSheet
 For i = 1 To 8
 .Cells(i, "F").Value = "Group " & i
 .Cells(i, 7).Value = .Cells(i, "B").Value
 .Cells(i, 8).Value = .Cells(i + 8, "B").Value
 .Cells(i, 9).Value = .Cells(i + 16, "B").Value
 .Cells(i, 10).Value = .Cells(i + 24, "B").Value
 .Cells(i, 11).Value = .Cells(i + 32, "B").Value
 .Cells(i, 12).Value = .Cells(i + 40, "B").Value
Next i
End With
    
    Next wks


End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Replys to large groups get stuck in outbox Outlook 2007	Guy ABS Broadcast	Outlook	0	08-05-2011 02:05 AM
Outlook 2010 Contact Groups	pmokover	Outlook	0	05-21-2011 09:57 AM
Groups of folders in Outlook 2010	balubeto	Outlook	1	01-06-2011 08:09 AM
Moving Sigs and Groups from OE6 to Outlook 2007	Latif	Outlook	0	10-11-2010 10:23 AM
Some contacts are listed in groups I don't want	ironwoods	Outlook	0	07-29-2010 04:40 PM