Excel question

[The Drunken Actuary]

I have run into this problem a few times and it seems like it happens when I get the data from an HR system like PeopleSoft. I will have, for example, a list of names and when I sort them all of the data is not in order. It starts over somewhere in the middle. In other words instead of getting:
Allen
Beck
Carter
Daneils
Eisle
Fletch

I get:
Allen
Carter
Eisle
Beck
Daniels
Fletch

It causes havoc with pivot tables too.

Has anyone experience this before and know how to fix it? I've tried reformatting, cutting a pasting to a new workbook, and anything else I could think of. Any suggestions would be appreciated.

[Gandalf]

I've never seen that.

I don't suppose the names could contain initial blanks, with different numbers of initial blanks?

You might try applying the Proper function then sorting. There's a chance it would remove whatever the problem is.

In your simplified example, you don't show any first letters occurring both before and after the "restart". If in your real problem cases you do have the same first letter in both parts of the list, you could try the Code function to see if it agrees that the first characters are identical.

[The Drunken Actuary]

Thanks. I am sure that the real life situations (being hundreds of entries) contains repeats of the same first letter. I'm not familiar with the Code function. I'll check that. What do I do if it does not agree they are the same?

Also, I'm pretty sure there are no initial blanks, but I will try the Proper function. (not familiar with that either but I assume I can figure them both out.)

[Gandalf]

Thanks. I am sure that the real life situations (being hundreds of entries) contains repeats of the same first letter. I'm not familiar with the Code function. I'll check that. What do I do if it does not agree they are the same?

I don't know. It might at least tell you why they are not sorting in the manner you expect (because it thinks the first characters are different).

Also, I'm pretty sure there are no initial blanks, but I will try the Proper function. (not familiar with that either but I assume I can figure them both out.)

Proper(text) just converts a text string to another text string with each word initially capitalized. I thought there would be an outside chance that applying it would remove whatever differences in the original strings was causing the odd sorting.

You might also just try looking at Left(text,1) to make sure that the left-most character looks like what you think it is. Sort of a variation of what I was hoping you might learn from Code. Code(text) is supposed to tell you the numeric code for the first character according to the character set being used by your computer.

[The Drunken Actuary]

Thanks, I'll use all of that next time it comes up. I just finished sorting a bunch of crap by hand because Excel wouldn't do it correctly!!

[Alya]

Also you can try to left align your list of last names and save it as a text file first (space delimeted), then open it up in any text editor. If something funny is going on there you will see your problem people right away.

[Elisha]

Do you know how to manully sort within a pivot table? Go to advanced, click on manual (as opposed to ascending, descending), then drag the columns or rows into whatever order you want. Probably that in conjunction with Gandalf's suggestion would work.