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?

 

[Guest]

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

 

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

 

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