+ Reply to Thread
Results 1 to 16 of 16

numbers not sorting correctly

  1. #1
    Registered User
    Join Date
    07-05-2013
    Location
    VT, USA
    MS-Off Ver
    Excel 2007
    Posts
    10

    numbers not sorting correctly

    Me and my cemetery records again. You guys have been so helpful. My latest silly problem:

    I have three columns expressing death dates (date, month, year). The months and dates are finally sorting correctly within the same year, but I have it set to sort by year first.

    The column starts off fine with 1860, goes up to 2013, then starts back at 1884 and goes back up to 2009. I can't find any difference between the two groups: there are some of the same years represented, there are blank month and date cells in both. I have a worksheet with birth dates that sorted fine; the only difference I can find is that birth dates only go up to 1997, but the column is formatted as General, so the turn of the century shouldn't be a problem.

    I've tried highlighting the whole thing before sorting as well as selecting one cell as usual. I've also tried formatting the column as Text. I can't think of anything else.

    Thank you for your help.
    Last edited by JMTMayor; 07-31-2013 at 10:20 PM. Reason: tried something else

  2. #2
    Forum Contributor
    Join Date
    07-30-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    121

    Re: numbers not sorting correctly

    Is all the information in the Cell entered as a Date or could some of it be text (with a ' in front of the first piece of data). This would cause the dates to sort before the text and therefore give you this secondary reading.
    Date cells will drop a leading zero and align to the right (default setting), whereas a text cell will retain a leading zero and align left.

  3. #3
    Registered User
    Join Date
    07-05-2013
    Location
    VT, USA
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: numbers not sorting correctly

    Thanks for replying. No, it's all just numbers (cell format General) with no extra stuff. I haven't pasted differently or anything. My other date worksheets (birth dates and burial dates) are exactly the same thing and are fine. I'm willing to rearrange them manually (there are only 850ish entries, but this is silly.

  4. #4
    Forum Contributor
    Join Date
    07-30-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    121

    Re: numbers not sorting correctly

    Got a sample of your data with the wierd sort info showing?

  5. #5
    Registered User
    Join Date
    07-05-2013
    Location
    VT, USA
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: numbers not sorting correctly

    A new development: when I only sort the years and months it's fine; when I add a third level to sort by date, then it sifts into the two groups I mentioned. I've tried the date column as Text and General. If I can get it this close (sorted at least by month and year), it's not such a big deal to fix it by cutting and pasting. Still silly.

    Don't laugh: how do I attach?
    Last edited by JMTMayor; 07-31-2013 at 11:51 PM.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,883

    Re: numbers not sorting correctly

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  7. #7
    Registered User
    Join Date
    07-05-2013
    Location
    VT, USA
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: numbers not sorting correctly

    sample.xlsx

    Thought I'd figured out how to attach - not sure


    Oh now I see FDibbins's reply; thank you!
    Last edited by JMTMayor; 08-01-2013 at 12:01 AM.

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,883

    Re: numbers not sorting correctly

    yes that worked

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,883

    Re: numbers not sorting correctly

    I just sorted your data and it seems to have sorted just fine?

    What I did was to 1st highlight the entire range (select C2, shift end/down, left left) then go into sort (not a-z or z-a, use the button that says sort), then select 1st sort = C, 2nd = A and 3rd = B

  10. #10
    Registered User
    Join Date
    07-05-2013
    Location
    VT, USA
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: numbers not sorting correctly

    That's what I did! Are you sure it's right? It should end with 2013, not 2009.

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,883

    Re: numbers not sorting correctly

    mine ended with 2013...
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    07-05-2013
    Location
    VT, USA
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: numbers not sorting correctly

    Oh. It's me, then. Okay, thank you. I'm going to bed, but tomorrow I'll try one more time and if it doesn't work just fix it by hand. If I only sort by month and year it's close.

    Thanks again for your time.

  13. #13
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: numbers not sorting correctly

    ok its the same problem AYdata eluded to in post #2 (minus the ')
    all the cells with green triangle in top left is taking cell as TEXT

    in fdibbins spreadsheet none of the column C has green triangle which is why you could just sort column C properly


    the quickest way i found to refresh all text columns to general is to select the whole column of data

    click Text to columns>finish
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  14. #14
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,883

    Re: numbers not sorting correctly

    not so, hum. there are still plenty of "text" numbers there (row 5, 8 and 15 are examples). I just told it to sort "anything that looks like a number, as a number", but thanks for bringing that up, I meant to mention the "sort as numbers" option when sorting.

    Also, if you highlight the column, starting from the 1st green triangle, then scroll back up to that 1st triangle, right click and select "convert to numbers" will do the same as the T2C

  15. #15
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: numbers not sorting correctly

    Right
    thats what happened
    it stored that check when i tried to sort because i tried to sort again and it didnt ask me about column C but did for Column B and made 2009 down bottom again

    arr thats the trick to make it work
    i couldnt get the pop up box to show up
    so it has to be on first text field

    Thanks FDibbins learnt something new

  16. #16
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,883

    Re: numbers not sorting correctly

    any time, and thanks for the feedback

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] The numbers in an alphanumeric string are not sorting correctly
    By sweetroman in forum Excel General
    Replies: 14
    Last Post: 07-19-2013, 10:46 AM
  2. Replies: 2
    Last Post: 07-18-2013, 06:18 PM
  3. Excel not sorting numbers correctly
    By lpexcel in forum Excel General
    Replies: 2
    Last Post: 04-04-2012, 08:50 AM
  4. Not sorting correctly
    By Josh_123456 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-10-2008, 03:48 PM
  5. Sorting numbers doesn't work correctly
    By GrammyEmmy in forum Excel - New Users/Basics
    Replies: 8
    Last Post: 06-25-2006, 06:50 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1