| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sort help
Hi
I have a spreadsheet with a list of names Mr & Mrs E. McAllister Mrs E. Hay Rev. G. & Mr M. Farquhar Mrs M. Russell Mr & Mrs J. Cuthbert Mr R. Cuthbert Miss Judith Cuthbert Mr & Mrs M. Weller Mrs C. Doherty Mr & Mrs J. Stewart Mr W. Ridges Mrs M. Bell Mr & Mrs D. Bell Mr & Mrs J. McMurran Mrs A. Erskine etc etc How do I sort by surname please? Using 2007 -- Martin |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sort help
Make sure that the surname is in a separate column. Select all the relevant
columns before you sort. -- David Biddulph Martin @nohere.net wrote: Hi I have a spreadsheet with a list of names Mr & Mrs E. McAllister Mrs E. Hay Rev. G. & Mr M. Farquhar Mrs M. Russell Mr & Mrs J. Cuthbert Mr R. Cuthbert Miss Judith Cuthbert Mr & Mrs M. Weller Mrs C. Doherty Mr & Mrs J. Stewart Mr W. Ridges Mrs M. Bell Mr & Mrs D. Bell Mr & Mrs J. McMurran Mrs A. Erskine etc etc How do I sort by surname please? Using 2007 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sort help
|
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sort help
Hi,
You need a helper column. Use this formula to extract the last name in to another column =RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) Select both columns and sort on the helper column which you can hide if you want Mike "Martin ©¿©¬ @nohere.net" wrote: Hi I have a spreadsheet with a list of names Mr & Mrs E. McAllister Mrs E. Hay Rev. G. & Mr M. Farquhar Mrs M. Russell Mr & Mrs J. Cuthbert Mr R. Cuthbert Miss Judith Cuthbert Mr & Mrs M. Weller Mrs C. Doherty Mr & Mrs J. Stewart Mr W. Ridges Mrs M. Bell Mr & Mrs D. Bell Mr & Mrs J. McMurran Mrs A. Erskine etc etc How do I sort by surname please? Using 2007 -- Martin ©¿©¬ . |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sort help
Martin:-
I have just put up a file for you at:- http://www.pierrefondes.com/ It is item number 35 towards the top of my home page. Your data really needs to be reorganized as it is not really in a manageable form. However, for the Workbook I have uploaded, if you click in the drop down in cell H 1 and select Sort A to Z you will get the people in the alpha order of surname. There are many ways this could be done. If my comments have helped please hit Yes. Thanks. "Martin ©¿©¬ @nohere.net" wrote: Hi I have a spreadsheet with a list of names Mr & Mrs E. McAllister Mrs E. Hay Rev. G. & Mr M. Farquhar Mrs M. Russell Mr & Mrs J. Cuthbert Mr R. Cuthbert Miss Judith Cuthbert Mr & Mrs M. Weller Mrs C. Doherty Mr & Mrs J. Stewart Mr W. Ridges Mrs M. Bell Mr & Mrs D. Bell Mr & Mrs J. McMurran Mrs A. Erskine etc etc How do I sort by surname please? Using 2007 -- Martin ©¿©¬ . |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sort help
On Wed, 13 Jan 2010 10:45:58 -0000, "David Biddulph" <groups [at]
biddulph.org.uk wrote: Make sure that the surname is in a separate column. Select all the relevant columns before you sort. Hi David I was hopeing to be able to sort without having to have/keep the surname in a seperate column -- Martin |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sort help
Hi,
The first thing you will need to do is create a "helper" column next to your data. In this column you will need to extract the surname from the full name. You can then sort by the surname in the "helper" column. There are a couple ways to extract the surname. Firstly you could use "text to columns". Given the text below you could possibly use "." as the seperator. Alternatively you could use formula to pull out the surname. Full name in A1. =RIGHT(A1,(LEN(A1)-FIND(".",A1,4)-1)) One note of caution :- you will need to be careful of your full name format as neither of the above will work with "Rev. G. & Mr M. Farquhar" as there are several "."s. Myles "Martin ©¿©¬ @nohere.net" wrote: Hi I have a spreadsheet with a list of names Mr & Mrs E. McAllister Mrs E. Hay Rev. G. & Mr M. Farquhar Mrs M. Russell Mr & Mrs J. Cuthbert Mr R. Cuthbert Miss Judith Cuthbert Mr & Mrs M. Weller Mrs C. Doherty Mr & Mrs J. Stewart Mr W. Ridges Mrs M. Bell Mr & Mrs D. Bell Mr & Mrs J. McMurran Mrs A. Erskine etc etc How do I sort by surname please? Using 2007 -- Martin ©¿©¬ . |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sort help
You're out of luck.
-- David Biddulph Martin @nohere.net wrote: On Wed, 13 Jan 2010 10:45:58 -0000, "David Biddulph" <groups [at] biddulph.org.uk wrote: Make sure that the surname is in a separate column. Select all the relevant columns before you sort. Hi David I was hopeing to be able to sort without having to have/keep the surname in a seperate column |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sort help
Separate surname into an empty helper column with this formula:
=MID(A1,FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,256) and sort by the helper column! -- Regards! Stefi Martin ©¿©¬ @nohere.net ezt *rta: Hi I have a spreadsheet with a list of names Mr & Mrs E. McAllister Mrs E. Hay Rev. G. & Mr M. Farquhar Mrs M. Russell Mr & Mrs J. Cuthbert Mr R. Cuthbert Miss Judith Cuthbert Mr & Mrs M. Weller Mrs C. Doherty Mr & Mrs J. Stewart Mr W. Ridges Mrs M. Bell Mr & Mrs D. Bell Mr & Mrs J. McMurran Mrs A. Erskine etc etc How do I sort by surname please? Using 2007 -- Martin ©¿©¬ . |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sort help
Consider retrieving the Surname into col. B with the proposed formula and
then sort, col. A+B, as per col. B You can hide col. B whenever you feel like it. http://img251.imageshack.us/img251/4248/nonamef.png Micky "Martin ©¿©¬ @nohere.net" wrote: Hi I have a spreadsheet with a list of names Mr & Mrs E. McAllister Mrs E. Hay Rev. G. & Mr M. Farquhar Mrs M. Russell Mr & Mrs J. Cuthbert Mr R. Cuthbert Miss Judith Cuthbert Mr & Mrs M. Weller Mrs C. Doherty Mr & Mrs J. Stewart Mr W. Ridges Mrs M. Bell Mr & Mrs D. Bell Mr & Mrs J. McMurran Mrs A. Erskine etc etc How do I sort by surname please? Using 2007 -- Martin ©¿©¬ . |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sort help
Insert the column, then the formula
=MID(A1,SEARCH(".",A1,1)+2,LEN(A1)) then sort it out click yes if helped -- Greatly appreciated Eva "Martin ©¿©¬ @nohere.net" wrote: Hi I have a spreadsheet with a list of names Mr & Mrs E. McAllister Mrs E. Hay Rev. G. & Mr M. Farquhar Mrs M. Russell Mr & Mrs J. Cuthbert Mr R. Cuthbert Miss Judith Cuthbert Mr & Mrs M. Weller Mrs C. Doherty Mr & Mrs J. Stewart Mr W. Ridges Mrs M. Bell Mr & Mrs D. Bell Mr & Mrs J. McMurran Mrs A. Erskine etc etc How do I sort by surname please? Using 2007 -- Martin ©¿©¬ . |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sort help
So for "Rev. G. & Mr M. Farquhar" you are treating the surname as "G. & Mr
M. Farquhar" and for "Miss Judith Cuthbert" you get #VALUE! But if there aren't too many of that type of exception it might make some sense to process the entries your way and deal with the exceptions manually. -- David Biddulph Eva wrote: Insert the column, then the formula =MID(A1,SEARCH(".",A1,1)+2,LEN(A1)) then sort it out click yes if helped Hi I have a spreadsheet with a list of names Mr & Mrs E. McAllister Mrs E. Hay Rev. G. & Mr M. Farquhar Mrs M. Russell Mr & Mrs J. Cuthbert Mr R. Cuthbert Miss Judith Cuthbert Mr & Mrs M. Weller Mrs C. Doherty Mr & Mrs J. Stewart Mr W. Ridges Mrs M. Bell Mr & Mrs D. Bell Mr & Mrs J. McMurran Mrs A. Erskine etc etc How do I sort by surname please? Using 2007 -- Martin . |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sort help
Thanks for all your help
I have been off line for a few days I will try your suggestions -- Martin On Wed, 13 Jan 2010 10:07:44 +0000, Martin @nohere.net wrote: Hi I have a spreadsheet with a list of names Mr & Mrs E. McAllister Mrs E. Hay Rev. G. & Mr M. Farquhar Mrs M. Russell Mr & Mrs J. Cuthbert Mr R. Cuthbert Miss Judith Cuthbert Mr & Mrs M. Weller Mrs C. Doherty Mr & Mrs J. Stewart Mr W. Ridges Mrs M. Bell Mr & Mrs D. Bell Mr & Mrs J. McMurran Mrs A. Erskine etc etc How do I sort by surname please? Using 2007 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Updating workbook with an alpha sort sheet and a numeric sort shee | Excel Discussion (Misc queries) | |||
| Sort by color: Is there an easy way to sort columns or rows in EX | Excel Worksheet Functions | |||
| sort function for dates does not sort properly in Office 2007 Exc. | Excel Worksheet Functions | |||
| Excel sort by Fill Color by custom list sort | Excel Discussion (Misc queries) | |||
| how can I hide sort ascending and sort descending options in the . | Excel Discussion (Misc queries) | |||
Linear Mode
