Formula to sort a range of names in a column

[Kenneth Näslund]

I am running Excel 97 and my problem is as follows

In col A is a range of names of individuals. Only one name per cell and
its entered directly into the the
cell. They are not arranged in any particullary order. In col B I would
like to enter a formula, referring
to the name range in col A, which then sort the names in either
ascending or descending order.

How would such a formula look like and is it possible at all?

I am aware of the Data - Sort command, but I can not use that, since the
workbook is used by people
with big variations in Excel knowledge. Risk of errors are too big. At
present I am using a simple
VBA-routine to perform the task, but it would be a lot better to do it
with formulas instead.

Col A Col B

Smith Anderson
Kelly Bridges
Bridges Kelly
Miller Miller
Anderson Smith

Hopeful to find someone outthere who can give me some ideas.

Kenneth Näslund

[Laurent Longre]

Bonjour,

If you want an add-in function, then you can try this XLL:

http://perso.wanadoo.fr/longre/excel/pages/Downloads.htm#Morefun.xll

This is a set of add-in worksheet-functions, to copy in the Library
folder and install with the add-in manager ("Tools" menu). These
functions appear in a new category in the function wizard. Documentation
and help file are in French, sorry.

TRIV is an array function which "sorts" ranges containing up to
10/20,000 cells, by 1 to 14 or more sort keys.

If you want to have the contents of A2:A800 automatically sorted in the
range B2:B800 in the increasing order, the formula is =TRIV(A2:A800;1)
(array formula: Ctrl-Shift-Enter), and =TRIV(A2:A800;0) in the
decreasing order. Blank cells are replaced by empty strings.

If you prefer to use just standard functions, check out this workbook:
http://perso.wanadoo.fr/longre/excel/downloads/Triform.zip.
It contains two examples of sort formulae (the one with intermediate
calculations, and a single stand-alone array function, which works much
faster).

HTH,

Laurent

[Bob Umlas]

Assume your names in column A are named "range"
In B1, array-enter:
=ROWS(range)+1-SUM((A1<=range)*1)
Fill down
This will give an index # used in the next formula
In C1, enter (not array-enter):
=INDEX(range,MATCH(ROW(),OFFSET(range,,1),0))
fill down.
Hide column B. Col C contains the sorted results.
If you NEED to have the results in B, put what I originally said to put in B
in some other column, say G (& hide it), and have column B's offset formula
(which was in C) be changed accordingly. For example, if it were G, then in
Column B enter
=INDEX(range,MATCH(ROW(),OFFSET(range,,6),0)) where the 6 is 6 collumns away
from A, or G.

Kenneth Näslund wrote in message <35A71F45...@interact.se>...