Please help with spreadsheets!

taraskyn
taraskyn Registered Posts: 41 Epic contributor ๐Ÿ˜
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

  • coojee
    coojee Registered Posts: 794 Epic contributor ๐Ÿ˜
    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.
  • MarieNoelle
    MarieNoelle Registered, Moderator Posts: 1,368
    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.
  • taraskyn
    taraskyn Registered Posts: 41 Epic contributor ๐Ÿ˜
    Yes, First Name and Surname are both in one column.
  • taraskyn
    taraskyn Registered Posts: 41 Epic contributor ๐Ÿ˜
    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 Clark
    Jo Clark Registered Posts: 2,525 Beyond epic contributor ๐Ÿง™โ€โ™‚๏ธ
    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
  • MarieNoelle
    MarieNoelle Registered, Moderator Posts: 1,368
    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!
  • taraskyn
    taraskyn Registered Posts: 41 Epic contributor ๐Ÿ˜
    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.
  • steve2008
    steve2008 Registered Posts: 89 Epic contributor ๐Ÿ˜
    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.
  • Pearce161
    Pearce161 Registered Posts: 57 Epic contributor ๐Ÿ˜
    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 Clark
    Jo Clark Registered Posts: 2,525 Beyond epic contributor ๐Ÿง™โ€โ™‚๏ธ
    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
  • Pearce161
    Pearce161 Registered Posts: 57 Epic contributor ๐Ÿ˜
    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.
Privacy Policy