sorting problem

[Guest]

I am sorting a list with 493 rows, and when I sort it by Last Name ascending
it sorts A-Z but then midway through starts sorting the list over by A-Z so I
end up with three different groups of sorted dated within the same worksheet.
No duplicates but very annoying. When I look in the drop down arrow on the
column header it shows the entire list sorted correctly with all of the A's
together etc. instead of what the actual sheet shows. Any ideas out there to
fix it?

 

[David Biddulph]

Did you select your complete list, all the rows & columns you want
including, before you requested the sort?

 

[Guest]

It sounds like the names have at least one leading space.

Example:
A1: Names
A2: Al
A3: Ben
A4: Corey
A5: Abby........(there is a leading space in this name)
A6: Barbara....(there is a leading space in this name)

Sort that list ascending.
The results are:
Name
Abby
Barbara
Al
Ben
Corey

Yet....with AutoFilter
The "Names" dropdown returns this list:
Abby
Al
Barbara
Ben
Corey

If that's your situation, you have a couple options.
If there are NO spaces in the names, you could replace all spaces with
nothing.
OR
You could put this kind of formula in a helper cell:
B2: =TRIM(A2)
Copy that formula down as far as you need.
Then copy the helper column and Paste_Special.Values over the original list.

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Gord Dibben]

Sweetpea

Check your data for leading spaces.

The data with leading spaces will sort to the top first.

i.e <sp>a and <sp>b will sort before a and b

<sp><sp>a will sort before <s>a or a

Maybe in an adjacent column enter =TRIM(cellref) and copy down.

Sort on that column.


Gord Dibben MS Excel MVP