Excel sort abnormality?

[NoGeekMe]

I'd be really, really appreciative of any help I can receive with this problem. In both Excel 2002 and 2007, Windows XP SP3 I am getting strange results on a very simple sort of a very simple spreadsheet.

The sheet has two date columns and one time column. The dates are formatted as dates, and the times are formatted as times.

When I sort first by time and second by a date column, four rows do not follow the sort.

I experimented with converting all cells to numbers and I get the same results: the same four lines out of order. I deleted the problem lines and retyped the data into the sheet - same problem. I even recreated the sheet and got the same result.

The spreadsheet is attached. On the top of the sheet I've noted which lines are misplaced, and they are also bolded.

I'd be so grateful if someone can figure this out. It's driving me nuts and created an inaccuracy in a report I did for work. Many thanks!

[pwillener]

I downloaded your zipped spreadsheet and opened it with Excel 2010.

Re-sorted the columns and got the same result as you.

I carefully checked your data (1 vs. l, 0 vs. O, etc.); all seems to be correct!

I converted the spreadsheet to XLSX, and it still won't sort correctly!

You really got me curious now with that problem: I will try some other things, e.g. upload it to my SQL server and analyze the data there. But first I have some other things to do; I will come back later...

[NoGeekMe]

Thanks for verifying I'm not nuts! When I converted all the data to numbers I kept rubbing my eyes, expecting an input error to have revealed itself. It feels surreal. I look forward to what you find!

[pwillener]

This is a mystery!

I have saved the spreadsheet as a CSV, then restored it back to an XLS: now it sorts correctly.

I have imported it to the SQL server, then exported it back to XLS: this also sorts correctly.

This indicates that something with your data is wrong - but what???

I have not yet given up - I am intrigued by mysteries...

[NoGeekMe]

Thank you so MUCH for working on this.

What's a CSV?

This is so strange. What I uploaded had even been inputted by keyboard, it was my test to be sure I wasn't missing a problem I'd created in the original spreadsheet.

Any chance I can have a copy of the .xls that's behaving properly?

[plodr]

Comma Separated Values file
http://en.wikipedia.org/wiki/Comma-separated_values

[pwillener]

I am busy today, but I will upload a copy of a "working" XLS tomorrow.

However, I am still intrigued by this mystery, and I hope to get eventually to the bottom of it. I am quite busy over the next two weeks, but I will again look into this after that.

I have sorted many things with Excel, and I have never seen such blatant misbehavior! I just don't see what's wrong with the data...

[NoGeekMe]

Thanks Plodr! Right after I asked the silly question I googled my way to the same link !

[tonyhale]

Quick look at problem,
Reformatted values - cursor on A2
used right mouse button
Tick My Data has Headers - Sort - Custom Sort - Sort by Session - Values Largest to Smallest - Smallest to Largest.
seems OK

[pwillener]

Originally Posted by tonyhale

Reformatted values ...

How did you reformat values?

What Excel version did you use?

[pwillener]

Attached file contains

• SortCSV.xlsx - clean result from CSV file
• SortOut.xlsx - clean result from SQL table

[tonyhale]

Originally Posted by pwillener

How did you reformat values?

What Excel version did you use?

Formated A & B as Date & C as Time. MS Office 2007

[NoGeekMe]

PWilly: Thank you for the fixed sheets, that was really nice of you!!

TonyHale: Are you sure lines 37, 38, 124 and 125 are in sequence with your sort?

If so, then the data is sorting properly on your machine, but not on the two machines I work on, or on PWillener's.

[tonyhale]

Originally Posted by NoGeekMe

PWilly: Thank you for the fixed sheets, that was really nice of you!!

TonyHale: Are you sure lines 37, 38, 124 and 125 are in sequence with your sort?

If so, then the data is sorting properly on your machine, but not on the two machines I work on, or on PWillener's.

Perhaps not the solution you were looking for but, I copied similar dates and times and pasted them into ROGUE CELLS.
YES ROGUE CELLS!! it works I've had this type of thing in the past, its a pain in the bum

[NoGeekMe]

I'm confused.

What's a rogue cell?

What is the work around?

Using one of the sheets provided by PWillener, I'm recreating the rest of the spreadsheet (which includes several text formatted columns I had to strip from what I'd uploaded, due to confidentiality issues).

Is what I am doing likely to trigger the issue again?

Originally Posted by tonyhale

Perhaps not the solution you were looking for but, I copied similar dates and times and pasted them into ROGUE CELLS.
YES ROGUE CELLS!! it works I've had this type of thing in the past, its a pain in the bum