Sort Formula for TEXT

Thanks Bernie,

This is working great with numbers. However, I'm trying
to sort a list of names (TEXT). I have tried to use
the "code" function to determine the letter code, and got
the list working to a point that it will sort by the first
letter of the name. The tie breaker ceased the further
sorting of the next letter and so on... Please see
example...


Col.A Col.B Col.C Col.D Col.E Col.F
3 USA 85 85 CAR
4 UK 85 85 CANADA
5 USA 85 85 USA
1 CAR 67 67 UK
2 CANADA 67 67.00000001 USA

With A1
=RANK(D1,$D$1:$D$5,1)

B1
Data to be sorted

C1
=IF(ISERROR(CODE(LEFT(B1,1))),"",CODE(LEFT(B1,1)))

D1
=C1+ROW()*0.000000001

F1
=VLOOKUP(ROW(),$A$1:$B$5,2,FALSE)

And everything copied down to ROW 5.


Please advise if it is possible to further sort the name
so then the outcome in Col.F would be:

CANADA
CAR
UK
USA
USA

Your assistance is very much appreciated.

Thanks again,
Aaron




-----Original Message-----
Aaron,

If you want to do that using formulas, then you need to
add 2 helper
columns to your original table - the first will return
the rank of the
value of the second, which must contain some formula that
returns a
number or other value that you can sort on. An example
will help.

Suppose you have

10
6
5
11

Insert a new column A, and in C1, use the formula

= B1 + ROW()*.000000001

This formula use row() as a tie-breaker.

In A1, use the formula

=RANK(C1,$C$1:$C$4)

and copy down to A4.

Now to make your auto-sorting table.

In cell F1, use the formula
=VLOOKUP(ROW(),$A$1:$B$4,2,FALSE)
and copy down for a total of 4 rows.

If your table doesn't start on row 1, then you need to
use something
like this, where your table starts in cell F11:
=VLOOKUP(ROW()-ROW($F10),$A$1:$B$4,2,FALSE)

And as the values in your data table change, your
resulting table will
re-sort automatically.

HTH,
Bernie
MS Excel MVP


"Aaron" wrote in
message
...
Is there a built in formula in Excel for Sort? For
example, I would like to have B1:B10 returned as the
sorted (alphabet) result of A1:A10. I know I could
create
a marco for this, but I am hoping a formula (or complex
multiple formulas) would solve this issue. Thanks in
advance.


.

You've lost us. Why can't you simply sort on Column F?

--
Regards,
Fred
Please reply to newsgroup, not e-mail


"Aaron" wrote in message
...
Thanks Bernie,

This is working great with numbers. However, I'm trying
to sort a list of names (TEXT). I have tried to use
the "code" function to determine the letter code, and got
the list working to a point that it will sort by the first
letter of the name. The tie breaker ceased the further
sorting of the next letter and so on... Please see
example...


Col.A Col.B Col.C Col.D Col.E Col.F
3 USA 85 85 CAR
4 UK 85 85 CANADA
5 USA 85 85 USA
1 CAR 67 67 UK
2 CANADA 67 67.00000001 USA

With A1
=RANK(D1,$D$1:$D$5,1)

B1
Data to be sorted

C1
=IF(ISERROR(CODE(LEFT(B1,1))),"",CODE(LEFT(B1,1)))

D1
=C1+ROW()*0.000000001

F1
=VLOOKUP(ROW(),$A$1:$B$5,2,FALSE)

And everything copied down to ROW 5.


Please advise if it is possible to further sort the name
so then the outcome in Col.F would be:

CANADA
CAR
UK
USA
USA

Your assistance is very much appreciated.

Thanks again,
Aaron




-----Original Message-----
Aaron,

If you want to do that using formulas, then you need to
add 2 helper
columns to your original table - the first will return
the rank of the
value of the second, which must contain some formula that
returns a
number or other value that you can sort on. An example
will help.

Suppose you have

10
6
5
11

Insert a new column A, and in C1, use the formula

= B1 + ROW()*.000000001

This formula use row() as a tie-breaker.

In A1, use the formula

=RANK(C1,$C$1:$C$4)

and copy down to A4.

Now to make your auto-sorting table.

In cell F1, use the formula
=VLOOKUP(ROW(),$A$1:$B$4,2,FALSE)
and copy down for a total of 4 rows.

If your table doesn't start on row 1, then you need to
use something
like this, where your table starts in cell F11:
=VLOOKUP(ROW()-ROW($F10),$A$1:$B$4,2,FALSE)

And as the values in your data table change, your
resulting table will
re-sort automatically.

HTH,
Bernie
MS Excel MVP


"Aaron" wrote in
message
...
Is there a built in formula in Excel for Sort? For
example, I would like to have B1:B10 returned as the
sorted (alphabet) result of A1:A10. I know I could
create
a marco for this, but I am hoping a formula (or complex
multiple formulas) would solve this issue. Thanks in
advance.


.