Excel sorting advice

[rocknrollstar]

Hi, I want to sort levels in ascending or descending order correctly. So a list like "2a, 2c, 1a, 2b" should sort to "1a,2c,2b,2a". I know how to do this with Custom Lists.

However, I have recently found the AutoFilter option which automatically gives you sort drop down boxes, which is very useful! But the default sort ascending option will sort it like "1a,2a,2b,2c", not the way that I need.

Is it possible to use AutoFilter with a custom sort list?

If not, do you know of another way to sort according to the levels, and get a drop down sort box (or button)?

Thanks in advance.

[Michael]

When you created an AutoFilter, by clicking the drop down menu and choosing "1a" this will only display all entries with 1a. Then specifying 'All' will then display all data again.

I've found using Conditional Formatting can help a lot giving visual representation instead of just a spreadsheet full of numbers and levels.

[rocknrollstar]

Thanks Michael. However, I really need to be able to sort the list in the correct 'level' order, and show all the data too. I know I can do this if I do Tools -> Data -> Sort -> then choose my custom sort list, but it involves selecting all the data first. The spreadsheet is pretty big!

Any thoughts?\I thought about overridding the default sort ascending method in excel, but don't know VBA very well. I've had a lot of experience in Java programming, but not VBA/Excel stuff.

Thanks again for your efforts.

[Michael]

I've had a quick look on Google and it appears Excel 2007 may give you more 'options' to specify proper filters. As time goes on I have found Excel 2007 does actually provide some good enhancements to previous versions.

[michaelf]

I've had a quick look on Google and it appears Excel 2007 may give you more 'options' to specify proper filters.

2007 includes the option to do a custom sort from the Auto Filter menu (although its hidden under the Sort By Colour menu). Another idea, what about defining a custom range in the sheet to save you selecting all the data each time?

[elsiegee40]

At my last school, 5 years ago now, I had to crack this problem. I was using Office XP at the time.

There were 2 solutions

1. Split the grade into 2 parts - So you have a column for Level and a Column for Sub-Level, you can then sort by Level ascending, Sub-Level descending to get the grades in the right order.

2. Add a column for Level Sorting and translate the grades into a numerical value, so I started with the lowest P grade as 1 and incremented by one until I got to Level 5. Youcan then sort by the level sorting value (but don't have to print it)

I used solution 2 in the end as the P grades were a pain. We didn't have many, but it was a nuisance.

I hope I've got the right letter for the P grades, I think that's what the "not reached Level 1s" were called

[sparkeh]

2. Add a column for Level Sorting and translate the grades into a numerical value, so I started with the lowest P grade as 1 and incremented by one until I got to Level 5. Youcan then sort by the level sorting value (but don't have to print it)

This is how I have done it in the past. In addition, I recorded a macro for sorting it, hid the level sorting column, then stuck a button at the top of the column to run the macro. Repeated for each column that I wanted to sort by.

[rocknrollstar]

Thanks for everyone's input on this question. I managed to work it out using vba scripting. It's to do with automatically creating a custom sort list in vba, then allowing that sort mechanism to be used via a button at the top of the column that you want to sort.

Attached is my full version of the tracking document, which also performs some analysis and creates graphs. It's created for use in a junior school, as I don't like Assessment Manager! Note that it needs Macros to be enabled to work, and I promise it doesn't do anything dodgy like delete files from your hard disk. Certainly doesn't on mine!

Hope it's useful to someone.