Formula to rank cells alphabetically
 

I have a list of names, e.g.

Bloggs, J
Smith, P
Peters, F

I want a formula that can rank these in order so the output would be

Bloggs, J 1
Smith, P 3
Peters, F 2

I know the sort function could be used but I need a formula.

I know the Rank function will do this for numbers but does not work on alpha
fields.
Can anyone help?

Thanks in advance

Formula to rank cells alphabetically
 

=COUNTIF($A$1:$A$5,"<"&A1)+1

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Dave Shaw" wrote in message
...
I have a list of names, e.g.

Bloggs, J
Smith, P
Peters, F

I want a formula that can rank these in order so the output would be

Bloggs, J 1
Smith, P 3
Peters, F 2

I know the sort function could be used but I need a formula.

I know the Rank function will do this for numbers but does not work on
alpha
fields.
Can anyone help?

Thanks in advance

Formula to rank cells alphabetically
 

Fantastic - so simple. I had started making a complicated formula based upon
code, left, right, etc.

Many thanks

Dave

"Bob Phillips" wrote:

=COUNTIF($A$1:$A$5,"<"&A1)+1

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Dave Shaw" wrote in message
...
I have a list of names, e.g.

Bloggs, J
Smith, P
Peters, F

I want a formula that can rank these in order so the output would be

Bloggs, J 1
Smith, P 3
Peters, F 2

I know the sort function could be used but I need a formula.

I know the Rank function will do this for numbers but does not work on
alpha
fields.
Can anyone help?

Thanks in advance