Solved

Sorting order in Excel

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
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.

708 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

17 Experts available now in Live!

Get 1:1 Help Now