Help with array formula

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.

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&rank!
$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.
>.
>

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&rank!
>$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.
>>.
>>
>.
>