Listing in Order
 

I have two columns The first has names of individuals the next has a list of
grades, is it possible for me to be able to make a formula in another cell
which will always list the students from the highest grade to the lowest
grade.

This is not a sort problem, because I want it to be able to do this
calculation when I change the grades around for example.

Thanks for your help in advance

Listing in Order
 

Short example:

In Range A1:B4 enter:

Bob 80
Ted 75
Carol 85
Alice 60

In C1 enter:
=LARGE($B$1:$B$4,ROW(1:1)) ' then copy C1 down to C4

In D1 enter:

=OFFSET($A$1,MATCH(C1,$B$1:$B$4,0)-1,0) ' then copy D1 down to D4.

If you insert or delete rows the Column C formula is going to HICK-UP as
the Row(2:2) will change to #REF! or the like -- The ROW(#:#) always need
to stay in the sequesnce (from beginning to end) as Row(1:1) sequentially to
Row(50:50), say.

Hope this helps,

Jim May




"emarzuq" wrote:

I have two columns The first has names of individuals the next has a list of
grades, is it possible for me to be able to make a formula in another cell
which will always list the students from the highest grade to the lowest
grade.

This is not a sort problem, because I want it to be able to do this
calculation when I change the grades around for example.

Thanks for your help in advance

Listing in Order
 

Wow, thank you very much that works out great.

When I was using the Large I was saying =LARGE($B$1:$B$4,1) and then
basically copying down and changing the 1 into 2 into 3 and so on.. could you
explain the ROW(1:1) I know what it does but I don't understand how it works.

Also, I had no idea Offset exists either. Could you please explain how that
formula works? if not then don't worry about it, the information you gave me
worked out great.

Thanks,

Eyad

"JMay" wrote:

Short example:

In Range A1:B4 enter:

Bob 80
Ted 75
Carol 85
Alice 60

In C1 enter:
=LARGE($B$1:$B$4,ROW(1:1)) ' then copy C1 down to C4

In D1 enter:

=OFFSET($A$1,MATCH(C1,$B$1:$B$4,0)-1,0) ' then copy D1 down to D4.

If you insert or delete rows the Column C formula is going to HICK-UP as
the Row(2:2) will change to #REF! or the like -- The ROW(#:#) always need
to stay in the sequesnce (from beginning to end) as Row(1:1) sequentially to
Row(50:50), say.

Hope this helps,

Jim May




"emarzuq" wrote:

I have two columns The first has names of individuals the next has a list of
grades, is it possible for me to be able to make a formula in another cell
which will always list the students from the highest grade to the lowest
grade.

This is not a sort problem, because I want it to be able to do this
calculation when I change the grades around for example.

Thanks for your help in advance

Listing in Order
 

Hello,

I just tested it out and there is one slight problem, I don't think these
are able to deferentiate if two entries happen to have the same number.

"JMay" wrote:

Short example:

In Range A1:B4 enter:

Bob 80
Ted 75
Carol 85
Alice 60

In C1 enter:
=LARGE($B$1:$B$4,ROW(1:1)) ' then copy C1 down to C4

In D1 enter:

=OFFSET($A$1,MATCH(C1,$B$1:$B$4,0)-1,0) ' then copy D1 down to D4.

If you insert or delete rows the Column C formula is going to HICK-UP as
the Row(2:2) will change to #REF! or the like -- The ROW(#:#) always need
to stay in the sequesnce (from beginning to end) as Row(1:1) sequentially to
Row(50:50), say.

Hope this helps,

Jim May




"emarzuq" wrote:

I have two columns The first has names of individuals the next has a list of
grades, is it possible for me to be able to make a formula in another cell
which will always list the students from the highest grade to the lowest
grade.

This is not a sort problem, because I want it to be able to do this
calculation when I change the grades around for example.

Thanks for your help in advance

Listing in Order
 

You can use Rows(1:1) as a substitute for the number 1.
When you do a copy - during the paste it converts up (automatically) to 2,
3, 4 etc..

The offset() has 3 parameters,
#1, The Anchor cell
#2 the number of rows you wish to "move" up or down from the Anchor row.
Negitives -2 (move up) Positive 2 (moves down)
#3 the number of columns you wish to "move" to the left or right from the
Anchor column.
Negitives -2 (moves left) Positive 2 (moves right)

