Jason,
Great formula. Works like magic.
Many thanks.
-----Original Message-----
If your ranking data is on a sheet called "rank", try
this in B1 of the new sheet:
=INDEX(rank!$C$1:$C$10,MATCH(A1&MAX(IF(rank!
$A$1:$A$10=A1,rank!$B$1:$B$10)),rank!$A$1:$A$10&r ank!
$B$1:$B$10,0))
Array-entered (press ctrl/shift/enter). After array-
entering, fill the formula down.
HTH
Jason
Atlanta, GA
-----Original Message-----
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.
.
.
|