Check the last 5 grades student has received

[ShakJames]

Hi all,

I'm wondering if someone can help me. I have very big spreadsheet with a long list of students and the last 10 exam grades they've received.

What I want to do, based on the criteria of the students name, is pull the last 5 grades they got (newest to oldest). The list will change over time and the grades they received will drop off and they will get new grades. So basically when I add a new grade, the oldest grade will drop off because I only ever need the last 5 results. It would help if I could see Grade 1 (being the oldest) through to Grade 5 (being the newest).

I hope that makes sense. Any help would be massively appreciated.

Thanks

[zbor]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Tony Valko]

Is the data sorted/grouped by student name?

Are the dates for the student sorted? In what order?

It might be better if you could post a SMALL sample file so we can see your data structure.

A SMALL file will be about 20 rows worth of data!

[ShakJames]

Hi,

Thanks for the reply. I have mocked up an example spreadsheet (the really SS has over 200 unique student names). There is no date sorting. The grades at the top are the oldest and ones at the bottom are the newest.

Every time a student gets a grade it is just added (not ideal - no idea why it has been designed like this. Done before I arrived). The problem I now I have is that I need to extract it.

I want be able to that by typing students name in A2 (on After Sheet Worksheet on the SS) and for it to bring up the last 5 grades received to be populated in B2,C2,D2,E2,F2...

I hope this makes sense.

Thanks again.

[Tony Valko]

This array formula** entered in B2 and copied across to F2:

=IFERROR(INDEX('Student Grade Data'!$B:$B,LARGE(IF('Student Grade Data'!$A$2:$A$150='Student Grade Data'!$D2,ROW('Student Grade Data'!A$2:A$150)),COLUMNS($B2:B2))),"")

** 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.

[ShakJames]

Hi,

Thanks for quick response. Just added it to B2 and copied across to F2, as an array and I'm just getting a 0 across all the checks.

Any ideas?

[Tony Valko]

Ooops!

I wrote the formula on the Student Grade Data sheet then just cut/pasted it to the After Sheet and forgot to change the criteria cell:

'Student Grade Data'!$D2 should be $A2...

=IFERROR(INDEX('Student Grade Data'!$B:$B,LARGE(IF('Student Grade Data'!$A$2:$A$150=$A2,ROW('Student Grade Data'!A$2:A$150)),COLUMNS($B2:B2))),"")

Still array entered.

[xlnitwit]

The name cell is in fact A2, therefore

=IFERROR(INDEX('Student Grade Data'!$B:$B,LARGE(IF('Student Grade Data'!$A$2:$A$150=$A2,ROW('Student Grade Data'!A$2:A$150)),COLUMNS($B2:B2))),"")

still array entered as Tony specified, and then copy across.

[ShakJames]

Brilliant. Thanks to you both. That has worked an absolute treat.

Massively appreciate all your help.

[Tony Valko]

You're welcome. Thanks for the feedback!

[ShakJames]

No problem. I love these forums. People are extremely generous.