Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorting odd and even (2000)

    Hi,
    I have an Excel list with names, street numbers, cities, etc. I'd like to sort the list by street numbers, with all the odd numbers first followed by all the even numbers. Can anyone suggest a way to do this? Thanks.

    Wyley

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sorting odd and even (2000)

    Create a new column in whicjh you can place an indicator for Odd/Even. You can use a number of functions to help such as ISEVEN(), ISODD() or MOD().

    For example if your street numbers are in column A enter the following formula in a new column :

    <big>=ISEVEN(A2)</big>

    which should place a TRUE or FALSE in the Cell. As FALSE sorts before TRUE you can use the new column as your primary sort key and the street number column (A in this case) as the secondary sort key

    Andrew C.

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting odd and even (2000)

    Thanks very much, Andrew. I used the ISEVEN() function and it worked fine. My street numbers are in a separate column. Just out of curiosity, do you know a way to sort odd/even when the street number and street name are in the same cell?

    Wyley

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sorting odd and even (2000)

    The same approcah could be used.

    Assuming that the street number comes first and has a following space the following should work :

    =ISEVEN(LEFT(A2,FIND(" ",A2)-1))

    or if the number has a following comma then

    =ISEVEN(LEFT(A2,FIND("<font color=red>,</font color=red>",A2)-1))

    The result should give TRUE or FALSE to provide the primary sort key.

    Andrew C

  5. #5
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting odd and even (2000)

    Hi Folks

    Observation.... If these were home addresses, you may need to cater for folks who have no house numbers but instead house names.

    Regards
    Peter

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sorting odd and even (2000)

    Wyley,

    On second thoughts you would need 2 columns, 1 for the odd/even indicator and one to render the number part of the sreet address as a numeric, so that it sorts correctly. 121 would come before 21 if both were text.

    Create a column to get the number with the formula =VALUE(LEFT(A2,FIND(" ",A2))), and use the previous approach in another column for the Odd/Even indicator. Then sort with the latter as primary sort key and the numeric value as the second.

    Andrew

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sorting odd and even (2000)

    Peter, a sensible observation, but names cannot be sorted on an Odd or Even basis.

    Some test could be used to check for the absence of a number, and in that case use a value of zero (even) used.

    Andrew C

  8. #8
    Star Lounger
    Join Date
    Jan 2001
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting odd and even (2000)

    Thanks for these suggestions. Just to keep matters simple, however, I think I'll put the street numbers and street names in separate columns!

    Wyley

Posting Permissions

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