Ranking in alphabetical order

[Gearcutter]

Hi Everyone, I have several lists of names in a column with spaces I would like to rank the list in alphabetical order is this possible?
Regards Howard

[VBA Noob]

Can you post an example of ranges and gaps and expected result.

VBA Noob

[Gearcutter]

Hi my hobby is producing spreadsheets that are soccer related. in this case i have produced a squad list of footballers for each team in the Premiership in squad order. What I want to do is give each name a number based on the alphabet, as of yet I Have not found a function that will do this for me.
Regards Howard

[VBA Noob]

How's the names stored Last Name, First name ??

So do the numbers restart for each team ??

Is Henry say return a 5. Can a team have two players with the same name.

Please post an example of the names and output otherwise I can't help

VBA Noob

[Gearcutter]

Club Arsenal
1 J.Lehman
2 V.A.Diaby
3
4 C.Fabregas
5 K.Toure
6 P.Senderos
7 T.Rosicky
8 F.Ljungberg
9 J.Baptista
10 W.Gallas
11 R.Van Persie
12 Lauren
13 A.Hleb
14 T.Henry
15
16 M.Flamini
17 A.Song
18
19 Gilberto
20 J.Djourou
21 M.Poom
22 G.Clichy
23
24 M.Almunia
25 E.Adebayor
26
27 E.Eboue
28
29
30 J.Aliadiere
31 J.Hoyte
32 T.Walcott
33
34 M.Connolly
35
36
37
38
39
40

45 A.Stokes

Hi, That is the Arsenal 1st team squad with squad numbers, this is repeated for the rest of the teams in the Premiership. hope this of some help.
Howard

[VBA Noob]

Surname then ??

What is your expected result.

No gaps ??


VBA Noob

[Gearcutter]

The result I would like is each name given a number decided by the order in which they would appear if they were in alphabetical order.In an Ideal world both in the context of the team squad and then in the entire Premiership which consists of 20 teams of varying sizes which also have duplicate names, some which have the same 1st name, as in the names A.Cole of Chelsea the A being Ashley and A.Cole of Portsmouth the A.being Andrew there is also a Andy Cole some where as well. At the moment I am distinguishing them by squad number and by giving them a number that is the order in which they appear in the list.
Regards Howard

[Bryan Hessey]

The result I would like is each name given a number decided by the order in which they would appear if they were in alphabetical order.In an Ideal world both in the context of the team squad and then in the entire Premiership which consists of 20 teams of varying sizes which also have duplicate names, some which have the same 1st name, as in the names A.Cole of Chelsea the A being Ashley and A.Cole of Portsmouth the A.being Andrew there is also a Andy Cole some where as well. At the moment I am distinguishing them by squad number and by giving them a number that is the order in which they appear in the list.
Regards Howard

The easy way to do that would be to use 2 spare columns (say AA and AB)

In AA1 put a 1, then CTRL/formula fill that down to cover your data.
this should number the rows in their current position.

Sort the sheet as required, and in column AB - the first row with real data (ignore blanks) put a 1, then CTRL/fill downwards again to the end of your names.

(note, equal keys will retain their relative position when sorted, as per your current system)

Re-Sort the sheet over the AA column.

If you need to repeat this often you can record a macro to assist.

hth
---

[Gearcutter]

Hi, Thanks Bryan,very much for the suggestions which I will do later this evening thanks also to VBA Noob for your interest
Best Regards Howard