Creating custom list with a comma in it

B

barnabel

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
 
D

Dave Peterson

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.
 
B

barnabel

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.
 
D

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.
 
B

barnabel

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.
 
D

Dave Peterson

Glad you got it working!
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.
Click to expand...
 
I

iceman84

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.
 
Top