+ Reply to Thread
Results 1 to 9 of 9

Ranking in alphabetical order

  1. #1
    Forum Contributor
    Join Date
    08-21-2006
    Location
    Ossett, West Yorkshire
    MS-Off Ver
    2003
    Posts
    150

    Ranking in alphabetical order

    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

  2. #2
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Can you post an example of ranges and gaps and expected result.

    VBA Noob
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  3. #3
    Forum Contributor
    Join Date
    08-21-2006
    Location
    Ossett, West Yorkshire
    MS-Off Ver
    2003
    Posts
    150

    Ranking in Alphabetical Order

    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

  4. #4
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    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

  5. #5
    Forum Contributor
    Join Date
    08-21-2006
    Location
    Ossett, West Yorkshire
    MS-Off Ver
    2003
    Posts
    150
    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

  6. #6
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Surname then ??

    What is your expected result.

    No gaps ??


    VBA Noob

  7. #7
    Forum Contributor
    Join Date
    08-21-2006
    Location
    Ossett, West Yorkshire
    MS-Off Ver
    2003
    Posts
    150
    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

  8. #8
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    Quote Originally Posted by 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
    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
    ---
    Last edited by Bryan Hessey; 11-07-2006 at 07:32 AM.
    Si fractum non sit, noli id reficere.

  9. #9
    Forum Contributor
    Join Date
    08-21-2006
    Location
    Ossett, West Yorkshire
    MS-Off Ver
    2003
    Posts
    150

    Thanks

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1