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 ...
5th December 2013, 05:15 PM #1
- Rep Power
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????
5th December 2013, 05:23 PM #2
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.
5th December 2013, 05:23 PM #3
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...
5th December 2013, 06:54 PM #4
- Rep Power
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
6th December 2013, 09:28 AM #5
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.
' Macro1 Macro
For i = 2 To 20
sRow = Trim(Str(i))
sRange = "L" + sRow + ":AL" + sRow
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range(sRange) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
Last edited by pcstru; 6th December 2013 at 09:30 AM.
9th December 2013, 12:38 PM #6
- Rep Power
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
9th December 2013, 12:50 PM #7
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.
9th December 2013, 01:01 PM #8
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 01:03 PM.
By LosOjos in forum MIS Systems
Last Post: 18th October 2010, 09:47 AM
By rocknrollstar in forum Windows
Last Post: 28th July 2009, 12:47 PM
By DMcCoy in forum EduGeek.net Site Problems
Last Post: 27th January 2008, 04:11 PM
By FN-GM in forum Scripts
Last Post: 17th October 2007, 06:33 PM
By mattpant in forum Wireless Networks
Last Post: 16th November 2005, 03:59 PM
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)