Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Sort Data Using Formula To Find Top X

  1. #1
    Senior Member
    Join Date
    Mar 2013
    Posts
    146
    Rep Power
    0

    Sort Data Using Formula To Find Top X

    I own a list of students,Iwant to be arranged depending on the total Top
    Attached Files Attached Files

  2. #2
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    I do not understand the logic behind the "ordered" list you say you want. The names are not in any alphabetic order nor is the list order by the numerical values in either the Total or Ranking column. Can you explain what determines the order you show for the "I want this list"?



    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 06-12-2023 at 05:45 PM.

  3. #3
    Senior Member
    Join Date
    Mar 2013
    Posts
    146
    Rep Power
    0
    I want to arrange the names depending on the total from high to low
    To became look like the first list
    Last edited by mahmoud-lee; 06-02-2013 at 06:12 AM.

  4. #4
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by mahmoud-lee View Post
    I want to arrange the names depending on the total from high to low
    To became look like the first list
    Oh, you showed the list backwards from what I was expecting. Okay, your out-of-order list is in cells A17:C29... Select that range, then click Data/Sort in your XL2003 worksheet, select "Ranking" from the first drop-down box (labeled "Sort by") and select the option button labeled "Ascending" next to it... make sure the option button on the bottom labeled "Header row" is selected (it should be by default) and then click the OK button.

  5. #5
    Senior Member
    Join Date
    Mar 2013
    Posts
    146
    Rep Power
    0
    I know very well how sorted data, but I want a solution by formula

  6. #6
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    Please find attached.
    Attached Files Attached Files
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  7. #7
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Use B3=LARGE($B$18:$B$29,ROWS(B$18:B18)) and drag down to get the data sorted by the 'total' column, and

    Use A3=INDEX($A$18:$A$29,SMALL(IF(B3=$B$18:$B$29,ROW(I NDIRECT("1:"&ROWS($B$18:$B$29))),""),COUNTIF($B$3: $B3,B3))) as an array formula to get the corresponding names.

    Your third column was anyway a rank, so I just used the ranking formula
    Attached Files Attached Files
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  8. #8
    Senior Member
    Join Date
    Mar 2013
    Posts
    146
    Rep Power
    0
    Thank you very much Mr. admin
    Thank you very much Mr. excel fox
    Thank you very much Mr. rick
    Excellent solution

  9. #9
    Senior Member
    Join Date
    Mar 2013
    Posts
    146
    Rep Power
    0
    can you arrange all data (name ,total) by other way ,Away from the array formula
    Last edited by mahmoud-lee; 06-02-2013 at 03:19 PM.

  10. #10
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Are you saying you don't want a solution with array formula?
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

Similar Threads

  1. Replies: 6
    Last Post: 05-10-2013, 01:13 AM
  2. Replies: 4
    Last Post: 04-05-2013, 12:08 PM
  3. Sort data sheet by right_click of mouse
    By Rasm in forum Excel and VBA Tips and Tricks
    Replies: 3
    Last Post: 12-08-2012, 07:34 PM
  4. Excel Macro to Sort Data if a value changes in defined range
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 4
    Last Post: 09-05-2012, 10:31 AM
  5. Sort Data When a Header Is Clicked
    By Rasm in forum Excel Help
    Replies: 9
    Last Post: 08-01-2012, 06:46 AM

Posting Permissions

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