Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Name alphabetising
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Name alphabetising
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Name alphabetising
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 ------------------------------------------------------------------------ John James's Profile: http://www.excelforum.com/member.php...o&userid=32690 View this thread: http://www.excelforum.com/showthread...hreadid=531829 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Name alphabetising
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 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Name alphabetising
I overcomplicated the problem of inserting spaces between the words held in different fields. This works and is simpler: =trim(A2&" "&B2&" "&C2&" "&D2) John James Wrote: 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 -- John James ------------------------------------------------------------------------ John James's Profile: http://www.excelforum.com/member.php...o&userid=32690 View this thread: http://www.excelforum.com/showthread...hreadid=531829 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|