+ Post New Thread
Results 1 to 8 of 8
Office Software Thread, Excel sorting by columns in Technical; I create a large spreadsheet with rows for each student and columns for each subject containing their current estimated grade ...
  1. #1

    Join Date
    Apr 2008
    Location
    Notts
    Posts
    55
    Thank Post
    1
    Thanked 2 Times in 2 Posts
    Rep Power
    14

    Excel sorting by columns

    I create a large spreadsheet with rows for each student and columns for each subject containing their current estimated grade coloured as to whether it is above or below their target in that subject

    I want to sort that list - keeping the colours - so the columns show their best to worst grade (subject not needed)

    I can do it for one row at a time

    Anyone know of a macro or code that would do all 150 rows????

  2. #2


    Join Date
    May 2009
    Posts
    3,275
    Thank Post
    290
    Thanked 883 Times in 661 Posts
    Rep Power
    339
    Usually if the columns are subjects, then each column would be a specific subject, with the cell of that column in the student row containing the grade. If you now sort the grades for a student, they will not be in the correct subject columns. Either you have more data in the cell than just the grade or you have more than one row for each student so you can show the subject.

    If you post your sheet it might help.

  3. #3

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,562
    Thank Post
    1,469
    Thanked 1,221 Times in 830 Posts
    Rep Power
    752
    What are you actually trying to achieve? A sorted list of results with no context (i.e. subject) doesn't sound very useful, unless you're trying to do something like get the Capped 8 point score?

    If you tell us what you want to do with the data when it's sorted, there may be a better way...

  4. #4

    Join Date
    Apr 2008
    Location
    Notts
    Posts
    55
    Thank Post
    1
    Thanked 2 Times in 2 Posts
    Rep Power
    14
    For the current best 8 measure we want to see what grades will currently count in a students best 8 in order

    Although we realise these grades will be without context ie subjects it would help us plan interventions if we could see which grades are below target

    I can get point scores in order from Sims using the Max function and could even convert back to grades using lookup. However the colour coding would be useful

    https://dl.dropboxusercontent.com/u/...Full%20Rag.xls

  5. #5


    Join Date
    May 2009
    Posts
    3,275
    Thank Post
    290
    Thanked 883 Times in 661 Posts
    Rep Power
    339
    It's a bit of a hack - hopefully you can see what you need to change (the loop numbers or the range identifier). Done in Excel 2010.

    Code:
    Sub Macro1()
    '
    ' Macro1 Macro
    '
    
    '
       
        For i = 2 To 20
            sRow = Trim(Str(i))
            sRange = "L" + sRow + ":AL" + sRow
            Range(sRange).Select
            ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
            ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range(sRange) _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        
        
            With ActiveWorkbook.Worksheets("Sheet1").Sort
                .SetRange Range(sRange)
                .Header = xlGuess
                .MatchCase = False
                .Orientation = xlLeftToRight
                .SortMethod = xlPinYin
                .Apply
            End With
        Next
            
        
    End Sub
    Last edited by pcstru; 6th December 2013 at 08:30 AM.

  6. #6

    Join Date
    Apr 2008
    Location
    Notts
    Posts
    55
    Thank Post
    1
    Thanked 2 Times in 2 Posts
    Rep Power
    14
    Works like a dream. Much appreciated

    Is it possible to add a custom sort list.

    So I could include my Btec Pass, merit grades etc as equal to C,B etc

  7. #7


    Join Date
    May 2009
    Posts
    3,275
    Thank Post
    290
    Thanked 883 Times in 661 Posts
    Rep Power
    339
    At the simplest, just include them and do a find/replace to substitute a grade for BTEC result. They will then sort as you want. If you want to track the information as a Btec, use "Abt" "Bbt" etc.

    I tend to use intermediate sheets and map grades to a numeric value then operate on that and map back for presentation.

  8. #8

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,562
    Thank Post
    1,469
    Thanked 1,221 Times in 830 Posts
    Rep Power
    752
    I calculate our best 8 a little differently. For each grade column, I create another column for the points. I then have two tables, one for GCSE and one for BTEC, with the grades listed left and points to the right. I then do a lookup on the relevant table to find the equivalent points for each subject. I then use Excel's LARGE function to get my best 8 (best 6-8 GCSE + best two BTEC if they have them).

    The reason I do it that way rather than with macros is simply because I'm not the only person who has to maintain it and my counterpart isn't too hot on VBA. Also means we can easily add in new point score tables for odd subjects (like CACHE or OCR) that use different point scores.
    Last edited by LosOjos; 9th December 2013 at 12:03 PM.

SHARE:
+ Post New Thread

Similar Threads

  1. [SIMS] User Defined Groups - Sort by name?
    By LosOjos in forum MIS Systems
    Replies: 32
    Last Post: 18th October 2010, 08:47 AM
  2. Excel sorting advice
    By rocknrollstar in forum Windows
    Replies: 7
    Last Post: 28th July 2009, 11:47 AM
  3. [CLOSED] Bug/Error: Forum threads now sorted by name, not date
    By DMcCoy in forum EduGeek.net Site Problems
    Replies: 4
    Last Post: 27th January 2008, 03:11 PM
  4. Script to sort by name on start menu
    By FN-GM in forum Scripts
    Replies: 21
    Last Post: 17th October 2007, 05:33 PM
  5. Sort By Name Group Policy
    By mattpant in forum Wireless Networks
    Replies: 6
    Last Post: 16th November 2005, 02:59 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •