[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 233
  • Last Modified:

Sorting order in Excel

I am sorting a table on a column that has alphabetic characters that signify statuses. I want to use a character to signify "Done" that is not alphabetic, and have those items appear at the bottom of the list. Instead of ASCII sort order, all special characters seem to appear before the alphabetic characters. Is there any way I can change the sort order to give me a special character at the bottom of the list? As I write my question I realize I can use "Z" for that function but I still would like an answer to my question.
0
John Fistere
Asked:
John Fistere
4 Solutions
 
Kent DyerCommented:
OK.. Think of an Excel File like a SQL table..
You have ID which can or cannot have an INDEX or PK or FK constraint
You have a series of fields

And maybe that is it..

Instead of sorting by Name or Product or?  Why not create a numbered column..  Numbers work better and than Alpha characters for sorting anyway...
0
 
Dan CraciunIT ConsultantCommented:
According to this: http://support.microsoft.com/kb/322067 , the order by which Excel sorts strings is:
0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { | } ~ + < = > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

Open in new window

So you could add "ZZ" at the beginning of the cells you want last.

HTH,
Dan
0
 
helpfinderCommented:
you can go the way Kent mentioned. Add new column into your table which ewill be connected with your statuses and yould be used for sorting.
I did a smal sample - check in attachment.
Lets imagine your table is the table which in my sample replresents column B, C, and D.
I added new column at the beginning of the table (A column).
In column K I put a list of statuses and in L their numbers (based on the alphabet order)
Then I used Excel feature Data validation in B column so you can choose status from drop down menu and in A column I used VLOOKUP formula to automatically populate the number connected with the status just after status is chosen.
Then if you sort your table based on A column you get statuses sorted alphabetically (and your special character for DONE, in my case v, won´t be first or last but exactly when you want)
Columns K and L, if you want A as well could be hidden so nobody will see them when workbook is open (I leave them visible so you can clearly see what I have done)
sample.xlsx
0
 
andrewssd3Commented:
In recent versions of Excel (post 2007, I think), you can sort on cell fill colour or font colour or icon.  You could use this in conjunction with conditional formatting to do what you want.  If you add a conditional format that gives your 'Done' cell a certain fill or font colour, you can then sort using two levels, first by cell colour, putting your 'Done' colour at the bottom, then by normal alpha sort.
0
 
John FistereEngineer, retiredAuthor Commented:
My solution for my problem is simply to use a-d for priority and z for done, which I included at the last minute in my question. Thanks for all your ideas.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now