Sorting levels in Excel - eg 2a 2b 2c

[rocknrollstar]

Hi, we use Excel for pupil level tracking. Levels are stored as 2a, 2b, 3c for example (according to QCA/SATs levelling).

 

I'd like to be able to sort these sensibly, but in Excel, 2a will come before 2b and so on. When we want to sort ascending, this won't work as 2a will be before 2b, but it should be the other way round.

 

Has anyone got any ideas for doing this?

 

Also- if you wanted to produce graphs of pupil progress, this will obviously make it difficult- drawing a graph using letters and numbers won't really work.

 

Any thoughts?

 

Thank you

[User3204]

I'm at home, where I use OpenOffice, so I don't know if it works the same... although it must be possible...

 

Anyway.. in OpenOffice (ver 2. whatever) If you click on the Tools > Sort > options, you can define a "custom sort order".

 

..Oh, and you'll have to have predefined a list from Tools > Options > OpenOffice Calc > Sort Lists

 

Dunno about the graphs.

Edited September 14, 2008 by User3204

[Michael]

I've produced spreadsheets for schools for monitoring/leveling. The easiest way around your problem is to use filtering, so when you specify a certain level, you just view all the pupils who are at that level.

[laserblazer]

You could split the number and letter into separate columns. Then sort on the number column first then letter column. Hiding the two new columns. Just tried it and it seems to work ok.

[rocknrollstar]

Thanks for your help on this.

 

I came across Sorting in Excel which lets you define custom sorting orders (so that a 2b comes before 2a) etc.

 

I'm also looking into using VBA macros to help with finding sub level progress.

 

Many thanks