+ Reply to Thread
Results 1 to 7 of 7

How To Sort Values With A Formula?

  1. #1
    Forum Contributor
    Join Date
    01-16-2015
    Location
    Istanbul
    MS-Off Ver
    2010
    Posts
    803

    How To Sort Values With A Formula?

    I'm trying to sort the values on the A2:D6 according to their numbers.



    I removed the letters on the range B2:B6.



    Then I tried to put a RANK formula onto the range C2:C6 to rank them. It doesn't work for some reason?



    I want to sort them on the rance D2:D6.



    How can I do that?
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,767

    Re: How To Sort Values With A Formula?

    The RIGHT function in column B will return a text value, not a number (you can see that these are aligned differently in the column). To overcome this you can change your formula in B2 to this:

    =IF(LEN(A2)=2,--RIGHT(A2, 1), A2)

    The double minus sign in front of the RIGHT function will ensure that a number is returned, so if you copy this down then your RANK formula will work correctly in column C.

    Hope this helps.

    Pete

  3. #3
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,197

    Re: How To Sort Values With A Formula?

    Please try at D2

    =INDEX(A$2:A$6,MOD(AGGREGATE(15,6,RIGHT(A$2:A$6)*10^6+ROW(A$2:A$6),ROWS(D$2:D2)),10^6)-ROW(A$1))
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    01-16-2015
    Location
    Istanbul
    MS-Off Ver
    2010
    Posts
    803

    Re: How To Sort Values With A Formula?

    Why my RANK formula doesn't work?

    (If It's because the formatting of the column B I formatted the column B as number and It doesn't work either.)

    I had considered using RANK formula then applying INDEX & MATCH to it.
    Last edited by zanshin777; 08-17-2020 at 02:08 PM.

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,767

    Re: How To Sort Values With A Formula?

    I explained why it doesn't work (some of the values are text values), and suggested a way to get it to work (using the double minus).

    Note that formatting will not change the underlying value, and the RANK formula only works with proper numbers.

    Pete

  6. #6
    Forum Contributor
    Join Date
    01-16-2015
    Location
    Istanbul
    MS-Off Ver
    2010
    Posts
    803

    Re: How To Sort Values With A Formula?

    Thank you very much Pete_UK and Bo_Ry.

    Both methods work.

  7. #7
    Forum Contributor
    Join Date
    01-16-2015
    Location
    Istanbul
    MS-Off Ver
    2010
    Posts
    803

    Re: How To Sort Values With A Formula?

    Then to be able to sort them I typed this formula on E2.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    It works.
    Last edited by zanshin777; 08-17-2020 at 10:38 PM.

+ 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. Pivot table - sort two column values using 2 different sort values
    By Jacks18928 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-07-2018, 07:38 AM
  2. Non Array Formula to Sort Values Alphabetically
    By bjnockle in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-28-2018, 06:21 PM
  3. [SOLVED] formula to sort values and remove duplicates
    By Chris Manion in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-19-2014, 06:39 PM
  4. [SOLVED] Formula problem, get and sort numbers between two values
    By Michaelice in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-04-2013, 03:36 AM
  5. Sort List Removing Certain Values (formula)
    By Filibuster in forum Excel General
    Replies: 1
    Last Post: 05-30-2012, 03:54 PM
  6. Formula to Sort Through Data and Display Specific Values
    By Sonic12040 in forum Excel General
    Replies: 1
    Last Post: 03-23-2010, 10:19 PM
  7. Formula to sort text values with spaces
    By slim in forum Excel General
    Replies: 4
    Last Post: 05-19-2006, 08:50 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