Solved: MS Excel Sort Problem

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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.
 

Attachments

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,478
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 ?
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,478
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
 

Attachments

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!!
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Members online

Top