+ Post New Thread
Results 1 to 8 of 8
Windows Thread, Excel sorting advice in Technical; Hi, I want to sort levels in ascending or descending order correctly. So a list like "2a, 2c, 1a, 2b" ...
  1. #1
    rocknrollstar's Avatar
    Join Date
    Jun 2008
    Location
    Hampshire
    Posts
    435
    Thank Post
    387
    Thanked 28 Times in 24 Posts
    Rep Power
    20

    Excel sorting advice

    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.

  2. #2

    Michael's Avatar
    Join Date
    Dec 2005
    Location
    Birmingham
    Posts
    9,266
    Thank Post
    242
    Thanked 1,575 Times in 1,254 Posts
    Rep Power
    341
    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.

  3. Thanks to Michael from:

    rocknrollstar (16th July 2009)

  4. #3
    rocknrollstar's Avatar
    Join Date
    Jun 2008
    Location
    Hampshire
    Posts
    435
    Thank Post
    387
    Thanked 28 Times in 24 Posts
    Rep Power
    20
    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.

  5. #4

    Michael's Avatar
    Join Date
    Dec 2005
    Location
    Birmingham
    Posts
    9,266
    Thank Post
    242
    Thanked 1,575 Times in 1,254 Posts
    Rep Power
    341
    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.

  6. Thanks to Michael from:

    rocknrollstar (16th July 2009)

  7. #5
    michaelf's Avatar
    Join Date
    Jun 2007
    Location
    Kettering, Northants
    Posts
    81
    Thank Post
    7
    Thanked 13 Times in 10 Posts
    Rep Power
    17
    Quote Originally Posted by Michael View Post
    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?
    Attached Images Attached Images

  8. Thanks to michaelf from:

    rocknrollstar (16th July 2009)

  9. #6

    elsiegee40's Avatar
    Join Date
    Jan 2007
    Location
    Kent
    Posts
    10,009
    Thank Post
    1,854
    Thanked 2,303 Times in 1,702 Posts
    Rep Power
    821
    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

  10. Thanks to elsiegee40 from:

    rocknrollstar (28th July 2009)

  11. #7

    sparkeh's Avatar
    Join Date
    May 2007
    Posts
    7,019
    Thank Post
    1,364
    Thanked 1,771 Times in 1,193 Posts
    Blog Entries
    22
    Rep Power
    530
    Quote Originally Posted by elsiegee40 View Post
    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.

  12. Thanks to sparkeh from:

    rocknrollstar (28th July 2009)

  13. #8
    rocknrollstar's Avatar
    Join Date
    Jun 2008
    Location
    Hampshire
    Posts
    435
    Thank Post
    387
    Thanked 28 Times in 24 Posts
    Rep Power
    20
    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.
    Attached Files Attached Files

SHARE:
+ Post New Thread

Similar Threads

  1. Sorting levels in Excel - eg 2a 2b 2c
    By rocknrollstar in forum Office Software
    Replies: 4
    Last Post: 15th September 2008, 07:21 PM
  2. Sorting Music
    By OutLawTorn in forum How do you do....it?
    Replies: 10
    Last Post: 23rd November 2007, 11:10 AM
  3. Italc sorting the users on teacher's screen
    By dbowen in forum Network and Classroom Management
    Replies: 15
    Last Post: 9th November 2007, 12:24 AM
  4. Sorting Programs List by Name
    By Midget in forum Windows
    Replies: 17
    Last Post: 24th January 2007, 11:04 AM

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
  •