Is there a "rank" like feature for alpha?

[jshrader]

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

 

[DOR]

Record a simple Sort macro and then attach it to a button on your
worksheet or put it in the Worksheet module, with the name

Private Sub WorkSheet_Deactivate()

...sort statements

End Sub

That will cause the macro to be run every time you deactivate the
sheet. However, you have to be careful to deactivate events and
reactivate within the macro. The following macro has worked for me to
sort a sheet called Results, but I don't claim any great VBA skills so
don't take it as being the best practice!

Private Sub WorkSheet_Deactivate()
'Sort the entered results whenever the Results sheet is deactivated
Dim strMySht As String
strMySht = ActiveSheet.Name
Application.ScreenUpdating = False
Worksheets("Results").Select
Range("A1").CurrentRegion.Select
Selection.Sort _
Key1:=Range("EntryDate"), Order1:=xlAscending, _
Key2:=Range("HomeTeam"), Order2:=xlAscending, _
Header:=True
Range("A1").Select
Application.EnableEvents = False
Worksheets(strMySht).Activate
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

You will need to substitute your own range names for the sort columns.
- substitute a reference in the Surname column for Entry Date and a
reference in the First Name column for Home Team, and substitute the
name of your entry sheet for Results

I may not have chosen the best way to deal with deactivating events
because if you get a failure in the next statement, events will stay
deactivated and you will need to run the macro explicitly again (press
F5), after correcting it, or restart Excel.

You may choose to run this automatically when the workbook is open or
closed instead by putting it in the Workbook VBA module.

Or you could do it the hard way by generating a sorting number from
each name using base 27 arithmetic (26 alpha plus space, ignoring
apostrophes et al, a=1, b=2, c=3 etc.) and rank based on those numbers.
You need not use all the letters of each name, the first 3 or 4 would
be enough to get close enough to alpha, but note that in using RANK for
sorting you will need to deal with ties. There are many threads on
these forums dealing with methods for doing that, if your eally want
toi use base 27 arithmetic to get quasi-alphabetic sequence.

HTH

Declan O'R

 

[bpeltzer]

If your data is in B1:B5, then =COUNTIF($B$1:$B$5,">"&B1)+1 should effect a
RANK of B1 within that range. Like RANK, ties will result in multiple inputs
having the same rank. (You could use >= without the ending +1, but that
would handle ties somewhat differently. Instead of having an array of
A,B,B,C,E result ranks of 1,2,2,4,5 you'd get 1,3,3,4,5.)
HTH. --Bruce

 

[DOR]

Really nice concise formula, Bruce - beats the heck out of my
suggestions, although the sort on deactivate could work ok. However, I
think you used > in your formula when you meant <. At least that is
what is suggested by your examples in your last sentence.

I suggest a modification that will provide a unique number for all
names, including duplicates, which will have consecutive numbers. If
your data is in B2:B30, use

=COUNTIF($B$2:$B$30,"<"&B2)+COUNTIF($B$2:B2,B2)

Enter normally and drag down.

HTH

Declan

 

[Ron Rosenfeld]

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

 

[bpeltzer]

Thanks for the comment. And you're right; the example at the end did suggest
<. But > gives a result more consistent with RANK, in that both would give
the rank '1' to the item listed first in an ascending sort. Either way, you
can effect a sort without performing a sort. And I like the second countif
with a varying range to break the ties. --BP

 

[bpeltzer]

.... sorry, that should be descending ...