+ Reply to Thread
Results 1 to 7 of 7

Creating custom list with a comma in it

  1. #1
    barnabel
    Guest

    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. #2
    Dave Peterson
    Guest

    Re: Creating custom list with a comma in it

    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. #3
    barnabel
    Guest

    Re: Creating custom list with a comma in it

    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. #4
    Dave Peterson
    Guest

    Re: Creating custom list with a comma in it

    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. #5
    barnabel
    Guest

    Re: Creating custom list with a comma in it

    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. #6
    Dave Peterson
    Guest

    Re: Creating custom list with a comma in it

    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. #7
    iceman84
    Guest

    Re: 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
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1