|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
February 7th, 2005, 10:41 PM
|
|||
|
|||
Creating custom list with a comma in it
I need to sort a list of cities. I know how to creat a custom list however
there seems to be a bug in it. I am hoping somebody knows how to work around this Creating this list is simple: CHICAGO BALTIMORE PHILADELPHIA DALLAS WASHINGTON The problem comes when the same city is in multiple states so that the cell actually says: COLUMBUS, OH COLUMBUS, GA PORTLAND, OR PORTLAND, ME because internally the item separator is a comma, the custom list changes these entries to: COLUMBUS OH COLUMBUS GA PORTLAND OR PORTLAND ME Obviously when you do a data sort it doesn't find any of these values. I tried creating the list programatically using the addCustomList but it did the same thing. I've run out of things to try. Any help would be appreciated. Peter Richardson 
|
|
#2
February 8th, 2005, 12:04 AM
|
|||
|
|||
|
I tried typing this list into a column in a worksheet.
COLUMBUS, OH COLUMBUS, GA PORTLAND, OR PORTLAND, ME Then I selected my range of cells the I did tools|options|custom lists tab and imported from the worksheet. It seemed to work better than typing them in the dialog. barnabel wrote: I need to sort a list of cities. I know how to creat a custom list however there seems to be a bug in it. I am hoping somebody knows how to work around this Creating this list is simple: CHICAGO BALTIMORE PHILADELPHIA DALLAS WASHINGTON The problem comes when the same city is in multiple states so that the cell actually says: COLUMBUS, OH COLUMBUS, GA PORTLAND, OR PORTLAND, ME because internally the item separator is a comma, the custom list changes these entries to: COLUMBUS OH COLUMBUS GA PORTLAND OR PORTLAND ME Obviously when you do a data sort it doesn't find any of these values. I tried creating the list programatically using the addCustomList but it did the same thing. I've run out of things to try. Any help would be appreciated. Peter Richardson -- Dave Peterson
|
|
#3
February 8th, 2005, 03:03 AM
|
|||
|
|||
|
Did you actually try to use it in a sort? It looks ok in the Custom List
box. But the values don't match when trying to sort. "Dave Peterson" wrote: I tried typing this list into a column in a worksheet. COLUMBUS, OH COLUMBUS, GA PORTLAND, OR PORTLAND, ME Then I selected my range of cells the I did tools|options|custom lists tab and imported from the worksheet. It seemed to work better than typing them in the dialog. barnabel wrote: I need to sort a list of cities. I know how to creat a custom list however there seems to be a bug in it. I am hoping somebody knows how to work around this Creating this list is simple: CHICAGO BALTIMORE PHILADELPHIA DALLAS WASHINGTON The problem comes when the same city is in multiple states so that the cell actually says: COLUMBUS, OH COLUMBUS, GA PORTLAND, OR PORTLAND, ME because internally the item separator is a comma, the custom list changes these entries to: COLUMBUS OH COLUMBUS GA PORTLAND OR PORTLAND ME Obviously when you do a data sort it doesn't find any of these values. I tried creating the list programatically using the addCustomList but it did the same thing. I've run out of things to try. Any help would be appreciated. Peter Richardson -- Dave Peterson
|
|
#4
February 8th, 2005, 11:15 PM
|
|||
|
|||
|
Yes, I did try a sort.
I think you missed a step in your sort. Select your range Data|Sort|Options button specify your custom sort order. But I did have to add that custom list via a range. If I typed them into the dialog, then I had trouble. barnabel wrote: Did you actually try to use it in a sort? It looks ok in the Custom List box. But the values don't match when trying to sort. "Dave Peterson" wrote: I tried typing this list into a column in a worksheet. COLUMBUS, OH COLUMBUS, GA PORTLAND, OR PORTLAND, ME Then I selected my range of cells the I did tools|options|custom lists tab and imported from the worksheet. It seemed to work better than typing them in the dialog. barnabel wrote: I need to sort a list of cities. I know how to creat a custom list however there seems to be a bug in it. I am hoping somebody knows how to work around this Creating this list is simple: CHICAGO BALTIMORE PHILADELPHIA DALLAS WASHINGTON The problem comes when the same city is in multiple states so that the cell actually says: COLUMBUS, OH COLUMBUS, GA PORTLAND, OR PORTLAND, ME because internally the item separator is a comma, the custom list changes these entries to: COLUMBUS OH COLUMBUS GA PORTLAND OR PORTLAND ME Obviously when you do a data sort it doesn't find any of these values. I tried creating the list programatically using the addCustomList but it did the same thing. I've run out of things to try. Any help would be appreciated. Peter Richardson -- Dave Peterson -- Dave Peterson
|
|
#5
February 9th, 2005, 12:31 AM
|
|||
|
|||
|
Totally awesome!
The trick is to reorder the city names in the list before importing them into the custom list. I had imported the list in alphabetic order and then tried to move them to the order I wanted them. That killed it. However if the city list is in the right order when importing then the sort works perfectly. "Dave Peterson" wrote: Yes, I did try a sort. I think you missed a step in your sort. Select your range Data|Sort|Options button specify your custom sort order. But I did have to add that custom list via a range. If I typed them into the dialog, then I had trouble. barnabel wrote: Did you actually try to use it in a sort? It looks ok in the Custom List box. But the values don't match when trying to sort. "Dave Peterson" wrote: I tried typing this list into a column in a worksheet. COLUMBUS, OH COLUMBUS, GA PORTLAND, OR PORTLAND, ME Then I selected my range of cells the I did tools|options|custom lists tab and imported from the worksheet. It seemed to work better than typing them in the dialog. barnabel wrote: I need to sort a list of cities. I know how to creat a custom list however there seems to be a bug in it. I am hoping somebody knows how to work around this Creating this list is simple: CHICAGO BALTIMORE PHILADELPHIA DALLAS WASHINGTON The problem comes when the same city is in multiple states so that the cell actually says: COLUMBUS, OH COLUMBUS, GA PORTLAND, OR PORTLAND, ME because internally the item separator is a comma, the custom list changes these entries to: COLUMBUS OH COLUMBUS GA PORTLAND OR PORTLAND ME Obviously when you do a data sort it doesn't find any of these values. I tried creating the list programatically using the addCustomList but it did the same thing. I've run out of things to try. Any help would be appreciated. Peter Richardson -- Dave Peterson -- Dave Peterson
|
|
#6
February 9th, 2005, 12:41 AM
|
|||
|
|||
|
Glad you got it working!
barnabel wrote: Totally awesome! The trick is to reorder the city names in the list before importing them into the custom list. I had imported the list in alphabetic order and then tried to move them to the order I wanted them. That killed it. However if the city list is in the right order when importing then the sort works perfectly. "Dave Peterson" wrote: Yes, I did try a sort. I think you missed a step in your sort. Select your range Data|Sort|Options button specify your custom sort order. But I did have to add that custom list via a range. If I typed them into the dialog, then I had trouble. barnabel wrote: Did you actually try to use it in a sort? It looks ok in the Custom List box. But the values don't match when trying to sort. "Dave Peterson" wrote: I tried typing this list into a column in a worksheet. COLUMBUS, OH COLUMBUS, GA PORTLAND, OR PORTLAND, ME Then I selected my range of cells the I did tools|options|custom lists tab and imported from the worksheet. It seemed to work better than typing them in the dialog. barnabel wrote: I need to sort a list of cities. I know how to creat a custom list however there seems to be a bug in it. I am hoping somebody knows how to work around this Creating this list is simple: CHICAGO BALTIMORE PHILADELPHIA DALLAS WASHINGTON The problem comes when the same city is in multiple states so that the cell actually says: COLUMBUS, OH COLUMBUS, GA PORTLAND, OR PORTLAND, ME because internally the item separator is a comma, the custom list changes these entries to: COLUMBUS OH COLUMBUS GA PORTLAND OR PORTLAND ME Obviously when you do a data sort it doesn't find any of these values. I tried creating the list programatically using the addCustomList but it did the same thing. I've run out of things to try. Any help would be appreciated. Peter Richardson -- Dave Peterson -- Dave Peterson -- Dave Peterson
|
|
#7
January 10th, 2006, 06:14 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Creating custom list with a comma in it
Hi,
I tried importing but it does not work, there seems to be a bug. I need to sort stuff in the below order: 1) COL 2) LTC 3) LTA 4) 1WO 5) 2WO 6) CPL But is always gives me: 1) COL 2) LTA 3) 1WO 4) 2WO 5) CPL 6) LTC Somehow LTC is always last. Pls need help sorting this out. "Dave Peterson" wrote: Glad you got it working! barnabel wrote: Totally awesome! The trick is to reorder the city names in the list before importing them into the custom list. I had imported the list in alphabetic order and then tried to move them to the order I wanted them. That killed it. However if the city list is in the right order when importing then the sort works perfectly. "Dave Peterson" wrote: Yes, I did try a sort. I think you missed a step in your sort. Select your range Data|Sort|Options button specify your custom sort order. But I did have to add that custom list via a range. If I typed them into the dialog, then I had trouble. barnabel wrote: Did you actually try to use it in a sort? It looks ok in the Custom List box. But the values don't match when trying to sort. "Dave Peterson" wrote: I tried typing this list into a column in a worksheet. COLUMBUS, OH COLUMBUS, GA PORTLAND, OR PORTLAND, ME Then I selected my range of cells the I did tools|options|custom lists tab and imported from the worksheet. It seemed to work better than typing them in the dialog. barnabel wrote: I need to sort a list of cities. I know how to creat a custom list however there seems to be a bug in it. I am hoping somebody knows how to work around this Creating this list is simple: CHICAGO BALTIMORE PHILADELPHIA DALLAS WASHINGTON The problem comes when the same city is in multiple states so that the cell actually says: COLUMBUS, OH COLUMBUS, GA PORTLAND, OR PORTLAND, ME because internally the item separator is a comma, the custom list changes these entries to: COLUMBUS OH COLUMBUS GA PORTLAND OR PORTLAND ME Obviously when you do a data sort it doesn't find any of these values. I tried creating the list programatically using the addCustomList but it did the same thing. I've run out of things to try. Any help would be appreciated. Peter Richardson -- Dave Peterson -- Dave Peterson -- Dave Peterson
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| E-mail Display name is wrong | Jane Nangle | Contacts | 9 | February 4th, 2005 10:05 AM |
| Eliminate creating list that returns blank cells | Marc Todd | Worksheet Functions | 1 | January 26th, 2005 09:58 PM |
| Problems creating a numbered list | Kasper Hviid | Page Layout | 4 | June 1st, 2004 02:03 AM |
| Creating drop-down list with VBA | LindsayR | General Discussion | 1 | May 21st, 2004 11:45 PM |
| Creating Custom Toolbars | Vasant Nanavati | Worksheet Functions | 0 | April 21st, 2004 03:26 PM |
Linear Mode
