Sorting

  • Thread starter Thread starter BK
  • Start date Start date
B

BK

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.
 
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

--
HTH

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Good idea!!


Sandy Mann said:
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

--
HTH

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
Click to expand...
 
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.
 
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.
 
Very cool!!! Thanks~!!



Ragdyer said:
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.
Click to expand...
 
Back
Top