Sorting Problem

[Guest]

Hi I am having trouble trying to sort a group of numbers with letters. Below
are some examples of what I am trying to sort and in the correct order. The
first number represents the floor the groups are on 1, 2, or 3. Second, is
the section within that floor. So "A" would be at the front of the floor, "B"
is the section behind it, so on and so forth. The third letter is
insignificant and should not factor into the sorting. The next two numbers
represent the group within the section and the last two numbers represent the
item within the group. So... 1AW0601 would be on the first floor (1) section
"A" (A) the 6th group (06) and the first item (01). I know this is very
confusing. Any help getting these sorted would be greatly appreciated. Thanks

1AW0601
1AW0804
2CC0401
2DF0402
3BB0406
3BW1201

 

[Ardus Petus]

Create a helper column with formula (say original data is in A1):
=LEFT(A1,2)&MID(A1,4,255)
Sort by that new column

HTH

 

[Mallycat]

I would suggest parsing out your data into extra columns, then you can
sort on those as you like.

Assuming your data is in column A
Heading in A1
First data in A2,
then in B1 enter Floor
in B2 enter left(A2,1)
in C1 enter section
in C2 enter mid(A2,2,1)
in D1 enter Section Group
in D2 enter mid(A2,4,2)

and so on

 

[Guest]

Thank you for the reply, however, when I try to sort that new column, it will
not do anything. If I manually type the same numbers in, it will sort them.
Any suggestions?

 

[starguy]

select all new columns and copy them, then right click on your selectio
(don't deselect the columns) and click past special, click past
values
your formulas are changed to the values they return. now you can sor
these columns as per your preferences.

 

[RagDyeR]

If *all* your data is exactly as you posted in your example, then TTC (Text
To Columns) is perfect for what you're looking to accomplish.

TTC can separate your data into individual columns *without* changing
(revising) your original data, where you can then set your sort keys, per
column, in any order that you wish, and then "throw away" those individually
parsed columns, leaving your original data behind, sorted as desired.


You should allow for 5 empty columns to the right of your data.
If necessary, you can *temporarily* insert those columns.

Select the column of data, then,
<Data> <TextTo Columns>
Click on "Fixed Width", then <Next>

Click in the 'Data Preview' window to place the break lines so that the data
is separated into the groups that you wish to sort by, such as:

3 | B | B | 04 | 06

Then <Next>

The 'Destination' box, by default, contains the address of your original
data.
To preserve your original data intact, change that address to the first of
your temporary columns, say if your data started in A1, the just enter B1.
Then <Finish>

You now should select your original data *and* all of the temporary 5
columns.
Since you have 4 sort keys, sort first on the *last* temp column ( F ),
Then you can sort on the other 3 (B, D, and E).

When you're finished, and everything checks out OK, just delete those 5
'helper' columns.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


Thank you for the reply, however, when I try to sort that new column, it
will
not do anything. If I manually type the same numbers in, it will sort them.
Any suggestions?