1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: MS Excel Sort Problem

Discussion in 'Business Applications' started by Usermaatre, Dec 23, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. Usermaatre

    Usermaatre Thread Starter

    Joined:
    Dec 23, 2012
    Messages:
    2
    I am having an issue with sorting in MS Excel. I have a spreadsheet of baseball stats and I am trying to sort by Last Name, then First Name and then Year. My problem is that for some players/years it is placing the years out of order. I have attached a portion of the spreadsheet in question. Any suggestions to what I am doing wrong or why this is happening I would greatly appreciate it.
     

    Attached Files:

  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,408
    First Name:
    Wayne
    the names have spaces included - if you click on wertz in cell B1513 and now click as far to the right of the formula bar you will see the cursor is next to the z
    if you do the same in B1514 you will see you have a bout 8 spaces between the z and the cursor now

    so "wertz" is different to "wertz "
    and so they sort as different names

    you need to get rid of all the spaces

    I would in a new column AA and AB do the following

    AA2 put =trim(A2)
    BB2 put =trim(b2)

    copy those down the length of the spreadsheet

    thats removed all the spaces
    Now Select AA2 to BB ?? end of the spreadsheet

    COPY
    and
    in A2
    do a
    paste special - Value

    that will put back all the names without any spaces now the sort will work OK

    just tried it and for some strange reason not working - so the spaces maybe different non display characters - need to look into a little more - but you could make a copy of your original and try

    just tried clean() which should remove other special characters and thats not working
    probably going to need vba to find out what the characters are and remove

    where did the names come from > did you import from another system at all ?
     
  3. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,408
    First Name:
    Wayne
    i think i sorted it now
    I used pearsons cell view function to identify the character -

    http://www.cpearson.com/excel/CellView.aspx
    http://www.cpearson.com/excel/installinganxla.aspx

    - it appears to be char(160) - so i have used substitute and trim

    =TRIM(SUBSTITUTE(A2,CHAR(160),""))

    so follow the same procedure as before only use the formula above

    so in AA2
    =TRIM(SUBSTITUTE(A2,CHAR(160),""))
    and in AB2
    =TRIM(SUBSTITUTE(B2,CHAR(160),""))

    copy down the sheet
    then use paste special - value - to paste back to cells A2 to B??

    see your sample attached , which appears to have worked now
     

    Attached Files:

  4. Usermaatre

    Usermaatre Thread Starter

    Joined:
    Dec 23, 2012
    Messages:
    2
    etaf,

    Thank you very much for both the solution and pointing out the very nice addin for looking at cells! You had asked about source of this information. I had this information in a .txt file and I copied and pastes and then used text to columns to spread it out over multiple columns. I had tried to copy a few times thinking it was an issue with the name but I did not seem to correct that way but using your method everything worked perfectly! Oh, for clarification, I did not so all of the data at the same time, so I am guessing I used slightly different data when I did text-to-columns so I will need to make sure I am more careful in the future.

    I greatly appreciate your help!!
     
  5. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,408
    First Name:
    Wayne
    your welcome (y) thanks for letting us know
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1082064

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice