First Value in Sorted Column is Not Sorted Properly

[jgraves]

I am using Excel 2007 and finding that sometimes when I sort a list of values
smallest to largest it puts a value out of order in the first cell of the
column of sorted values. I have even cut and pasted that record back in its
right place, then perform a sort again and it goes back to the first cell.
This happens whether the cell format is general, text or number.
It happens when I sort from largest to smallest - the out of order value
just goes to the bottom of the list.
Here is part of the list I am sorting and what it looks like when I sort
Smallest to Largest (A-Z):

39101613
26101101
26101102
26101103
26101105
26101106

Has anyone experienced this before? What can I do to remedy?

Thank you,
Jen

 

[Dave Peterson]

And you're sure that you're not seeing excel treat that cell as the header for
that column (and you specified that your range has headers), right?

I'd bet that the value in the cell is not what you expect. Even though it may
look like a number and contain nothing but digits, it could still be text.

You could count the number of cells that are used in your range with a formula
like:
=counta(a1:a10)

You could count the number of cells that hold numbers with a formula like:
=count(a1:a10)

Do those results match when you try them?

You can test to see if a cell is numeric with:
=isnumber(a1)

You can test to see if a cell is text with:
=istext(a1)

Changing the format of a cell from Text to Number (or General) won't affect the
value in the cell until you reenter it. Hitting F2, then enter is enough.

If you have lots to do, you can:
Select an empty cell
Copy it
select the range to fix
Paste special|Check Values and Add

Then check your sort.

========
But you should have seen a message that asked you if you wanted to sort things
that look like numbers as numbers. Didn't you when you did the sort?

 

[Aoife]

Hi Dave,

I'm afraid I am suffering from a similar problem. I have changed the
formatting to general and to number in an attempt to rectify the situation
but neither worked. Interestingly if I do not have the rows in question
surrounded by thick bold lines the largest to smallest sort function does
work correctly. Could this be a glitch in the design?

I realize the simple solution would then be to just put regular borders
around the rows I am trying to sort however my spreadsheets have on average
30,000 lines and are seperated in varying groups by the thicker lines to
identify different segments.

Do you have any solutions on how to fix this particular problem?

Kindest Regards,

 

[Dave Peterson]

Remember it's not enough to just change the numberformat of the cell.

You have to change the value, too.

This was written for a different purpose (=vlookup()), but it would apply to
sorting, too:

Debra Dalgleish has lots of notes on troubleshooting =vlookup():
http://contextures.com/xlFunctions02.html#Trouble