Hope that helps,,

Jim May


"emarzuq" wrote:

Wow, thank you very much that works out great.

When I was using the Large I was saying =LARGE($B$1:$B$4,1) and then
basically copying down and changing the 1 into 2 into 3 and so on.. could you
explain the ROW(1:1) I know what it does but I don't understand how it works.

Also, I had no idea Offset exists either. Could you please explain how that
formula works? if not then don't worry about it, the information you gave me
worked out great.

Thanks,

Eyad

"JMay" wrote:

Short example:

In Range A1:B4 enter:

Bob 80
Ted 75
Carol 85
Alice 60

In C1 enter:
=LARGE($B$1:$B$4,ROW(1:1)) ' then copy C1 down to C4

In D1 enter:

=OFFSET($A$1,MATCH(C1,$B$1:$B$4,0)-1,0) ' then copy D1 down to D4.

If you insert or delete rows the Column C formula is going to HICK-UP as
the Row(2:2) will change to #REF! or the like -- The ROW(#:#) always need
to stay in the sequesnce (from beginning to end) as Row(1:1) sequentially to
Row(50:50), say.

Hope this helps,

Jim May




"emarzuq" wrote:

I have two columns The first has names of individuals the next has a list of
grades, is it possible for me to be able to make a formula in another cell
which will always list the students from the highest grade to the lowest
grade.

This is not a sort problem, because I want it to be able to do this
calculation when I change the grades around for example.

Thanks for your help in advance

Listing in Order
 

I was afraid you would have this situation. Daaaaaa..
Maybe someone else can solve that part, I can't..

Jim

"emarzuq" wrote:

Hello,

I just tested it out and there is one slight problem, I don't think these
are able to deferentiate if two entries happen to have the same number.

"JMay" wrote:

Short example:

In Range A1:B4 enter:

Bob 80
Ted 75
Carol 85
Alice 60

In C1 enter:
=LARGE($B$1:$B$4,ROW(1:1)) ' then copy C1 down to C4

In D1 enter:

=OFFSET($A$1,MATCH(C1,$B$1:$B$4,0)-1,0) ' then copy D1 down to D4.

If you insert or delete rows the Column C formula is going to HICK-UP as
the Row(2:2) will change to #REF! or the like -- The ROW(#:#) always need
to stay in the sequesnce (from beginning to end) as Row(1:1) sequentially to
Row(50:50), say.

Hope this helps,

Jim May




"emarzuq" wrote:

I have two columns The first has names of individuals the next has a list of
grades, is it possible for me to be able to make a formula in another cell
which will always list the students from the highest grade to the lowest
grade.

This is not a sort problem, because I want it to be able to do this
calculation when I change the grades around for example.

Thanks for your help in advance

Listing in Order
 

Check out this old post.

It'll take care of duplicates!

http://tinyurl.com/2tl9ar

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"emarzuq" wrote in message
...
Hello,

I just tested it out and there is one slight problem, I don't think these
are able to deferentiate if two entries happen to have the same number.

"JMay" wrote:

Short example:

In Range A1:B4 enter:

Bob 80
Ted 75
Carol 85
Alice 60

In C1 enter:
=LARGE($B$1:$B$4,ROW(1:1)) ' then copy C1 down to C4

In D1 enter:

=OFFSET($A$1,MATCH(C1,$B$1:$B$4,0)-1,0) ' then copy D1 down to D4.

If you insert or delete rows the Column C formula is going to HICK-UP as
the Row(2:2) will change to #REF! or the like -- The ROW(#:#) always need
to stay in the sequesnce (from beginning to end) as Row(1:1) sequentially
to
Row(50:50), say.

Hope this helps,

Jim May




"emarzuq" wrote:

I have two columns The first has names of individuals the next has a
list of
grades, is it possible for me to be able to make a formula in another
cell
which will always list the students from the highest grade to the
lowest
grade.

This is not a sort problem, because I want it to be able to do this
calculation when I change the grades around for example.

Thanks for your help in advance