Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-06-2011, 10:23 PM
skoz55 skoz55 is offline Arranging Into Groups Windows 7 64bit Arranging Into Groups Office 2010 64bit
Novice
Arranging Into Groups
 
Join Date: Jan 2009
Posts: 20
skoz55 is on a distinguished road
Default Arranging Into Groups

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.
Reply With Quote
  #2  
Old 09-07-2011, 12:02 AM
Catalin.B Catalin.B is offline Arranging Into Groups Windows Vista Arranging Into Groups Office 2007
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

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
Attached Files
File Type: xlsm create groups.xlsm (21.0 KB, 4 views)
Reply With Quote
Reply

Thread Tools
Display Modes


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

Other Forums: Access Forums

All times are GMT -7. The time now is 11:04 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft