Sort with case sensitive (AC before Ab)

[katzelmacher]

Hi, all:

I have a huge spreadsheet and I need to sort the rows based on the values of column A. The desired behavior is that it is sorted alphabetically but uppercase letters should appear first, ie. AC should come before Ab.

I have checked the "case sensitive" box in Sort options and tried creating a custom list, but it hasn't worked. Would any of you know what to do?

Thanks for your help

[Special-K]

Are they all two letter codes?
How many codes are there?
If there aren't too many type them into a table with a number next to them 1 to however many there are and sort on the number.

[Glenn Kennedy]

Can you give us a few examples of the REAL text that you are trying to sort in this way, and the expected answer?

[oeldere]

you can make a liste with the unique values in column A (e.g in column P)

Then you can (manualy) sort those values.

After that you could use VLookup in a helpcolum, which will help you by sorting the data.

[katzelmacher]

Thanks for your quick replies!

The file is pretty big (over 3,000 rows) and no value of column A is repeated, so I have over 3,000 unique values. Some of them are 2 letters but most are pretty big, they could be entirely in uppercase, entirely in lowercase or mixed, but the rule I need is always the same: first A-Z and then a-z, isn't there a simple way to sort it?

[Glenn Kennedy]

OK. First attempt at a solution for you...

Put this in B1 (assuming that your data start in A1) and copy down.

Please Login or Register  to view this content.

Then select both columns, DATA, SORT, Sort by Column B smallest to largest; then by Column A, A to Z.

[katzelmacher]

Thanks a lot Glenn, that is a pretty impressive formula. It took me a while to understand the reasoning behind it and I learned a new trick with it However, the result is not the desired. If I had these 3 words: About, ALL and BOOT, this is how the formula sorts them:

1) ALL
2) BOOT
3) About

The desired result would be:

1) ALL
2) About
3) BOOT

In other words, it should work like a regular alphabetically sort but as if the alphabet were 52 letters long, starting with A-Z and then a-z. I see there is no easy way to do it without using formulas, that's a shame

Thanks anyways

[shg]

UDF?

Please Login or Register  to view this content.

	A	B	C
1	ALL	414C4C	B1: =hexcode(A1)
2	About	41626F7574
3	BOOT	424F4F54

Sort by col B.

[katzelmacher]

Cool, thanks, that is exactly what I needed, you saved my day!!!

[shg]

You're welcome.

It will only work (sorting-wise) for strings up to 127 characters.

[Glenn Kennedy]

It eventually dawned on me... stupidly simple, actually. It needs no code, but does need two helpers. I post it only 'cos I don't like to be beaten by a straightforward request!!