How to auto sort rows by a ranked column in Excel?

Hi, I was trying to auto sort rows by a ranked column in Excel. I’m aware that it is done by using macro and vba coding, but that is how far I went with my research. Wonder if you could please assist with a tute. Cheers, Gus

Explorer Asked on 26th July 2015 in Excel for Windows.
Add Comment
  • 2 Answer(s)

    We often come across well presented Excel reports which include tables that show sorted results. Most of the times these tables were sorted manually in Excel, using the Data > Sort command or using the Sort & Filter button on the Excel Ribbon.

    However, I feel, reports would be much easier to maintain and update if formulas could sort the data automatically (not VBA / Macros as you mentioned)

    There’s a simple way to sort data in reports automatically using Excel Formulas. I’m using RANK, INDEX & MATCH Functions below. Have a look:

    RE: How to auto sort rows by a ranked column in Excel?

    Columns A, B & E show the actual data (manually entered). Enter the following formulas for the cells shown (Column C, F & G), and copy/drag the formulas down their columns as needed:
    C5=RANK(B5,$B$5:$B$10,0)

    F5 =INDEX($A$5:$A$10,MATCH(E5,$C$5:$C$10,0))

    G5 =INDEX($B$5:$B$10,MATCH(E5,$C$5:$C$10,0))

    The formula in Column C ranks the scores, where the highest score is ranked 1 and the lowest score in the table is ranked 6.

    The formula in Column F pulls the NAME of the person with the sorted RANK in Column E matched with the RANK in Column C.

    The formula in Column G pulls the SCORE of the person with the sorted RANK in Column E matched with the RANK in Column C.

    ——————————————————————————–

    Now, there might be a situation where the scores in 2 or more rows are the same, consequently Excel assigns an equal rank to all these rows and the following rank is skipped. For example – In our data, say, Amit and Uyen have the same score 71. They both are assigned Rank 4 by the Excel formula and Rank 5 is skipped. In such an event, The Sorted Data will show a #N/A error against Rank 5 (See Row 9 in ‘Sorted Data’). How do we deal with this?

    RE: How to auto sort rows by a ranked column in Excel?

    This problem has a quick fix. Just insert a new column for an Adjusted Score and then rank those adjusted values. Here are the formulas for the cells shown:

    C5 =B5+0.0000001*ROW()

    D5 =RANK(C5,$C$5:$C$10,0)

    RE: How to auto sort rows by a ranked column in Excel?

    Column C adds a tiny amount to each Score, an amount based on the number of each row in the table. This ensures each value in ‘ADJ SCORE’ is unique, which in turn makes sure the RANK values in Column D to be unique. And this gives us our automatically sorted values in Columns F:H (Sorted Data).


    Enthusiast Answered on 4th September 2015.
    Add Comment

    If anyone has same problem again I think there is easier way to do this. Watch this video tutoria, VBA code is also included in video description.

    If you´re new to macros this is, I believe, the best solution of how to do automatic sorting in Excel 🙂

     

    https://www.youtube.com/watch?v=XqGGSID6TaY

     

    Explorer Answered on 25th January 2018.
    Add Comment
  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of use.