Bizarre Excel sorting issue

Tofystedeth

So a guy here has an Excel file that has data in 2 columns. Column the first is the model of the unit sold, and the second column is the serial number of the unit. When he tries to sort it by serial number however, one row will not sort. We tried all sorts of things, moving the row to another location at the end or middle of the list. Changing the data type in the cells from general to number. Deleting the data which had been copy/pasted in from a Google spreadsheet and hand entering. It would always just leave that row where it was. The only way we could get it to sort that row, was to remove the 'T' from the end of the model number.

Anybody know why the heck Excel would refuse to sort a row where the data in the cell in the column not being sorted by has a 'T' at the end?

edit: the other model numbers are a mix of 3 digit numbers, and 3 digit numbers ending in other letters, such as K or D. Those work fine.

Kipling

Tofystedeth wrote: »

So a guy here has an Excel file that has data in 2 columns. Column the first is the model of the unit sold, and the second column is the serial number of the unit. When he tries to sort it by serial number however, one row will not sort. We tried all sorts of things, moving the row to another location at the end or middle of the list. Changing the data type in the cells from general to number. Deleting the data which had been copy/pasted in from a Google spreadsheet and hand entering. It would always just leave that row where it was. The only way we could get it to sort that row, was to remove the 'T' from the end of the model number.

Anybody know why the heck Excel would refuse to sort a row where the data in the cell in the column not being sorted by has a 'T' at the end?

edit: the other model numbers are a mix of 3 digit numbers, and 3 digit numbers ending in other letters, such as K or D. Those work fine.

Without the file, that is impossible to replicate. But there is an answer if it is the top row.

If it is the top row, then Excel is assuming that the first row is the header row and won't sort it. When you remove the T, then Excel thinks that the list has no header, because of the random nature of the data types in the columns.

If it isn't the top row, the other option is to try sorting portions of the data set - rows 5 through 10. What happens then?

Soggybiscuit

Another thing to check would be data types. That one row could be set up as a text data type while the others are numbers for example. I always use "General" for ease of sorting when mixing text/numbers.

Crashtard

Definitely sounds like a header row issue. When you click Data > Sort, make sure you uncheck the Header Row button. If the row that wasn't sorting was the top row that should solve your issue.

uean

Yerah. Header rows.

Insert a header onto each. "MODEL" and "SERIALNO"
Highlight both.
In excel 2003, turn on Autofilter under the data tab
In excel 2007, just click the Filter button on the Home tab.
Use the dropdown arrows to sort your data.

Tofystedeth

The weird thing though was that if that data was moved to another row it would still not sort it. It would just sit where it was while everything else was sorted around it.

He did some more fooling around with it yesterday after I posted and found that if he moved that row to the bottom before he copied and pasted it into a new excel sheet it wouldn't do it. So it could be some kind of weird header thing. It just behaved in a very strange way.