+ Reply to Thread
Results 1 to 3 of 3

Custom List Sorts Out of Order

  1. #1
    Registered User
    Join Date
    10-05-2012
    Location
    Ohio
    MS-Off Ver
    Office 365 for PC
    Posts
    11

    Custom List Sorts Out of Order

    In excel 2010 (on a W7 64-bit machine), I have created the following custom list:

    Benzene
    Toluene
    Ethylbenzene
    Total Xylenes
    MTBE
    Benzo(a)anthracene
    Benzo(a)pyrene
    Benzo(b)fluoranthene
    Benzo(k)fluoranthene
    Chrysene
    Dibenz(a,h)anthracene
    Indeno(1,2,3-cd)pyrene
    Naphthalene

    Problem is, when I select a range to sort, the results are out of order, like this (the last 3 entries are out of order):

    Benzene
    Toluene
    Ethylbenzene
    Total Xylenes
    MTBE
    Benzo(a)anthracene
    Benzo(a)pyrene
    Benzo(b)fluoranthene
    Benzo(k)fluoranthene
    Chrysene
    Naphthalene
    Dibenz(a,h)anthracene
    Indeno(1,2,3-cd)pyrene

    Odd thing is, when I go to the custom list dialogue box & highlight my custom list, the list is displayed in the correct order in the "List Entries" window. Even more odd, is if I type the 1st list entry (i.e. benzene) in a cell and drag down, the list will auto fill correctly.

    I have tried deleting the list, restarting excel, and recreating the list to no avail. There are no other custom lists (other than the 4 default excel lists) displayed in the custom list dialogue.

    Does anyone have any insight as to how I could sort (HA - pun!) this out?

    Thanks in advance,

    Lucas

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,431

    Re: Custom List Sorts Out of Order

    The commas in the text are probably causing the confused sorting. Comma is the list delimiter.
    If you replace comma with space in both the custom list and your data you will see the sort works as expected.

    So you may need to add extra column of data to your existing data where commas are replaced with spaces in order to sort the way you want.
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    10-05-2012
    Location
    Ohio
    MS-Off Ver
    Office 365 for PC
    Posts
    11

    Re: Custom List Sorts Out of Order

    Andy, thank you for the heads up with respect to the commas as they indeed were the culprits. In order to maintain both the auto fill and sorting functionality, I had to create two custom lists: one custom list where the commas are replaced with spaces and one custom list with commas. The custom list with commas gives me my desired auto fill; however, I had to make sure that when creating the two custom lists, the custom list with commas was created second. This was necessary as apparently when there are two custom lists that start with the same word, the auto fill text comes from the bottom of the two lists.

    With respect to sorting, I will use a helper column as you indicated - the helper column will duplicate the text in the adjacent column; however, all commas will be replaced with spaces. Then, after selecting my entire range (including the helper column) I will sort on the helper column using the custom sort order list where the commas were replaced with spaces. Once complete, the helper column will be deleted.

    Thanks again,

    Lucas

+ 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