Sort Order

[bobbyv33]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

this is a pretty basic question.

What comes after "Z" in a sort order? I want to make a custom list with more than 26 items that I can sort. I've tried "aa" but that puts aa after a. I have also tried a1 with the same results. If I put some sort of punctuation before (I've tried "." and "~" amongst others) they get sorted above A.

Using the excel convention that they use for cell numbers (aa,ab,ac) again sorts all before b.

Any ideas?

 

[CyberTaz]

The sort order is based on the ASCII value of the cell content - which most
of us don't know... But each character has a value associated with it.

You can create a custom Sort order, but for what you describe it would be a
PITA. Why not just use numbers - such as 1 through 50 or whatever?

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[Bob Greenblatt]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

this is a pretty basic question.

What comes after "Z" in a sort order? I want to make a custom list with more
than 26 items that I can sort. I've tried "aa" but that puts aa after a. I
have also tried a1 with the same results. If I put some sort of punctuation
before (I've tried "." and "~" amongst others) they get sorted above A.

Using the excel convention that they use for cell numbers (aa,ab,ac) again
sorts all before b.

Any ideas?

After Z it is [ \ ] ^. You can figure this out for yourself. Start with the
value 33 in a cell (A1). In the cell to the right place the formula
=char(a1). Then select both cells and fill down for a ways.

 

[bobbyv33]

I've tried your idea of checking the CHAR() function. According to that, the "|" charactor comes AFTER lowercase z however, when I tried that, it, again, sorted above a.

 

[Bob Greenblatt]

I've tried your idea of checking the CHAR() function. According to that, the
"|" charactor comes AFTER lowercase z however, when I tried that, it, again,
sorted above a.

Try Excel help. You'll find it will answer a lot of your questions, and is
usually faster and more complete than posting here. The first entry
retrieved when searching for "Sort Order" says:

Excel sorts data by using the following ascending sort order: 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 FALSE TRUE (sheet errors)
(blank cells). You can also sort data by weekday, month, or a custom list
that you specify.

 

[bobbyv33]

Bob,

I appreciate all your help. I have, of course checked the excel help but it says that after z comes false then true, that unfortuneatly doesn't help me. Thus far the only way I have found to properly sort is "x, y, z, za, zb, zc..." I really don't like that but it works.

Any other ideas?

 

[CyberTaz]

Since you chose to ignore my first reply let me offer another idea:

Use combinations of 1A, 1B, 2A, 2B, etc. - or use the letter first followed
by the digit.

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[bobbyv33]

I'm sorry if you think I have ignored any of your ideas. I have not and very much appreciate all of your help, thank you.

the reason that I prefer to sort by letters rather than numbers is so I don't confuse them in calculations that sometimes get pretty complicated.

1a,1b,1c ...2a,2b,2c works ok as long as I start from there (can't start first with a,b,c because all the numbers come before the letters, but I guess I can live with that). a1,b1,c1 doesn't work, it gets sorted a, a1, a2, b, b1, b2.

once again, thanks for all your help.

 

[CyberTaz]

No problem - I was just teasing about being ignored ;-) It takes a lot more
than that to offend me. I just offer what I can, and if someone does choose
to ignore it, wellllll, that's on them

As you're finding, it becomes difficult to establish a sort key when the
number of items to be sorted exceeds the number of unique identifiers
available... That's why credit card numbers are 16 digits - plus exp. date,
plus, plus, plus....

Regards |:>)
Bob Jones
[MVP] Office:Mac