Solved

Sorting order in Excel

Posted on 2014-04-04
5
222 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel Formula 5 43
VBA: insert new column and re-adapat string with lower letterS 4 26
Excel conditional formatting based on 'zero value' 6 18
Excel Question 17 15
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

806 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