Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sort not sorting! (2000)

    We have a spreadsheet (attached) which is sorted on the third collumn, Site.

    For some strange reason site number 509 stays at the top of the list where it's not supposed to be!

    The file was created previously by somebody else and originally included additional collumns and some VB which has been deleted.

    Any offers?

    I told my colleugues that I don't know the answer but I do know a place where I can go and ask a few thousand people if they know the answer!

    Regards

    Graeme

  2. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sort not sorting! (2000)

    Column C contains a mixture of numbers and text. Cells C2:C7 are all stored as numbers whilst the rest of the column is stored as text. If you change these values to text your sort should work.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sort not sorting! (2000)

    If you set horizontal alignment of column C to general, you will see that this column contains a mixture of text and number values. Perhaps you'll have better luck if you format column C as text. But if you're using Windows XP, you'll probably have to resort to tricks to get the sorting OK.

  4. #4
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort not sorting! (2000)

    The odds of a solution being the right one when two replies offer the same advice are good. Sadly not this time!

    509 still sits stubbornly at the top of the list!

    I

  5. #5
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sort not sorting! (2000)

    Replace 509 with '509 (or copy and paste from one of the 509's that are further down the list)
    This will ensure it is text rather than a number.

  6. #6
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort not sorting! (2000)

    Sorted!

    'Scuse the pun!!!

    Regards

    Graeme

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sort not sorting! (2000)

    Does the attached do what you want? I put formulas in column S and sorted on that column. S2 contains ="T"&C2, and so on; the T forces all values to be interpreted as text.

  8. #8
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort not sorting! (2000)

    I've done as Tony suggested. However, that's a neat trick which can no doubt be used again in the future.

    Thanks

    Regards

    Graeme

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort not sorting! (2000)

    Just changing the format of a cell does not change what is in the cell. If a cell contains a number, and you change the format to Text, the cell will still contain a number. You have to change the format to Text, then re-enter a text value into the cell. There are a number of ways to convert the numberic value that is alread there to text that do not require you to retype all of the values. However, it sounds like you already have the problem solved.
    Legare Coleman

  10. #10
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort not sorting! (2000)

    Thanks

    All ok, sorted and printed. I'll now be able to see what's happening when I import next month's data! All part of that never ending learning curve!

    Graeme

  11. #11
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort not sorting! (2000)

    Legare's advice also applies the other way round, i.e. if you have a 'number' that has been entered into a cell formatted to text, changing the cell format to number or general will not turn the 'number' into a value. One trick for this situation is to enter 1 into a spare cell then copy and paste special multiply this onto the 'numbers' .

    stuck

Posting Permissions

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