| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
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
|
|||
|
|||
|
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
|
|||
|
|||
|
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
|
|||
|
|||
|
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
|
|||
|
|||
|
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
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Creating a dynamic list | Excel Worksheet Functions | |||
| Eliminate creating list that returns blank cells | Excel Worksheet Functions | |||
| custom list | Excel Discussion (Misc queries) | |||
| Creating a dynamic list | Excel Worksheet Functions | |||
| Creating a list from an existing list. | Excel Worksheet Functions | |||
Hybrid Mode
