On Mon, 28 Nov 2005 16:36:58 -0600, jshrader
wrote:


I am trying to write a sheet where people will send in a form to sign up
(name, address, phone #, etc.). However, when it gets compiled to
submit, all contact must be printed in alphabetic order based on last
name. I know that I can manually choose DATA SORT. This is
taking too much time to continutlaly add and delete lines based on
alphabetic order.

My sheet is setup like:
Column B is Last Name;
Column C is First Name;
Column D is Address;
Column E is City;
Coulmn F is State; and
Column G is Zip.

If I had a "rank" like feature for alphabetic order I could add column
A/B so that it would put in the rank for each contact. Then on the
other sheet, I could do a VLOOKUP for each rank (1, then 2, then 3,
etc.) and pull the other fields in as needed. This way I do not have
to take the time to worry about alphabetic order (adding lines, etc.)
and re-sorting.

Is this possible? If there is not an excel function, so you see any
other way to accomplish? Thanks in advance for your insight and
assistance!!!

You might want to try Longre's VSORT function.

You'll need to download and install his free add-in: morefunc.xll from
http://xcell05.free.fr

Then enter it as an array over a range that is larger than you expect to need.
In other words, if you expect 1000 entries on Sheet1 (in range B2:E1000), you
might enter the formula on Sheet2!B2:E2000.

The formula might look like:

=VSORT(Sheet1!B2:G2000,Sheet1!B2:B2000,1)

Remember, this needs to be array-entered into, for example, Sheet2!B2:G2000.
To do that, select Sheet2!B2:G2000. The active cell should be B2.

Enter the formula into the formula bar, then hold down <ctrl<shift while
hitting <enter. Excel should place braces {...} around the formula and also
fill in every cell in the range with the same formula.

It should return a blank where there is no entry.

The formula is volatile and as you add lines to the table in Sheet1, it will
automatically display the sorted rows on sheet2.


--ron