cannot sort

[chuckaarthur]

on macintosh os x version 10.3.9 and ' excel x for mac service release
1' copyright 2001 i sort spreadsheet with about 540 rows on column1
'a' which has numbers between 70000 and 2e9. the sort routine appears
to sort the first 512 rows correctly. the final forty rows are also
sorted, but they appear below the 512. from previous posts, i removed
all blank rows with no results.

 

[Bob Greenblatt]

on macintosh os x version 10.3.9 and ' excel x for mac service release
1' copyright 2001 i sort spreadsheet with about 540 rows on column1
'a' which has numbers between 70000 and 2e9. the sort routine appears
to sort the first 512 rows correctly. the final forty rows are also
sorted, but they appear below the 512. from previous posts, i removed
all blank rows with no results.

Are you sure all the numbers are really numbers and not text. Format the
column as numbers. Then in an empty cell, enter a 1. Copy this cell. Then
select all the numbers in column A to be sorted. Then from the Edit menu
select paste special and choose multiply. This will convert any text values
to numbers.

 

[Kenneth Cohen]

Bob, a question on the same subject:

I have exactly the same sorting problem. In a data list with about 900
records, the first 300 or so sort correctly. The remaining 600 sort
separately from the first 300. There are no blank rows. I¹m only selecting
one cell in the Sort By column when I use the Sort... Command. The entire
column is formatted as decimal numbers and consists of whole five-digit
numbers.

In an unused column, I used the ISTEXT function to test the data in the
³sort by...² column. The 600 that don¹t sort with the others all show TRUE
(they¹re text) while the first 300 all show FALSE (they¹re numbers).

I don¹t know why that is so, since the entire column is formatted as
numbers, and there is no text whatever that I can find in it.

I tried your Paste Special suggestion below. I selected all the ³text² cells
and applied the Paste Special. However, it only affected the topmost cell,
which changed to Number. The remaining cells all stayed as Text even though
they were included in the Paste Special.

Then I discovered something strange: I put the cursor in the formula bar, to
see if I could find any spaces hiding in the current ³Text² cell. There were
none. But when I then hit Return, that cell suddenly became a Number cell.

Evidently my data is hiding something that doesn¹t belong there and occupies
no space. Any idea what this might be? There are 600 odd cells like this. I
might be able to run a macro to fix the problem, but I¹d like to understand
what is going on. I go through this exercise occasionally with other sets of
data from the same source (imported data created by an old Mac program some
years ago), so I¹d like to find an easy way to resolve the problem.

Thanks.

 

[CyberTaz]

Just a guess here - Take a look in Excel>Preferences - Calculation & make
sure it's set to Automatic.

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[Bob Greenblatt]

Bob, a question on the same subject:

I have exactly the same sorting problem. In a data list with about 900
records, the first 300 or so sort correctly. The remaining 600 sort
separately from the first 300. There are no blank rows. I¹m only selecting
one cell in the Sort By column when I use the Sort... Command. The entire
column is formatted as decimal numbers and consists of whole five-digit
numbers.

In an unused column, I used the ISTEXT function to test the data in the
³sort by...² column. The 600 that don¹t sort with the others all show TRUE
(they¹re text) while the first 300 all show FALSE (they¹re numbers).

I don¹t know why that is so, since the entire column is formatted as
numbers, and there is no text whatever that I can find in it.

I'm not sure either, except that that test proves that they are indeed text
and therefore explains why they are sorting the way they are.

I tried your Paste Special suggestion below. I selected all the ³text² cells
and applied the Paste Special. However, it only affected the topmost cell,
which changed to Number. The remaining cells all stayed as Text even though
they were included in the Paste Special.

Something's fishy. Try that again, this time select the entire column, not
just the text cells.

Then I discovered something strange: I put the cursor in the formula bar, to
see if I could find any spaces hiding in the current ³Text² cell. There were
none. But when I then hit Return, that cell suddenly became a Number cell.

This isn't strange at all. Doing this is just like entering a number in a
cell, it gets entered as a number. I'd apply the formatting to the entire
column, not just the errant cells.

Evidently my data is hiding something that doesn¹t belong there and occupies
no space. Any idea what this might be? There are 600 odd cells like this. I
might be able to run a macro to fix the problem, but I¹d like to understand
what is going on. I go through this exercise occasionally with other sets of
data from the same source (imported data created by an old Mac program some
years ago), so I¹d like to find an easy way to resolve the problem.

This is a frequent artifact of importing data from other applications. What
always works is the paste special multiply process. I'm not sure why it is
not working for you.