Name alphabetising

[RK]

Hope I can explain this right, I have a record catalogue on line. I
use Excel obviously.

The Artiste name to assist the customer is entered e.g. Smith, John
and his big band. Entered in one column, so that customers can easily
scan down the list for stuff they want.

Is it possible to somehow enter the detail as John Smith and and the
list is ranked as per the Smith not the John i.e ranking on the second
word, or even the thrid word in some cases?. I notice that others
on-line seem to do it. Is it an Excel possibility?

[Toppers]

Hi,
Would this help ... enter name as <John Brian Smith> and create
"SortName" as Smith, John Brian. This is the name to put in your spreadsheet.
New additions would require re-sorting the spreadsheet or writing logic to
insert at correct position.

HTH

Dim v As Variant

ArtistName = Application.InputBox("Enter Artists name", "Name of Artist",
Type:=2)
If ArtistName = False Then Exit Sub ' Cancel
v = Split(ArtistName)
SortName = v(UBound(v)) & "," ' Set as Surname
For i = LBound(v) To UBound(v) - 1
SortName = SortName & " " & v(i) ' add forenames ....
Next i

MsgBox SortName

"RK" wrote:

> Hope I can explain this right, I have a record catalogue on line. I
> use Excel obviously.
>
> The Artiste name to assist the customer is entered e.g. Smith, John
> and his big band. Entered in one column, so that customers can easily
> scan down the list for stuff they want.
>
> Is it possible to somehow enter the detail as John Smith and and the
> list is ranked as per the Smith not the John i.e ranking on the second
> word, or even the thrid word in some cases?. I notice that others
> on-line seem to do it. Is it an Excel possibility?
>

[John James]

Keep whatever keys you might wish to sort on in separate columns.

You can always calculate most full names in another column, rather than having to re-enter e.g.
=A2&" "&B2

or if say columns A and C are always used but B and D are sometimes used:
=A2&if(B2=""," "," "&B2&" ")&"C2"&if(D2="",""," "&D2)


You will then be able to sort however you want - e.g. by christian name within surname, by full bandname, etc. It's likely you'll eventually regret it if you don't store all usable fields separately.

[RK]

I knew Excell would have that sussed, I also suspected that my
successful lobotomy would hamper my understanding. Still you never
know I may be able to work it out.

A Genuine Thanks (I think)


On Tue, 11 Apr 2006 07:29:16 -0500, John James
<[email protected]> wrote:

>
>Keep whatever keys you might wish to sort on in separate columns.
>
>You can always calculate most full names in another column, rather than
>having to re-enter e.g.
>=A2&" "&B2
>
>or if say columns A and C are always used but B and D are sometimes
>used:
>=A2&if(B2=""," "," "&B2&" ")&"C2"&if(D2="",""," "&D2)
>
>
>You will then be able to sort however you want - e.g. by christian name
>within surname, by full bandname, etc. It's likely you'll eventually
>regret it if you don't store all usable fields separately.

[John James]

I overcomplicated the problem of inserting spaces between the words held in different fields. This works and is simpler:

=trim(A2&" "&B2&" "&C2&" "&D2)

or if say columns A and C are always used but B and D are sometimes used:
=A2&if(B2=""," "," "&B2&" ")&"C2"&if(D2="",""," "&D2)
Errata: The "C2" above shouldn't be in quotes