Solved

Sorting order in Excel

Posted on 2014-04-04
5
221 Views
Last Modified: 2014-04-05
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
Comment
Question by:John Fistere
5 Comments
 
LVL 17

Accepted Solution

by:
Kent Dyer earned 125 total points
ID: 39979654
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
 
LVL 34

Assisted Solution

by:Dan Craciun
Dan Craciun earned 125 total points
ID: 39979675
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
 
LVL 19

Assisted Solution

by:helpfinder
helpfinder earned 125 total points
ID: 39979733
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
 
LVL 17

Assisted Solution

by:andrewssd3
andrewssd3 earned 125 total points
ID: 39979744
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
 
LVL 2

Author Comment

by:John Fistere
ID: 39980667
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now