Please help with spreadsheets!

taraskyntaraskyn Feels At HomeLondonPosts: 41Registered
Hi All!
I have searched the internet, all similar issues and answers but still cannot find a simple straightforward solution.
How to sort name by alphabetical order by surname????
Sound very simple, yes?
I am sure they did a mistake in the book considering it is in chapter 3 - basics.
You can change it and sort it by manually re-enter all the names, or separating the surname into the different row.
But how to do it automatically???
Anyone?
Thanks.

Comments

  • coojeecoojee Experienced Mentor Posts: 794Registered
    taraskyn wrote: »
    Hi All!
    I have searched the internet, all similar issues and answers but still cannot find a simple straightforward solution.
    How to sort name by alphabetical order by surname????
    Sound very simple, yes?
    I am sure they did a mistake in the book considering it is in chapter 3 - basics.
    You can change it and sort it by manually re-enter all the names, or separating the surname into the different row.
    But how to do it automatically???
    Anyone?
    Thanks.
    Highlight all the data that you want to sort then go to Data and Sort on the toolbar, it then asks you which column you want to sort by, select the column that has the surname in and then whether you want it to be A-Z or Z-A (A-Z is the default so you shouldn't need to change this). Note that it's the same process if you want to sort a numerical column but here you would select low to high for values.
  • MarieNoelleMarieNoelle Trusted Regular Hampshire/Surrey borderPosts: 1,461Moderator, MAAT, AAT Licensed Accountant
    Hi taraskyn,
    Which book are you using and which page/exercise are you on?
    Here is what I found on the internet but this is quite advanced and I don't think we study this with AAT!

    http://www.wikihow.com/Separate-First-Names-and-Last-Names-Into-Separate-Fields-in-a-Microsoft-Excel-List

    Just saw Coojee's reply. I have assumed we have one column with first names and surnames together.
  • taraskyntaraskyn Feels At Home LondonPosts: 41Registered
    Yes, First Name and Surname are both in one column.
  • taraskyntaraskyn Feels At Home LondonPosts: 41Registered
    I am on Osborne Book tutorial, chapter 3, exercise2 stage2 task2 - page69.
    In the picture which comes after instructions it is indeed sorted by surname.
    As you all know first three chapters telling you about ridiculous basics like - how to make an entry in the cell and how copy and paste all that crap. But here suddenly - sort by fricking surnames. So pissed!!!
  • Jo ClarkJo Clark Font Of All Knowledge Posts: 2,526Registered
    Hello

    The only way I can think is to split the first/last name into different columns and then sort. Would be interested if it is possible to do it another way.


    JC
    ~ An investment in knowledge always pays the best interest ~
    Benjamin Franklin
  • MarieNoelleMarieNoelle Trusted Regular Hampshire/Surrey borderPosts: 1,461Moderator, MAAT, AAT Licensed Accountant
    taraskyn wrote: »
    I am on Osborne Book tutorial, chapter 3, exercise2 stage2 task2 - page69.
    In the picture which comes after instructions it is indeed sorted by surname.
    As you all know first three chapters telling you about ridiculous basics like - how to make an entry in the cell and how copy and paste all that crap. But here suddenly - sort by fricking surnames. So pissed!!!

    taraskyn, it is asking to "move (re-arrange) the rows so they are in alphabetical order by surname". I think it means you do it manually!
  • taraskyntaraskyn Feels At Home LondonPosts: 41Registered
    taraskyn, it is asking to "move (re-arrange) the rows so they are in alphabetical order by surname". I think it means you do it manually!

    I think you're right. I will do that.
  • steve2008steve2008 Feels At Home Posts: 89Registered
    Jo, it's almost certainly beyond the scope of this course, but It would be possible to do it by writing a bit of code in VBA. If there was a specific reason why the user wouldn't want the names split up then this would work, but it would take longer to program than just splitting up the name into two columns especially if it was a one off.

    If you really wanted it to look the same without using VBA, you could use "text to columns" (in the data menu) to split it up, then concatenate to put the name back together in a new column, then sort by the surname column, then hide / delete the surname and first name columns.

    e.g.
    Cell A2 = Joe Bloggs
    ---
    text to columns, using space as a seperator to make
    Cell A2 = Joe
    Cell B2 = Bloggs
    ---
    Make a new column C, and in C2 type =CONCATENATE(A2," ", B2) then
    Cell C2 = Joe Bloggs
    ---
    Sort by column B
    ---
    Hide columns A and B

    btw, I haven't started studying the Excel module yet, so have no idea whether any of these activities are covered.
  • Pearce161Pearce161 Feels At Home Posts: 57Registered
    I haven't looked at it in the book yet, but to me that sounds like you have to re-write the names surname first ie Smith, Dennis then you can just do a normal sort a-z and they will be ordered alphabetically by surname and all in one column.
  • Jo ClarkJo Clark Font Of All Knowledge Posts: 2,526Registered
    steve2008 wrote: »
    Jo, it's almost certainly beyond the scope of this course, but It would be possible to do it by writing a bit of code in VBA. If there was a specific reason why the user wouldn't want the names split up then this would work, but it would take longer to program than just splitting up the name into two columns especially if it was a one off.

    If you really wanted it to look the same without using VBA, you could use "text to columns" (in the data menu) to split it up, then concatenate to put the name back together in a new column, then sort by the surname column, then hide / delete the surname and first name columns.

    e.g.
    Cell A2 = Joe Bloggs
    ---
    text to columns, using space as a seperator to make
    Cell A2 = Joe
    Cell B2 = Bloggs
    ---
    Make a new column C, and in C2 type =CONCATENATE(A2," ", B2) then
    Cell C2 = Joe Bloggs
    ---
    Sort by column B
    ---
    Hide columns A and B

    btw, I haven't started studying the Excel module yet, so have no idea whether any of these activities are covered.

    Hello Steve

    Thanks for this, I thought there may be a way :o I'm sure you will have no problems when you complete the Excel module! Just had a go with text to columns... easy when you know how... and easy to forget what you know when you don't use that skill for a period of time :)

    VBA is not something I have really looked at in much depth apart from when editing macros.


    JC
    ~ An investment in knowledge always pays the best interest ~
    Benjamin Franklin
  • Pearce161Pearce161 Feels At Home Posts: 57Registered
    That reply by Steve is fantastic but now i've actually got to this exercise I think you have over complicated it. It asks you to move (re-arrange) the cells into alphabetical order by surname, but there are only 5 entries and in the chapter preceding it, it explains how to cut and paste using insert cut cells. It is just asking you to cut and paste them around into order, manually rather than by an automatic function or formula.
Sign In or Register to comment.