Solved

Sorting order in Excel

Posted on 2014-04-04
5
227 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 35

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

705 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