Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Apr 2003
    Location
    Carson, California, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Force a Sort Order? (2000)

    I've got a sorting problem . The Sort Ascending should give me 1 Main, 10 Main, 100 Main, 101 Main, 1000 Main but the attached shows the sort I'm getting. How do I change this?

  2. #2
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Force a Sort Order? (2000)

    These look like street addresses. Excel sees the string as text so "101 xx" comes after "1000 xx". If there's always going to be a space between the number and the street name, you could use DataText to Columns to split the addresses into one column for the numeric part and one column for the street name. Then use the street name as your primary key and the number as your secondary key.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Force a Sort Order? (2000)

    Another option is to add spaces to the numbers to represent the "missing text":
    "x Main" becomes " x Main" (3 spaces)
    "xx Main" becomes " xx Main" (2 spaces)
    "xxx Main" becomes " xxx Main" (1 space)
    "xxxx Main" becomes "xxxx Main" (No spaces)

    Then they will be sorted like numbers. There is no way to fix this without changing your way of keeping the data, either by separating the numbers out or adding the extra text, or writing your own VB sort routine.

    The crux of the problem is that "101 " > "1000" since the first 2 characters are identical, so the sort is based on the third and 1 > 0.

    Steve

Posting Permissions

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