MajorGeeks Support Forums

Go Back   MajorGeeks Support Forums > ----------= PC, Desktop and Laptop Support =---------- > Software
Register FAQ Members List Calendar Casino Mark Forums Read

Software Software such as operating systems like Windows XP, Windows Vista, Windows 7 etc., or specific programs.


Closed Thread
 
Thread Tools Display Modes
  #1  
Old 04-15-12, 18:39
NoGeekMe's Avatar
NoGeekMe NoGeekMe is offline
Private First Class
 
Join Date: Oct 2004
Posts: 44
Thanks: 5
Thanked 0 Times in 0 Posts
Unhappy Excel sort abnormality?

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
File Type: zip Sort Abnormality.zip (4.4 KB, 10 views)
Sponsored links
  #2  
Old 04-16-12, 00:03
pwillener's Avatar
pwillener pwillener is offline
Major Geek
 
Join Date: Feb 2007
Location: Tokyo, Japan
Posts: 1,653
Thanks: 17
Thanked 274 Times in 246 Posts
Default Re: Excel sort abnormality?

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  
Old 04-16-12, 00:31
NoGeekMe's Avatar
NoGeekMe NoGeekMe is offline
Private First Class
 
Join Date: Oct 2004
Posts: 44
Thanks: 5
Thanked 0 Times in 0 Posts
Default Re: Excel sort abnormality?

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  
Old 04-16-12, 01:54
pwillener's Avatar
pwillener pwillener is offline
Major Geek
 
Join Date: Feb 2007
Location: Tokyo, Japan
Posts: 1,653
Thanks: 17
Thanked 274 Times in 246 Posts
Default Re: Excel sort abnormality?

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...
  #5  
Old 04-16-12, 16:16
NoGeekMe's Avatar
NoGeekMe NoGeekMe is offline
Private First Class
 
Join Date: Oct 2004
Posts: 44
Thanks: 5
Thanked 0 Times in 0 Posts
Default Re: Excel sort abnormality?

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?
Sponsored links
  #6  
Old 04-16-12, 17:37
plodr's Avatar
plodr plodr is offline
Major Geek Extraordinaire
 
Join Date: Sep 2007
Location: at my computer
Posts: 14,497
Thanks: 207
Thanked 2,643 Times in 2,447 Posts
Default Re: Excel sort abnormality?

Comma Separated Values file
http://en.wikipedia.org/wiki/Comma-separated_values
__________________
7" ASUS eeepc lubuntu 14.04
Aspire V5 7 HomePremium
  #7  
Old 04-17-12, 02:37
pwillener's Avatar
pwillener pwillener is offline
Major Geek
 
Join Date: Feb 2007
Location: Tokyo, Japan
Posts: 1,653
Thanks: 17
Thanked 274 Times in 246 Posts
Default Re: Excel sort abnormality?

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...
The Following User Says Thank You to pwillener For This Useful Post:
NoGeekMe (04-18-12)
  #8  
Old 04-17-12, 12:33
NoGeekMe's Avatar
NoGeekMe NoGeekMe is offline
Private First Class
 
Join Date: Oct 2004
Posts: 44
Thanks: 5
Thanked 0 Times in 0 Posts
Default Re: Excel sort abnormality?

Thanks Plodr! Right after I asked the silly question I googled my way to the same link !
  #9  
Old 04-17-12, 16:16
tonyhale's Avatar
tonyhale tonyhale is offline
Lounge Lizard No.2
 
Join Date: Mar 2009
Location: HALE VILLAGE ENGLAND
Posts: 853
Thanks: 145
Thanked 73 Times in 66 Posts
Default Re: Excel sort abnormality?

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
__________________
Never put off till tomorrow what you can put off for ever
  #10  
Old 04-17-12, 21:23
pwillener's Avatar
pwillener pwillener is offline
Major Geek
 
Join Date: Feb 2007
Location: Tokyo, Japan
Posts: 1,653
Thanks: 17
Thanked 274 Times in 246 Posts
Default Re: Excel sort abnormality?

Quote:
Originally Posted by tonyhale View Post
Reformatted values ...
How did you reformat values?

What Excel version did you use?
Sponsored links
  #11  
Old 04-17-12, 21:28
pwillener's Avatar
pwillener pwillener is offline
Major Geek
 
Join Date: Feb 2007
Location: Tokyo, Japan
Posts: 1,653
Thanks: 17
Thanked 274 Times in 246 Posts
Default Re: Excel sort abnormality?

Attached file contains
  • SortCSV.xlsx - clean result from CSV file
  • SortOut.xlsx - clean result from SQL table
Attached Files
File Type: zip Sort.zip (20.9 KB, 2 views)
The Following User Says Thank You to pwillener For This Useful Post:
NoGeekMe (04-18-12)
  #12  
Old 04-18-12, 08:07
tonyhale's Avatar
tonyhale tonyhale is offline
Lounge Lizard No.2
 
Join Date: Mar 2009
Location: HALE VILLAGE ENGLAND
Posts: 853
Thanks: 145
Thanked 73 Times in 66 Posts
Default Re: Excel sort abnormality?

Quote:
Originally Posted by pwillener View Post
How did you reformat values?

What Excel version did you use?
Formated A & B as Date & C as Time. MS Office 2007
__________________
Never put off till tomorrow what you can put off for ever
  #13  
Old 04-18-12, 11:05
NoGeekMe's Avatar
NoGeekMe NoGeekMe is offline
Private First Class
 
Join Date: Oct 2004
Posts: 44
Thanks: 5
Thanked 0 Times in 0 Posts
Default Re: Excel sort abnormality?

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  
Old 04-18-12, 12:34
tonyhale's Avatar
tonyhale tonyhale is offline
Lounge Lizard No.2
 
Join Date: Mar 2009
Location: HALE VILLAGE ENGLAND
Posts: 853
Thanks: 145
Thanked 73 Times in 66 Posts
Default Re: Excel sort abnormality?

Quote:
Originally Posted by NoGeekMe View Post
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
__________________
Never put off till tomorrow what you can put off for ever
  #15  
Old 04-18-12, 14:50
NoGeekMe's Avatar
NoGeekMe NoGeekMe is offline
Private First Class
 
Join Date: Oct 2004
Posts: 44
Thanks: 5
Thanked 0 Times in 0 Posts
Question Re: Excel sort abnormality?

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?

Quote:
Originally Posted by tonyhale View Post
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
Sponsored links
Closed Thread

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2010, Microsoft Excel is waiting for another application to complete an OLE act kfischer79 Software 1 12-19-11 17:39
Can only open excel documents from within excel, right clicking won't work asphaltninja Software 1 09-11-09 16:31
Need help with an Excel date sort bigbazza Software 6 02-24-06 15:12
Strange rippling screen abnormality PC Fool Hardware 4 01-21-06 04:20
Disk space abnormality kreten Malware Removal 1 11-29-05 12:11


All times are GMT -5. The time now is 16:38.

MajorGeeks.Com Menu

MajorGeeks.Com \ 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 \ NEW! PC Games \ System Tools \ Macintosh \ Demonews.Com \ Top Downloads

MajorGeeks.Com \ News (Tech) \ Off Base (Other Websites News) \ Way Off Base (Offbeat Stories and Pics)

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


All content Copyright MajorGeeks.com source code Powered by vBulletin® Version 3.8.4
Copyright © 2009 vBulletin Solutions, Inc. All rights reserved.
Ad Management by RedTyger