+ Reply to Thread
Results 1 to 24 of 24

Sort a column by last name

  1. #1
    Forum Contributor
    Join Date
    08-19-2012
    Location
    Tacoma, Washingtonn
    MS-Off Ver
    Excel 2019
    Posts
    250

    Sort a column by last name

    I was wondering if there were a formula I could use to sort column A (which has many names listed like "John Smith" (first and last name only) into column B in alphabetical order by last name? I have found a couple using SORT, INDEX, SPLIT...but they don't work. Thanks for any help.

  2. #2
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: Sort a column by last name

    use the menu bar. sort is at the top. if you want to code it, push the menu buttons and use the macro recorder.

  3. #3
    Forum Contributor
    Join Date
    08-19-2012
    Location
    Tacoma, Washingtonn
    MS-Off Ver
    Excel 2019
    Posts
    250

    Re: Sort a column by last name

    Sorry, I don't understand your reply. I am looking for a formula to automatically sort the column. Thank you.

  4. #4
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: Sort a column by last name


  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Sort a column by last name

    Use formula to reorder the name with Last then first name, in another column then sort

    A1 is full name
    B1:

    Please Login or Register  to view this content.
    Attached Images Attached Images
    Quang PT

  6. #6
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Sort a column by last name

    ARRAY formula in D3 then copy down.
    Please Login or Register  to view this content.
    To enter ARRAY formula
    Copy and paste the formula in cell
    Press F2
    Press Ctrl+Shift+Enter together
    Excel covers the formula with {}.
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 11-28-2020 at 09:20 AM.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Sort a column by last name

    By any chance... do you now have Excel 365?
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  8. #8
    Forum Contributor
    Join Date
    08-19-2012
    Location
    Tacoma, Washingtonn
    MS-Off Ver
    Excel 2019
    Posts
    250

    Re: Sort a column by last name

    Thank you. This formula seems to work great. I have a couple of follow-up questions. 1) It does duplicate one name. I've looked for any issue that may cause that and cannot find one. Any suggestions? 2) My list of names auto-populate from another field. I have allowed room for 150 names total in column A. When I edit your formula to go from $A$3:$A$150 it does not function. It will only function if I change $A$150 to the actual number of names. Is there a way around that? Thanks for your help.

  9. #9
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Sort a column by last name

    If you are referring to post #6 pl upload file showing the file.

  10. #10
    Forum Contributor
    Join Date
    08-19-2012
    Location
    Tacoma, Washingtonn
    MS-Off Ver
    Excel 2019
    Posts
    250

    Re: Sort a column by last name

    Hope I did this right.
    Attached Files Attached Files

  11. #11
    Valued Forum Contributor
    Join Date
    10-14-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2019
    Posts
    353

    Re: Sort a column by last name

    This is a VBA solution.
    It is possible to activate the macro automatically when something is modifiied in column A
    Attached Files Attached Files
    Kind regards,
    Piet Bom

  12. #12
    Forum Contributor
    Join Date
    08-19-2012
    Location
    Tacoma, Washingtonn
    MS-Off Ver
    Excel 2019
    Posts
    250

    Re: Sort a column by last name

    You are extremely kind to help me with this project. The VBA solutions seems to be my preferred choice. Your sample works perfectly. I am trying to edit and place it into my actual worksheet and I can't seem to get it to work. The actual name of my worksheet is "Email Invites". The names are in column L and I would like to sort into column M. I'm not sure if this makes a difference. I also tried to copy your VBA code exactly but I sometimes become confused. I am trying to learn these things on my own. Thanks again for your time and your help! Any suggestions?

  13. #13
    Valued Forum Contributor
    Join Date
    10-14-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2019
    Posts
    353

    Re: Sort a column by last name

    In red you see the adjustments for names in column L and the result in column M
    Please Login or Register  to view this content.

  14. #14
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Sort a column by last name

    There was wrong entry in the formula . I have corrected it. Corrected formula is giving correct result. Corrected portion is in red font.
    Array formula in B2 then copy down
    Please Login or Register  to view this content.
    The entry was
    =ROWS($B1:$B$2)
    Attached Files Attached Files

  15. #15
    Forum Contributor
    Join Date
    08-19-2012
    Location
    Tacoma, Washingtonn
    MS-Off Ver
    Excel 2019
    Posts
    250

    Re: Sort a column by last name

    Thank you very much. One final question...as the list in column A gets longer, column B stops calculating at $A$72 until I change the formula to the correct number of names in column A. Is there a way around that?

  16. #16
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Sort a column by last name

    You could use a Named Range:

    ='Email Invites'!$A$2:INDEX('Email Invites'!$A:$A,MATCH("zzz",'Email Invites'!$A:$A))

    CTRL-F3 to view/Edit. the formula then becomes:

    =IFERROR(INDEX(List,SMALL(IF((MMULT(--((MID(List,FIND(" ",List)+1,250)&" " &LEFT(List,FIND(" ",List)-1))>TRANSPOSE((MID(List,FIND(" ",List)+1,250)&" " &LEFT(List,FIND(" ",List)-1)))),--(ROW(List)>0))+1=ROWS($B$2:$B2)),ROW(List),""),1)-ROW($A$1)),"")

    and will work, once the occasional space, or whatever, is deleted from the cells below the last row of data.

    Incidentally, you ignored my question at Post 7, as it is so much easier with 365. So, do you have O365?
    Attached Files Attached Files

  17. #17
    Forum Contributor
    Join Date
    08-19-2012
    Location
    Tacoma, Washingtonn
    MS-Off Ver
    Excel 2019
    Posts
    250

    Re: Sort a column by last name

    Very sorry for my ignorance. I so much appreciate all the help I get in here. To answer your question, no, I have Excel 2016. I'm very inexperienced. Trying to input your formula with no luck. I may not understand all of the terminology. Thanks for the input.

  18. #18
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Sort a column by last name

    OK. Small steps.

    1. Did it work in the sheet I attached?

    2. It is still an array formula, so did you use CTRL-SHIFT-ENTER?

    2. In your real sheet, are you seeing blanks?

    3. In your REAL sheet, selece Cell B2 (or wherever the formula is first used). Formulas/Evaluate formula. You will see a box that begins =IFERROR(INDEX(List....
    If you click Evaluate ONCE, what do you see instead of List? What is the LAST row of data in your REAL sheet.

  19. #19
    Forum Contributor
    Join Date
    08-19-2012
    Location
    Tacoma, Washingtonn
    MS-Off Ver
    Excel 2019
    Posts
    250

    Re: Sort a column by last name

    1. Yes it works in your sheet perfectly
    2. Yes I entered as array formula, and yes I see blanks
    3. I see...=IFERROR(INDEX($A$2:$A$108,SMALL. Right now the last row of data is in A108 (but it will grow).

  20. #20
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Sort a column by last name

    A nice clear answer... that leaves me in the dark!! Is it possible to post your real sheet?

  21. #21
    Forum Contributor
    Join Date
    08-19-2012
    Location
    Tacoma, Washingtonn
    MS-Off Ver
    Excel 2019
    Posts
    250

    Re: Sort a column by last name

    Wow. As I was preparing to send you real sheet (which I had closed and saved) It now reflects the formula correctly and works perfectly. I tested by adding several names. Thank you for your kind help with this project. I really appreciate it! It seems to be doing exactly what I wanted it to do.

  22. #22
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Sort a column by last name

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

  23. #23
    Valued Forum Contributor
    Join Date
    10-14-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2019
    Posts
    353

    Re: Sort a column by last name

    Quote Originally Posted by thedunna View Post
    I am trying to edit and place it into my actual worksheet and I can't seem to get it to work.
    Did you copy the code into a module witin the VBA-editor ?

  24. #24
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Sort a column by last name

    Quote Originally Posted by thedunna View Post
    Thank you very much. One final question...as the list in column A gets longer, column B stops calculating at $A$72 until I change the formula to the correct number of names in column A. Is there a way around that?
    Revised formula here
    Please Login or Register  to view this content.
    Change $A72 to $A700 then it will take care up to row 700 or convenient number as you require. (even blank cells are also taken care of)
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 8
    Last Post: 05-12-2019, 07:35 AM
  2. Replies: 12
    Last Post: 09-08-2018, 10:34 PM
  3. Replies: 1
    Last Post: 03-29-2018, 07:58 AM
  4. Replies: 5
    Last Post: 05-09-2017, 08:48 AM
  5. Replies: 1
    Last Post: 03-04-2015, 02:57 PM
  6. [SOLVED] I have a sort macro. How to add script to preselect rows to sort based on column value?
    By Jasonhouse in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-09-2014, 02:25 AM
  7. Replies: 2
    Last Post: 01-15-2014, 09:31 AM

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