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

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

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

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

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?

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?

Sorting an Address list - 1 attachment
 

Thank you for your help Alok. I really appreciate it.

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

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