+ Reply to Thread
Results 1 to 9 of 9

Sorting name and score from two columns to another two columns in order by score

  1. #1
    Registered User
    Join Date
    11-20-2012
    Location
    North Carolina
    MS-Off Ver
    excel 2010
    Posts
    13

    Sorting name and score from two columns to another two columns in order by score

    So in column A I have a name and in column B I have a score. In column C I would like a name and in column D I would like the score but the data needs to be sorted from High score to low score in column C and D with the appropriate name corresponding with the appropriate score.

    I currently have the following formula that does part of the job.

    =INDEX(A$1:A$400,MATCH(LARGE($B$1:$B$400,ROWS($1:1)),$B$1:$B$400,0)) in column C
    =INDEX(B$1:B$400,MATCH(LARGE($B$1:$B$400,ROWS($1:1)),$B$1:$B$400,0)) in column D

    This works unless there is a duplicate score. Then the formula returns the correct score but the name is returned for the first person who recieved that score. Any ideas on how to make the function return the unique name for duplicate scores?

    Thanks in advance

  2. #2
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Re: Sorting name and score from two columns to another two columns in order by score

    How about just using filter/sort for columns A and B? You can sort for column B [Highest to Lowest].
    Or maybe Pivot Table? I am not too good with Pivot Tables but someone here can help you with that.
    1. Click on the * Add Reputation if you think this helped you
    2. Mark your thread as SOLVED when question is resolved

    Modytrane

  3. #3
    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
    44,023

    Re: Sorting name and score from two columns to another two columns in order by score

    In C2:
    =INDEX($A:$A,SMALL(INDEX(($B$2:$B$10<>D2)*10^10+ROW(B$2:B$10),0),COUNTIF(D$2:D2,D2)))

    In D2:
    =LARGE($B$1:$B$10,ROWS($1:1))
    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

  4. #4
    Registered User
    Join Date
    11-20-2012
    Location
    North Carolina
    MS-Off Ver
    excel 2010
    Posts
    13

    Re: Sorting name and score from two columns to another two columns in order by score

    Alright glenn we are close! however my lists will be around 300 entries I tried to change the numbers in the formula to reflect this and it did not work? Thank you so much for your help

  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
    44,023

    Re: Sorting name and score from two columns to another two columns in order by score

    So... what did you change?? from what to what???

  6. #6
    Registered User
    Join Date
    11-20-2012
    Location
    North Carolina
    MS-Off Ver
    excel 2010
    Posts
    13

    Re: Sorting name and score from two columns to another two columns in order by score

    =INDEX($A:$A,SMALL(INDEX(($B$2:$B$300<>D2)*300^300+ROW(B$2:B$300),0),COUNTIF(D$2:D2,D2)))

    anywhere you see 300 I changed 10 to 300.

  7. #7
    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
    44,023

    Re: Sorting name and score from two columns to another two columns in order by score

    Try:

    =INDEX($A:$A,SMALL(INDEX(($B$2:$B$300<>D2)*10^10+ROW(B$2:B$300),0),COUNTIF(D$2:D2,D2)))

    You'll have given Excel a coronary, trying to calculate 300 to the power of 300.... 10^10 is simply a large number that Excel can understand. 300^300... too many zeros...

  8. #8
    Registered User
    Join Date
    11-20-2012
    Location
    North Carolina
    MS-Off Ver
    excel 2010
    Posts
    13

    Re: Sorting name and score from two columns to another two columns in order by score

    You are correct! That was silly of me. fixed and functional. Thank you for your time and I will update to resolved

  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
    44,023

    Re: Sorting name and score from two columns to another two columns in order by score

    You're welcome.



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

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

+ 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. Replies: 9
    Last Post: 01-20-2016, 08:59 AM
  2. Replies: 1
    Last Post: 11-24-2013, 07:53 AM
  3. [SOLVED] display highest score from specific columns
    By sirdon in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-09-2013, 06:30 AM
  4. Moving a calclated score to a master score sheet and ranking the scores into placings
    By Jongleur69 in forum Excel Programming / VBA / Macros
    Replies: 30
    Last Post: 04-22-2013, 11:53 PM
  5. Replies: 2
    Last Post: 03-20-2012, 06:30 PM
  6. Determining Top Score of Multiple Columns
    By Johnmus in forum Excel General
    Replies: 5
    Last Post: 02-20-2012, 07:54 PM
  7. Pulling test score based on date, not highest score.
    By PowerSchoolDude in forum Excel General
    Replies: 2
    Last Post: 12-01-2009, 06:42 PM

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