Problems when doing DataSort

[WCyrek]

Hi when sorting thousands of rows by column A (With
numbers in it), Quite a few entries end up out of order.
This is alarming :/ Anyone know what's up with that? All I
want is to sort numerically ascending column A. I end up
having few (Hard to track down) numbers out of order!

 

[Ken Wright]

They are probably not what you think they are. If you can find a couple like
this, then assuming they are in say Col A and you can see normally sorted data
in cells A1:A20 and a funny one in A21 and then normal after that, in say B1 put
=ISTEXT(A1) and copy down. Pound to a penny your TRUE and FALSES line up with
the funny looking sort. This means that you are probably working with numbers,
but some of them are in reality text, ie they may have leading or trailing
spaces, or the cell may just have been formatted as text before the data went
in. If this is what you are seeing, then copy an empty cell, select all the
numbers and do Edit / Paste Special / Add. This should coerce the data back to
numeric and you can now re-sort. If this doesn't fix it, then you may have
garbage in your data in the fomr of MTML characters, especially if thsi s a data
dump from a web page. If thsi is the case then I would take a look at Dave
McRitchies TRIMALL macro which will fix this for you nicely:-

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

 

[WCyrek]

Thank you I shall give it a try. I reformmated my A column
as numbers before but it may be the case some cells may
have locked formating :/ I will look into your solution
extensively!

 

[Guest]

Ok I verified most columns are False (Ie numbers) But some
are infact True. Worst yet Even thou I unloacked all the
columns (Some being locked) I was still unable to apply
uniform standard :/ I dont think there is any hidde html
in there. If there was I should see it in the formula
window right? SO why cant I apply uniform standard to the
column?

 

[Ken Wright]

Formatting doesn't work that way. Once a cell has been formatted as either
numeric or text and then data entered, if you want to change it around, you need
to change the format to what you want it to be and then edit the cell by either
double clicking into it and hitting enter, or by pressing F2 then enter. As
long as somehow you effect a change to that cell ( Change does not mean the data
has to change - Confused yet :-> ) it will then accept the new format. The
usual fix for that is to do as I suggested:- Copy an empty cell, select all
your data and do Edit / Paste Special / Add. This effectively adds nothing or 0
to the value in the cell, which although it hasn't changed your data, the cell
has gone through a change in that an action has been performed on it. The
addition of the 0 tells Excel that you actually want to treat that data as a
real number, and so it converts it to a numeric value.

 

[WCyrek]

Thank you, that cleared everything up. And I learned
something in the process

 

[Ken Wright]

See - Who said learning wasn't fun

Glad it worked, and do appreciate the feedback - Thank You.