Excel sorting by columns

[THERADIOTUBBY]

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????

[pcstru]

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.

[LosOjos]

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...

[THERADIOTUBBY]

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/209380151/Yr11%20Full%20Rag.xls

[pcstru]

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.

 

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

Edited December 6, 2013 by pcstru

[THERADIOTUBBY]

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

[pcstru]

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.

[LosOjos]

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.

Edited December 9, 2013 by LosOjos