Please help with spreadsheets!

taraskyn

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.

coojee

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.

MarieNoelle

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

Yes, First Name and Surname are both in one column.

taraskyn

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

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

MarieNoelle

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!

taraskyn

MarieNoelle wrote: »

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

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

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

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 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

Pearce161

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.