G
Guest
Hello friends,
I believe that the solution to my problem lies in the
writing of an array formula; but since my creativity with
writing array formula is very limited I am posing the
question to you.
Here is the scenario:
I have a spreadsheet with details on the rankings of
students in a class. We have rankings every few months
(thus several times in a year).
Thus, column A is the name of the student. Column B is
the date when the ranking was taken. Column C is the rank
of the student on that given date. Since several such
rankings are done in a year; we have in column A
duplicates.
What I want to do is to find out the ranking of each
student during the last such ranking exercise.
On a separate TAB, in column A, I have already the names
of the students (thus unique values from column A). But
now my requirement is to have on this tab a column B that
gives the ranking of the student during the
chronologically last exercise done for ranking.
Can you tell me what formula to use.
As I mentioned above, I feel that it is an array formula;
taking all the rankings for the matching student; and
finally I need to take a MAX on the date of the ranking.
But obviously I am unable to think out the algortihm on my
own.
(BTW: if array formula is not the solution then please
feel free to recommend alternative strategies).
Lots of thanks in advance for your help.
I believe that the solution to my problem lies in the
writing of an array formula; but since my creativity with
writing array formula is very limited I am posing the
question to you.
Here is the scenario:
I have a spreadsheet with details on the rankings of
students in a class. We have rankings every few months
(thus several times in a year).
Thus, column A is the name of the student. Column B is
the date when the ranking was taken. Column C is the rank
of the student on that given date. Since several such
rankings are done in a year; we have in column A
duplicates.
What I want to do is to find out the ranking of each
student during the last such ranking exercise.
On a separate TAB, in column A, I have already the names
of the students (thus unique values from column A). But
now my requirement is to have on this tab a column B that
gives the ranking of the student during the
chronologically last exercise done for ranking.
Can you tell me what formula to use.
As I mentioned above, I feel that it is an array formula;
taking all the rankings for the matching student; and
finally I need to take a MAX on the date of the ranking.
But obviously I am unable to think out the algortihm on my
own.
(BTW: if array formula is not the solution then please
feel free to recommend alternative strategies).
Lots of thanks in advance for your help.