Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting an Address list - 1 attachment
Hi Scott,
It is the convention in NGs not to attach Excel workbook because of the damage they can cause. Please try and display the layout in your message Thanks. "Scott G" wrote: I have an address list that I need to sort by the addressee's last name. My problem is that some of the adressees are businesses and some have titles. Each address is in a separate row. Should I use a formula to replace the titles (Mr. Mrs. Dr.) with nothing? How do I handle sorting the businesses? I would want to sort these by the first word in the name. The people I would want to sort by their last name, ignoring Jr. Sr. II, III. Can this be done? I am attaching an Excel list for your consideration. Thank you!! Scott |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting an Address list - 1 attachment
?B?QWxvaw==?= wrote in
: I have an address list that I need to sort by the addressee's last name. My problem is that some of the adressees are businesses and some have titles. Each address is in a separate row. Should I use a formula to replace the titles (Mr. Mrs. Dr.) with nothing? How do I handle sorting the businesses? I would want to sort these by the first word in the name. The people I would want to sort by their last name, ignoring Jr. Sr. II, III. Can this be done? I am attaching an Excel list for your consideration. The list is contained in columns A through F, like this: 1410] Mr & Mrs Dennis D Kaney] 26 Triton Rd]Ormond Beach] FL] 32724] 1418] Mrs Brendan Sayers Jr]500 N Oleander Avenue]DeLand] FL] 32724] 1419] Microsoft Corporation] 1 Gates Way]Redmond] WA] 11545 The first column is the account no. Name, street address, city, state, and zip are in B through F. Thanks!!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting an Address list - 1 attachment
Hi Scott,
Here is a function I wrote which should be able to figure out the difference between a Personal name and a company name etc and return the right name to sort on. Use this as a formula in your worksheet and see if this works for you. You could modify the function if it is not doing what you want. Public Function NameToSortOn(ByVal sName$) As String Dim i%, iPos% Dim asTitles(1 To 10) As String asTitles(1) = " Jr" asTitles(2) = " Sr" asTitles(3) = " I" asTitles(4) = " II" asTitles(5) = " III" 'etc etc sName = Trim(sName) If InStr(UCase(sName), "MR.") = 1 Or InStr(UCase(sName), "MRS.") = 1 Or InStr(UCase(sName), "DR.") = 1 Then 'Do individual stuff For i = 1 To UBound(asTitles) If asTitles(i) < "" Then iPos = InStr(UCase(sName), UCase(asTitles(i))) If iPos 0 Then sName = Mid$(sName, 1, iPos - 1) Exit For End If End If Next i If Right$(sName, 1) = "." Then sName = Mid(sName, Len(sName) - 1) iPos = InStrRev(sName, " ") If iPos 0 Then sName = Mid$(sName, iPos + 1) End If End If NameToSortOn = sName End Function "Scott G" wrote: ?B?QWxvaw==?= wrote in : I have an address list that I need to sort by the addressee's last name. My problem is that some of the adressees are businesses and some have titles. Each address is in a separate row. Should I use a formula to replace the titles (Mr. Mrs. Dr.) with nothing? How do I handle sorting the businesses? I would want to sort these by the first word in the name. The people I would want to sort by their last name, ignoring Jr. Sr. II, III. Can this be done? I am attaching an Excel list for your consideration. The list is contained in columns A through F, like this: 1410] Mr & Mrs Dennis D Kaney] 26 Triton Rd]Ormond Beach] FL] 32724] 1418] Mrs Brendan Sayers Jr]500 N Oleander Avenue]DeLand] FL] 32724] 1419] Microsoft Corporation] 1 Gates Way]Redmond] WA] 11545 The first column is the account no. Name, street address, city, state, and zip are in B through F. Thanks!!! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting an Address list - 1 attachment
?B?QWxvaw==?= wrote in
: I created the custom function you suggested. When I tried to use it I got a "Compile Error: Syntax error message." Is this because I need to edit the function further to get it to run properly? I appreciate your assistance. I am getting an education!! If InStr(UCase(sName), "MR.") = 1 Or InStr(UCase(sName), "MRS.") = 1 Or |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting an Address list - 1 attachment
My formula is =NameToSortOn(B1) Column B contains the name of the addressee. Do I need to add to the formula? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting an Address list - 1 attachment
Hi Scott,
The formula should work. However, make sure that the long lines are not broken because of word wrap. For instance the line starting with If ... has to continue till the then You will know this kind or error (syntax error) if you see a line in red. There may be some logical error but that will give you a wrong result though the code will run. In your case the code is not running. Alok "Scott G" wrote: My formula is =NameToSortOn(B1) Column B contains the name of the addressee. Do I need to add to the formula? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting an Address list - 1 attachment
Thank you for your help Alok. I really appreciate it. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting an Address list - 1 attachment
Alok,
The line was broken, and was an easy fix. At first the code seemed not to work. Then I realized that the case of the text is important. I did a search & replace on the lower case titles and used upper case and periods , ie "MR." Everything works well now. Thanks again for the help!! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting an Address list - 1 attachment
You are welcome.
"Scott G" wrote: Alok, The line was broken, and was an easy fix. At first the code seemed not to work. Then I realized that the case of the text is important. I did a search & replace on the lower case titles and used upper case and periods , ie "MR." Everything works well now. Thanks again for the help!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I email an excel file as attachment to a specific address | Excel Discussion (Misc queries) | |||
Address List Sort Help Needed - 1 attachment | Excel Discussion (Misc queries) | |||
Sorting an Address List | Excel Worksheet Functions | |||
Hyperlink to email address with attachment | Excel Discussion (Misc queries) | |||
macro that sends email with attachment and address | Excel Programming |