+ Reply to Thread
Results 1 to 10 of 10

Returning a list of names based on their test scores without any gaps

  1. #1
    Registered User
    Join Date
    01-15-2006
    Posts
    15

    Returning a list of names based on their test scores without any gaps

    Hi, this sounds like it should be really easy but I am a bit stumped.
    I have a list of names and test scores, Names in A, test scores in B. I want to return a list on another sheet of the students who got over a certain test score and the score they got. I can do it with vlookup but I have to copy and paste the formula and I get gaps in the list. How can I return this data without leaving gaps in the list?

    Name Score
    Fred 12
    Alice 14
    Bob 6
    George 4
    Mildred 10
    Graham 8


    I want it to return names of students with scores >=10

    by using lookup and copying the formula I get

    Fred 12
    Alice 14


    Mildred 10

    I want it to look like this

    Fred 12
    Alice 14
    Mildred 10


    Thanks

    Phil

  2. #2
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,255

    Re: Returning a list of names based on their test scores without any gaps

    Try this one
    Attached Files Attached Files
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  3. #3
    Registered User
    Join Date
    01-15-2006
    Posts
    15

    Re: Returning a list of names based on their test scores without any gaps

    It works, but is it possible without creating the helper column in A?

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Returning a list of names based on their test scores without any gaps

    What version of Excel does this need to work in?
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Registered User
    Join Date
    01-15-2006
    Posts
    15

    Re: Returning a list of names based on their test scores without any gaps

    2010 mainly, but it would be good if it worked back to 2003.

    Phil

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Returning a list of names based on their test scores without any gaps

    Try this...


    Data Range
    A
    B
    C
    D
    E
    1
    Name
    Score
    -----
    Name
    Score
    2
    Fred
    12
    Fred
    12
    3
    Alice
    14
    Alice
    14
    4
    Bob
    6
    Mildred
    10
    5
    George
    4
    6
    Mildred
    10
    7
    Graham
    8


    Enter this array formula** in D2:

    =LOOKUP("zzz",CHOOSE({1,2},"",INDEX(A:A,SMALL(IF(B$2:B$7>=10,ROW(B$2:B$7)),ROWS(D$2:D2)))))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Enter this formula in E2:

    =IF(D2="","",VLOOKUP(D2,A$2:B$7,2,0))

    Select D2:E2 and copy down until you get blanks.

    This will work in ANY version of Excel.

  7. #7
    Registered User
    Join Date
    01-15-2006
    Posts
    15

    Re: Returning a list of names based on their test scores without any gaps

    I am having a little bit of trouble transferring your formula into my data.
    The original information is in 'Y11 Triple'C2:D31 and I want the list produced in 'Overview'B24 for the name and 'Overview'C24 for the data.
    Also what is "zzz"?

    Thanks

    Phil

  8. #8
    Registered User
    Join Date
    01-15-2006
    Posts
    15

    Re: Returning a list of names based on their test scores without any gaps

    Got it working. Brilliant.
    Thanks for your help.

    Phil

  9. #9
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Returning a list of names based on their test scores without any gaps

    If the names are in column B from B2:B7 and the scores are in column C from C2:C7 enter the target score that you want as a minimum in H1 and enter this formula in E2 and copy down to give you the list of names that meet the criteria set in E1.

    Enter with Ctrl + Shift + Enter

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


    Enter this formula to the right of the names and copy down to get the associated scores for each name:

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


    If you want to change the threshold of the values to be returned, set the value in E1.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  10. #10
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Returning a list of names based on their test scores without any gaps

    You're welcome. Thanks for the feedback!

+ 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. [SOLVED] Help ranking tutor groups based on test scores
    By leeus111 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 06-25-2013, 11:47 AM
  2. [Cricket] Selecting scores from list based on multiple factors.
    By cricket_stoner in forum Excel General
    Replies: 5
    Last Post: 03-26-2010, 11:15 PM
  3. Pull sub-test scores from test w/best overall test score (PSAT)
    By PowerSchoolDude in forum Excel General
    Replies: 0
    Last Post: 11-19-2009, 08:29 PM
  4. Names and scores of the two lowest scores
    By dmyoungsal in forum Excel General
    Replies: 3
    Last Post: 07-15-2008, 11:47 AM
  5. Calculate mean of test scores from rows of test answers
    By RiotLoadTime in forum Excel General
    Replies: 1
    Last Post: 07-26-2006, 12:20 PM

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