Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / January 2008

Tip: Looking for answers? Try searching our database.

Sorting

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
BK - 15 Dec 2007 19:03 GMT
Using Excel 2003 on an XP machine.

In Column A, I have teams listed as follows:  Team 1, Team 2, Team 3, . . .
Team 10

When I sort this column, I get Team 1 followed my Team 10  followed by Team
2.  I understand why this is happening, but I'm wondering is there is some
what to format the cell entry so that the teams will be sorted numerically.

The only work around I have found so far is to change "10" to "ten" for the
sorting procedure and then changing "ten" back to "10" when I'm done.
Sandy Mann - 15 Dec 2007 19:34 GMT
Inserrt a new Column B and enter the numbers 1 - 10.  Hide column B and then
select the data that you want to sort and sort by Column B

Signature

HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

> Using Excel 2003 on an XP machine.
>
[quoted text clipped - 8 lines]
> The only work around I have found so far is to change "10" to "ten" for
> the sorting procedure and then changing "ten" back to "10" when I'm done.
BK - 15 Dec 2007 19:40 GMT
Good idea!!

> Inserrt a new Column B and enter the numbers 1 - 10.  Hide column B and
> then select the data that you want to sort and sort by Column B
[quoted text clipped - 11 lines]
>> The only work around I have found so far is to change "10" to "ten" for
>> the sorting procedure and then changing "ten" back to "10" when I'm done.
BK - 24 Dec 2007 13:39 GMT
Know what I did?  I actually used "text to columns - delimited" to separate
the numerals from the word "team."  (I actually had a long list than the one
I used as my question example.)  After sorting on the column of numerals, I
went back and used the "concatenate" function to put them back together.
<grin>

Thanks for your suggestion of separated the numerals from the text.  That
was the key that unlocked the door.

> Inserrt a new Column B and enter the numbers 1 - 10.  Hide column B and
> then select the data that you want to sort and sort by Column B
[quoted text clipped - 11 lines]
>> The only work around I have found so far is to change "10" to "ten" for
>> the sorting procedure and then changing "ten" back to "10" when I'm done.
Ragdyer - 24 Dec 2007 15:30 GMT
You can even make it easier for yourself when using TTC, so that you *don't*
have to put your data back together again (concatenate).

After you click on <Space> as the delimiter, don't click <Finish>.
Click <Next> to get to the 3rd step of the TTC Wizard.

There, you'll see in the "data preview" window, that the first column
containing Team is selected, by default.
Click on "Do Not Import Column (skip)",
Then, in the "Destination" box, change the column letter to the next column
(depending on where your original data was located), meaning:
If the box displays A1 ... change to B1
If the box displays D12 ... change to E12

THEN click <Finish>

This leaves your original data *untouched*, while copying the numbers to the
adjoining column.
Now, just sort both columns on the number column, and then delete the number
column, leaving your original data sorted, and in it's original form as
data, not concatenate formulas that may have to be transformed back to data
values.
Signature

HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

> Know what I did?  I actually used "text to columns - delimited" to separate
> the numerals from the word "team."  (I actually had a long list than the one
[quoted text clipped - 20 lines]
> >> The only work around I have found so far is to change "10" to "ten" for
> >> the sorting procedure and then changing "ten" back to "10" when I'm done.
BK - 02 Jan 2008 22:11 GMT
Very cool!!!  Thanks~!!

> You can even make it easier for yourself when using TTC, so that you
> *don't*
[quoted text clipped - 55 lines]
>> >> the sorting procedure and then changing "ten" back to "10" when I'm
> done.
Ragdyer - 03 Jan 2008 01:22 GMT
You're welcome, and thanks for the feed-back.
Signature

Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

> Very cool!!!  Thanks~!!
>
[quoted text clipped - 62 lines]
> >> >> the sorting procedure and then changing "ten" back to "10" when I'm
> > done.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.