Results 1 to 4 of 4

Thread: Targeting top result for each entry
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2019
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Targeting top result for each entry

    Hi,
    I have a workbook that lists all terms attended for each student as well as their GPA for that term in descending order. What I am trying to achieve is target the top entry for each student, which will give me the last term attended as well as their last known GPA. Please see a short example of a much larger list below.


    Student ID Last Name First Name Cumulative GPA Term
    122145 Smith John 3.1 5
    122145 Smith John 2.9 4
    122145 Smith John 3.45 3
    122651 Reid Jane 3.7 4
    122651 Reid Jane 3.0 3
    122156 Hayes Ben 4.0 6
    122156 Hayes Ben 3.52 5

    As you can see from the example, the last term attended is always the first entry for each student. I would need Term 5 for John, Term 4 for Jane, and Term 6 for Ben. This would give me their last term attended, and cumulative GPA. I tried filtering out by specific term but that would throw off the numbers because each student's last term attended is different.
    Is there a way I can target the top entry for each student? Please kindly advise.
    Thanks!!

  2. #2
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,631
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Targeting top result for each entry

    You could add a helper column which flags the uppermost entry per student. Then filter on that:

    ABCDEF
    1Student IDLast NameFirst NameCumulative GPATermFilter
    2122145SmithJohn3.15TRUE
    3122145SmithJohn2.94FALSE
    4122145SmithJohn3.453FALSE
    5122651ReidJane3.74TRUE
    6122651ReidJane33FALSE
    7122156HayesBen46TRUE
    8122156HayesBen3.525FALSE

    Sheet3



    Worksheet Formulas
    CellFormula
    F2=COUNTIFS($B$2:$B2,B2,$C$2:$C2,C2)=1

    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  3. #3
    New Member
    Join Date
    May 2019
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Targeting top result for each entry

    Wow great stuff, works perfectly. You ROCK Eric!

    Thank you so much!!

  4. #4
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,631
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Targeting top result for each entry

    Glad to help!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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