Problems when doing DataSort

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!

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"WCyrek" wrote in message
...
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!


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004

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!



-----Original Message-----
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

--
Regards
Ken....................... Microsoft MVP -
Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------
------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------
------------------



"WCyrek" wrote in
message
...
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!


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.560 / Virus Database: 352 - Release Date:
08/01/2004


.

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?



-----Original Message-----
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

--
Regards
Ken....................... Microsoft MVP -
Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------
------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------
------------------



"WCyrek" wrote in
message
...
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!


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.560 / Virus Database: 352 - Release Date:
08/01/2004


.

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.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



wrote in message
...
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?



-----Original Message-----
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

--
Regards
Ken....................... Microsoft MVP -
Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------
------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------
------------------



"WCyrek" wrote in
message
...
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!


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.560 / Virus Database: 352 - Release Date:
08/01/2004


.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004

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

-----Original Message-----
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.

--
Regards
Ken....................... Microsoft MVP -
Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------
------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------
------------------



wrote in message
...
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?



-----Original Message-----
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

--
Regards
Ken....................... Microsoft
MVP -
Excel
Sys Spec - Win XP Pro / XL 00/02/03

-------------------------------------------------------
---
------------------
It's easier to beg forgiveness than ask permission :-)
-------------------------------------------------------
---
------------------



"WCyrek" wrote in
message
...
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!


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system
(http://www.grisoft.com).
Version: 6.0.560 / Virus Database: 352 - Release Date:
08/01/2004


.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.560 / Virus Database: 352 - Release Date:
08/01/2004


.

See - Who said learning wasn't fun :-)

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"WCyrek" wrote in message
...
Thank you, that cleared everything up. And I learned
something in the process

-----Original Message-----
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.

--
Regards
Ken....................... Microsoft MVP -
Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------
------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------
------------------



wrote in message
...
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?



-----Original Message-----
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

--
Regards
Ken....................... Microsoft
MVP -
Excel
Sys Spec - Win XP Pro / XL 00/02/03

-------------------------------------------------------
---
------------------
It's easier to beg forgiveness than ask permission :-)
-------------------------------------------------------
---
------------------



"WCyrek" wrote in
message
...
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!


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system
(http://www.grisoft.com).
Version: 6.0.560 / Virus Database: 352 - Release Date:
08/01/2004


.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.560 / Virus Database: 352 - Release Date:
08/01/2004


.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.562 / Virus Database: 354 - Release Date: 16/01/2004