software

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?

Hi,
Would this help ... enter name as lt;John Brian Smithgt; and create
quot;SortNamequot; 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(quot;Enter Artists namequot;, quot;Name of Artistquot;,
Type:=2)
If ArtistName = False Then Exit Sub ' Cancel
v = Split(ArtistName)
SortName = v(UBound(v)) amp; quot;,quot; ' Set as Surname
For i = LBound(v) To UBound(v) - 1
SortName = SortName amp; quot; quot; amp; v(i) ' add forenames ....
Next i

MsgBox SortName

quot;RKquot; wrote:

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


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.
=A2amp;quot; quot;amp;B2

or if say columns A and C are always used but B and D are sometimes
used:
=A2amp;if(B2=quot;quot;,quot; quot;,quot; quot;amp;B2amp;quot; quot;)amp;quot;C2quot;amp;if(D2=quot;quot;,quot;quot;,quot; quot;amp;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
------------------------------------------------------------------------
John James's Profile: www.excelforum.com/member.php...oamp;userid=32690
View this thread: www.excelforum.com/showthread...hreadid=531829I 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
gt; wrote:

gt;
gt;Keep whatever keys you might wish to sort on in separate columns.
gt;
gt;You can always calculate most full names in another column, rather than
gt;having to re-enter e.g.
gt;=A2amp;quot; quot;amp;B2
gt;
gt;or if say columns A and C are always used but B and D are sometimes
gt;used:
gt;=A2amp;if(B2=quot;quot;,quot; quot;,quot; quot;amp;B2amp;quot; quot;)amp;quot;C2quot;amp;if(D2=quot;quot;,quot;quot;,quot; quot;amp;D2)
gt;
gt;
gt;You will then be able to sort however you want - e.g. by christian name
gt;within surname, by full bandname, etc. It's likely you'll eventually
gt;regret it if you don't store all usable fields separately.
I overcomplicated the problem of inserting spaces between the words held
in different fields. This works and is simpler:

=trim(A2amp;quot; quot;amp;B2amp;quot; quot;amp;C2amp;quot; quot;amp;D2)

John James Wrote:
gt;
gt; or if say columns A and C are always used but B and D are sometimes
gt; used:
gt; =A2amp;if(B2=quot;quot;,quot; quot;,quot; quot;amp;B2amp;quot; quot;)amp;quot;C2quot;amp;if(D2=quot;quot;,quot;quot;,quot; quot;amp;D2)
gt; Errata: The quot;C2quot; above shouldn't be in quotes
gt;--
John James
------------------------------------------------------------------------
John James's Profile: www.excelforum.com/member.php...oamp;userid=32690
View this thread: www.excelforum.com/showthread...hreadid=531829