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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

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