+ Reply to Thread
Results 1 to 5 of 5

Odd and even number sorting

  1. #1
    Registered User
    Join Date
    02-28-2015
    Location
    Australia
    MS-Off Ver
    2010-2013
    Posts
    19

    Odd and even number sorting

    Hi all...
    my first post..
    i have a dilemma, i have data in the following ways which i wold like to sort by odd and even numerical value

    i have a range from A1-01-1A to A9-10-4B
    where only the middle digits of the data needs sorting to get even numbers and odd numbers..

    what formula needs to be written to make the sort possible ?

    Thank you

  2. #2
    Valued Forum Contributor
    Join Date
    11-22-2014
    Location
    Arizona, USA
    MS-Off Ver
    Office 365
    Posts
    973

    Re: Odd and even number sorting

    You can add a column next to it that is like this
    IF you put in the new column on A and your data was in B
    A2=ISODD(MID(B2,4,2))

    Now if you don't know that it will always start there, but the value you are checking will always follow the first hyphen you can do this
    A2=ISODD(MID(B2,FIND("-",B2)+1,2))

    Both should get the same result which is TRUE/False then you can sort on True or False
    -If you think you are done, Start over - ELeGault

  3. #3
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Odd and even number sorting

    I don't really know what your real values look like so I made some up just to illustrate this proposed solution.

    With data starting in A2 and down the column, enter this formula in B2 and fill down. This formula leaves the odd numbers as they are but adds 10000 to even numbers. This will allow a sort to have the odd numbers together before the even numbers that will also be together as a group and both groups will be in order of their middle numbers.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Before:
    A
    B
    2
    A1-01-1A
    1
    3
    A9-10-4B
    10010
    4
    A1-02-1A
    10002
    5
    A9-13-4B
    13
    6
    A1-04-1A
    10004
    7
    A9-22-4B
    10022
    8
    A1-05-1A
    5
    9
    A9-12-4B
    10012
    10
    A1-33-1A
    33
    11
    A9-15-4B
    15
    12
    A1-41-1A
    41
    13
    A9-98-4B
    10098
    14
    A1-07-1A
    7
    15
    A9-20-4B
    10020
    16
    A1-09-1A
    9
    17
    A9-56-4B
    10056
    18
    A1-11-1A
    11
    19
    A9-40-4B
    10040


    After:
    A
    B
    2
    A1-01-1A
    1
    3
    A1-05-1A
    5
    4
    A1-07-1A
    7
    5
    A1-09-1A
    9
    6
    A1-11-1A
    11
    7
    A9-13-4B
    13
    8
    A9-15-4B
    15
    9
    A1-33-1A
    33
    10
    A1-41-1A
    41
    11
    A1-02-1A
    10002
    12
    A1-04-1A
    10004
    13
    A9-10-4B
    10010
    14
    A9-12-4B
    10012
    15
    A9-20-4B
    10020
    16
    A9-22-4B
    10022
    17
    A9-40-4B
    10040
    18
    A9-56-4B
    10056
    19
    A9-98-4B
    10098
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  4. #4
    Registered User
    Join Date
    02-28-2015
    Location
    Australia
    MS-Off Ver
    2010-2013
    Posts
    19

    Re: Odd and even number sorting

    thank you both for the quick responses, both worked well and has added to my learning

  5. #5
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Odd and even number sorting

    Thank you for the feedback.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. sorting the number not whether it's = or -
    By Ian99099 in forum Excel General
    Replies: 3
    Last Post: 02-21-2015, 11:16 AM
  2. Sorting on the last number
    By kyoushin in forum Excel General
    Replies: 1
    Last Post: 10-22-2007, 08:42 AM
  3. Number sorting
    By ReD-DevIL in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-30-2006, 10:40 PM
  4. Sorting by number of ocurrences!
    By tbalza in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-02-2006, 07:16 PM
  5. Sorting number as text
    By boris_lui in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-03-2005, 11:47 AM

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