+ Reply to Thread
Results 1 to 10 of 10

Rank and Sort 2 Columns auto.

  1. #1
    Registered User
    Join Date
    02-10-2022
    Location
    Waikato, New Zealand
    MS-Off Ver
    2019
    Posts
    8

    Rank and Sort 2 Columns auto.

    Hi
    I want to auto rank the results of a car sprint. The cars run over set distance and the time for each car is recorded. This is called Overall Rank. I have this auto sorting by using "Match" and "Index:. The cars run in groups called classes.( A,B, C, D etc) This the part I am having problems with. I can rank them by using "Sumproduct ", but am unable to auto sort them.
    I am using Excel 2019 with does not have the Sort Function
    The idea is to have a Dashboard sheet which stays in car Number order as an input sheet and have one sheet which has the results sorted by Overall Rank and an other sheet which is sorted to Class Rank.
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Rank and Sort 2 Columns auto.

    Each different shading = different formula. I am not going to paste them all here, but this is the one that sorts the car classes into alphabetical order.

    =IFERROR(INDEX(Sheet1!B:B,MOD(AGGREGATE(15,6,COUNTIF(Sheet1!$B$3:$B$12,"<"&Sheet1!$B$3:$B$12)*10^6+ROW(Sheet1!$B$3:$B$12),ROWS(B$3:B3)),10^6)),"")

    They are all ordinary formulae (just Enter...).
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Rank and Sort 2 Columns auto.

    Helper columns R and S used.
    In Sheet2
    In G3 then copied to G,I,J,K columns.
    Please Login or Register  to view this content.
    In H3 then copied down
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  4. #4
    Registered User
    Join Date
    02-10-2022
    Location
    Waikato, New Zealand
    MS-Off Ver
    2019
    Posts
    8

    Re: Rank and Sort 2 Columns auto.

    Thank to you both for different replies. There is heaps that I dont understand in the formula .. but that gives me challenge to break them down and learn. Both work, I was a bigger ask than I thought. Cheers

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Rank and Sort 2 Columns auto.

    If you get stuck, ask.

    You're welcome.



    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

  6. #6
    Registered User
    Join Date
    02-10-2022
    Location
    Waikato, New Zealand
    MS-Off Ver
    2019
    Posts
    8

    Re: Rank and Sort 2 Columns auto.

    Cheers.... I will get stuck and will need to ask

  7. #7
    Registered User
    Join Date
    02-10-2022
    Location
    Waikato, New Zealand
    MS-Off Ver
    2019
    Posts
    8

    Re: Rank and Sort 2 Columns auto.

    Hi
    On 12 feb 2022 Glenn Kenney replied to my post with a formula that I have been trying to understand, but am still struggling with.
    The formula is :

    =IFERROR(INDEX(Sheet1!B:B,MOD(AGGREGATE(15,6,COUNTIF(Sheet1!$B$3:$B$12,"<"&Sheet1!$B$3:$B$12)*10^6+ROW(Sheet1!$B$3:$B$12),ROWS(B$3:B3)),10^6)),"")

    I have tried to break it down and understand it bit by bit, So here is some questions.

    =IFERROR(INDEX(Sheet1!B:B,MOD
    What does MOD do here, is it looking for empty cell ?


    (AGGREGATE(15,6,COUNTIF(Sheet1!$B$3:$B$12,"<"&Sheet1!$B$3:$B$12)
    What are you checking here, does “<” mean less than


    *10^6+ROW(Sheet1!$B$3:$B$12),ROWS(B$3:B3)),10^6)),"")
    Whats the reason for multiply 10 to the power of 6 ?, This part of the formula has me complety lost ☹

    Cheers

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,345

    Re: Rank and Sort 2 Columns auto.

    You may wish to remove the SOLVED tag for now.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Rank and Sort 2 Columns auto.

    =IFERROR(INDEX(Sheet1!B:B,MOD(AGGREGATE(15,6,COUNTIF(Sheet1!$B$3:$B$12,"<"&Sheet1!$B$3:$B$12)*10^6+ROW(Sheet1!$B$3:$B$12),ROWS(B$3:B3)),10^6)),"")

    Red... counts the number of values that are less than the value being measured. Although the values are TEXT, Excel still is able to evaluate their alphabetical order as if they were numbers. The first letter is a B. there are 4 values less than B (the 4 A's so it returns 4. In the second cell (A) there are no values lower, so it returns zero. The third is B so it returns 4 again... and so on.

    Cyan It multiplies by 10^6.. returning 4000000; 0; 4000000

    Green It adds on the row number, returning 4000003, 4, 4000005

    Blue: it arranges the values in ascending order, returning: 4, 4000003, 4000005

    Purple this returns the REMAINDER after dividing by 1,000,000 returning> 4, 3, 5

    Orange: then returning the values in column B in th e order B4, B3, B5.... ie in alphabetical order.

    Black return a blank when it runs out of values.

  10. #10
    Registered User
    Join Date
    02-10-2022
    Location
    Waikato, New Zealand
    MS-Off Ver
    2019
    Posts
    8

    Re: Rank and Sort 2 Columns auto.

    Hi
    Sorry about the late repy but I have been busy at work ( I dont work on a computer )
    I ran the formula through Evaluate Formula and no your reply helps make it make sense
    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. Rank Data Based On Their Dates Without Ties and sort according to rank
    By boredinosaur in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-13-2021, 01:39 PM
  2. Need fix for VBA Code to Custom Sort multiple columns that includes DATE columns
    By Kirk3737 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-14-2020, 02:35 PM
  3. Sort/rank multiple columns
    By nightmare_101 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-09-2013, 05:56 AM
  4. Help with Rank, Auto Sort Features (Spreadsheet Attached)
    By jagerman18 in forum Excel General
    Replies: 2
    Last Post: 08-22-2012, 06:46 PM
  5. [SOLVED] Auto Rank and Sort Variables Number/Change
    By bertique in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 04-06-2012, 04:17 AM
  6. Auto Sort Columns
    By mgs in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-21-2011, 02:19 AM
  7. Auto rank/sort
    By Number_8 in forum Excel General
    Replies: 3
    Last Post: 06-08-2010, 08:33 AM

Tags for this Thread

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