Excel sort abnormality?

Discussion in 'Software' started by NoGeekMe, Apr 15, 2012.

  1. NoGeekMe

    NoGeekMe Private E-2

    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!
     

    Attached Files:

  2. pwillener

    pwillener MajorGeek

    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...
     
  3. NoGeekMe

    NoGeekMe Private E-2

    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!
     
  4. pwillener

    pwillener MajorGeek

    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... :confused
     
  5. NoGeekMe

    NoGeekMe Private E-2

    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? LOL
     
  6. plodr

    plodr Major Geek Super Extraordinaire

  7. pwillener

    pwillener MajorGeek

    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...
     
  8. NoGeekMe

    NoGeekMe Private E-2

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

    tonyhale Lounge Lizard No.2

    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
     
  10. pwillener

    pwillener MajorGeek

    How did you reformat values?

    What Excel version did you use?
     
  11. pwillener

    pwillener MajorGeek

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

    Attached Files:

  12. tonyhale

    tonyhale Lounge Lizard No.2

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

    NoGeekMe Private E-2

    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.
     
  14. tonyhale

    tonyhale Lounge Lizard No.2

    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
     
  15. NoGeekMe

    NoGeekMe Private E-2

    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?

     

MajorGeeks.Com Menu

Downloads All In One Tweaks \ Android \ Anti-Malware \ Anti-Virus \ Appearance \ Backup \ Browsers \ CD\DVD\Blu-Ray \ Covert Ops \ Drive Utilities \ Drivers \ Graphics \ Internet Tools \ Multimedia \ Networking \ Office Tools \ PC Games \ System Tools \ Mac/Apple/Ipad Downloads

Other News: Top Downloads \ News (Tech) \ Off Base (Other Websites News) \ Way Off Base (Offbeat Stories and Pics)

Social: Facebook \ YouTube \ Twitter \ Tumblr \ Pintrest \ RSS Feeds