|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 04:35 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com