+ Reply to Thread
Results 1 to 13 of 13

Sorting column alphabetically by surname

  1. #1
    Registered User
    Join Date
    02-17-2017
    Location
    England
    MS-Off Ver
    2007
    Posts
    16

    Sorting column alphabetically by surname

    I've already got this code on to sort a column alphabetically so that whenever I add a name to the bottom of the list, the column re-sorts itself to accommodate the new name..

    Please Login or Register  to view this content.
    What I'm asking is if there is a way to edit this code so that the column sorts by the surname and not the first name?

    I've seen methods that involve a column of names that will never change, but my list is constantly changing by adding new names in and deleting old names

    Thanks for any help

  2. #2
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Excel 2010
    Posts
    3,274

    Re: Sorting column alphabetically by surname

    typically Range("A1") would be changed to encompass the whole range, and the Key1 parameter of your sort is the column to sort by, so if you change the range, and change the key to B2 instead of A2, then it will sort by the surname (assuming surname is in column B). If I assume you have data in columns A through D for example, then:

    Please Login or Register  to view this content.
    You could specify a specific range, but using Columns is easier since you don't have to first calculate the last row.
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  3. #3
    Registered User
    Join Date
    02-17-2017
    Location
    England
    MS-Off Ver
    2007
    Posts
    16

    Re: Sorting column alphabetically by surname

    Quote Originally Posted by Arkadi View Post
    typically Range("A1") would be changed to encompass the whole range, and the Key1 parameter of your sort is the column to sort by, so if you change the range, and change the key to B2 instead of A2, then it will sort by the surname (assuming surname is in column B). If I assume you have data in columns A through D for example, then:

    Please Login or Register  to view this content.
    You could specify a specific range, but using Columns is easier since you don't have to first calculate the last row.
    Thanks for the reply.

    Sorry I should have said that the names are all in one column to satisfy a lookup i have that matches the name with a number.

    I just wasn't sure whether it was possible, with the name in one cell, to sort by the surname instead of what it understandably already does and sorts by the first letter in the cell.

    I'm currently working around it by changing the names in the lookup to "Surname, First name", but would obviously prefer it to be "FirstName Surname"

  4. #4
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Excel 2010
    Posts
    3,274

    Re: Sorting column alphabetically by surname

    You can't sort by the second half of the value of cells, but depending on the data maybe we can work around it....
    Is it always FirstName (space) LastName? or are there sometimes middle names too? You could have the code find just the last name part, dump it in some other column, sort by that column and then delete the dummy column again.

    Seeing a sample workbook could be helpful if you want more input.

  5. #5
    Registered User
    Join Date
    02-17-2017
    Location
    England
    MS-Off Ver
    2007
    Posts
    16

    Re: Sorting column alphabetically by surname

    Quote Originally Posted by Arkadi View Post
    You can't sort by the second half of the value of cells, but depending on the data maybe we can work around it....
    Is it always FirstName (space) LastName? or are there sometimes middle names too? You could have the code find just the last name part, dump it in some other column, sort by that column and then delete the dummy column again.

    Seeing a sample workbook could be helpful if you want more input.
    There are some middle names included in the big list of employees, but these can be taken out no trouble.

    I'm not sure if i'm allowed to share the workbook on here, so I can describe it as best I can..


    -So at the moment I have cocancated (or whatever it is, sorry) the column A in the workbook with every employee's name so that it goes "Surname, FirstName" so that my sheet can be sorted alphabetically. In column B is the corresponding employee number for each employee "XXXX"

    - So in my different workbook, I have a sheet where I enter the employee's name into A (in the format "Surname, FirstName") and then in column B I have a lookup which inserts the right employee number for the name I have just typed in. This is then ordered automatically whenever the list is updated by deleting or adding names

    At the moment is is usable with having the "Surname, FirstName" format, but it would obviously be preferable to have "FirstName Surname" and sorted by surname

    edited to add:

    The workbook where all of the names are numbers are in is in the following format..

    Column A - First name
    Column B - Surname
    Column C - Employee number

    So in column D I have made it so this column shows "Surname, FirstName"
    Last edited by Nemery; 02-17-2017 at 10:52 AM.

  6. #6
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Excel 2010
    Posts
    3,274

    Re: Sorting column alphabetically by surname

    Ok so columns ABC are in one workbook, but you want to sort the OTHER workbook?

  7. #7
    Registered User
    Join Date
    02-17-2017
    Location
    England
    MS-Off Ver
    2007
    Posts
    16

    Re: Sorting column alphabetically by surname

    Quote Originally Posted by Arkadi View Post
    Ok so columns ABC are in one workbook, but you want to sort the OTHER workbook?
    Yes that's right. The workbook with all of the names and number isn't mine. But I can sort it any way that will make my own workbook how I want it

    I have my own section of workers (and so I don't need all of the names, just the ones i type into my own workbook) and so use the workbook to populate my own workbook

  8. #8
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Excel 2010
    Posts
    3,274

    Re: Sorting column alphabetically by surname

    Well I'm a bit confused, but if I understand right:
    Workbook A, which has separate first name last name and employee number, is used to genereate the initial data, which is placed in workbook B?
    Then workbook B becomes independent and you want to sort it, whenever you add new names?

    One option is to have a new column in Workbook B, with just the surname... you could hide that column and it won't show, then sort by that column...

  9. #9
    Registered User
    Join Date
    02-17-2017
    Location
    England
    MS-Off Ver
    2007
    Posts
    16

    Re: Sorting column alphabetically by surname

    Quote Originally Posted by Arkadi View Post
    Well I'm a bit confused, but if I understand right:
    Workbook A, which has separate first name last name and employee number, is used to genereate the initial data, which is placed in workbook B?
    Then workbook B becomes independent and you want to sort it, whenever you add new names?
    Yes workbook A had FirstName, Surname and employee number all in different columns. I've used "CONCATENATE" in another column in this workbook to join the names in the format "Surname, FirstName". But the end goal is to change the format to "FirstName Surname" and be able to sort alphabetically on workbook B by surname.

    On workbook B (the one I want sorting by surname) I type in the names of the employees in column A (in "Surname, FirstName" format) and the employee number in column B is filled using a lookup to workbook A. So at the moment the code I have all works fine by auto-sorting the names and employees numbers alphabetically (as I have changed the format so Surname is first). But I'm looking to have this happen, but being able to change the format I type the names in with to "FirstName Surname"

    Hope this makes sense

  10. #10
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Excel 2010
    Posts
    3,274

    Re: Sorting column alphabetically by surname

    So on workbook B, if you are using a lookup to populate B with the emp. Number, could you not do a lookup in column C as well, but return the surname instead of employee number?
    You could hide column C so it won't actually show up, and you could make your sort use C2 instead of A2 as the Key1 value in the sort. Just adjust your sort to:

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    02-17-2017
    Location
    England
    MS-Off Ver
    2007
    Posts
    16

    Re: Sorting column alphabetically by surname

    Quote Originally Posted by Arkadi View Post
    So on workbook B, if you are using a lookup to populate B with the emp. Number, could you not do a lookup in column C as well, but return the surname instead of employee number?
    You could hide column C so it won't actually show up, and you could make your sort use C2 instead of A2 as the Key1 value in the sort. Just adjust your sort to:

    Please Login or Register  to view this content.
    I think I understand. I've done the look up to get the last name only in C, but putting in..
    Please Login or Register  to view this content.
    doesn't seem to work any more to auto-sort the column

  12. #12
    Registered User
    Join Date
    02-17-2017
    Location
    England
    MS-Off Ver
    2007
    Posts
    16

    Re: Sorting column alphabetically by surname

    Sorry no, ignore me.

    It works perfectly, thanks for your help, reputation added!

  13. #13
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Excel 2010
    Posts
    3,274

    Re: Sorting column alphabetically by surname

    Happy to help, appreciate the rep, and thanks for marking the thread as solved also

+ 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