Ranking without duplicates

[Joff]

Sorry to bring this subject up again, but i'm really confused!!!

My Example:

On a one sheet spread I want to auto sort and rank a list.

In A1 to A4 I have the players: John, Bob, Dave, Ian
In B1 to B4 I have their scores: 75, 31, 18, 31

How do I sort these so when the players scores are updated the list is auto
sorted in columns E and F with their ranked position in column D (John in
position 1)?

I am getting confused when the scores are tied (as in Bob and Ian's case).
All I seem to get when I follow other instruction on here is duplicate names!!

What I need to know is what formulas should exactly appear in each cell (not
just simply copy and paste!) to stop the duplicate entries happening.

If anyone could explain and perhaps send me an example by e-mail
([email protected]), this would be appreciated.

Many thanks for your time.

 

[Jim May]

Go here...

http://www.cpearson.com/excel/rank.htm

 

[T. Valko]

One way...

Enter this formula in D1:

=LARGE(B$1:B$4,ROWS($1:1))

Enter this formula in E1:

=INDEX(A$1:A$4,MOD(SUMPRODUCT(LARGE(B$1:B$4+(ROW(B$1:B$4)-MIN(ROW(B$1:B$4))+1)/1000,ROWS($1:1))),1)*1000)

Select both D1 and E1 and copy down as needed.

Results:

75.....John
31.....Ian
31.....Bob
18.....Dave

 

[Joff]

OK, when this is entered the duplicate names are replaced with #REF!

The copy down as needed advice causes an issue, because one needs to
actually see the formula that should be in each cell. It can't be exactly the
same as this causes issues also. The parts of the formula I had to adjust is:

In the "=LARGE(B$1:B$4,ROWS($1:1))" formula the ($1:1) to ($1:2), ($1:3) and
($1:4) respectively per cell and the same in the
"=INDEX(A$1:A$4,MOD(SUMPRODUCT(LARGE(B$1:B$4+(ROW(B$1:B$4)-MIN(ROW(B$1:B$4))+1)/1000,ROWS($1:1))),1)*1000)"

Should I be doing this or not? Either way, I can't get the duplicates
resolved!

Best regards

 

[Pete_UK]

I think you need to learn about copying/pasting formulae - you do not
need to type the formula into every individual cell.

Pete

 

[Joff]

That's not very helpful, Pete.

I am not typing in every entry! I have tried copy and paste, which
increments the cell refs, and i've copied and pasted direct into the formula
entry bar (which does not increment the refs). Neither seem to work, so what
do I actually do?

Without actually showing me what should be in the individual cells, so I can
work out for myself what is happening, or telling me another way to copy and
paste that I don't know about, I am still stuck with duplicate entries!

 

[Joff]

Sorry, Pete.

I now realise what the $ is for. I was leaving the $ out when I typed in
formulas, thinking it was just the way that this forum edited posts!

Once again, my apologies.

 

[Pete_UK]

That's alright, I normally give more fulsome comments, but I was a bit
pushed for time earlier. I tried to send you an email with my version
of this auto-sort, but it bounced back. If you are still looking for
an alternative, send me an email to:

pashurst <at> auditel.net

then I can reply back to you.

Pete

 

[Joff]

I have tried Biff's formula, but all I am getting is #REF! where the
duplicate score is. This is because some of my actual scores that I am
testing are in .5's rather than whole numbers as in my example (eg. changing
my original example above the numbers would be in B1-B4: 75, 31.5, 18, 31.5).
So what can be done to rectify this issue?

If I add another column at C for matches attended (reading C1-C4: 4, 2, 4,
3), as the scores of 31.5 are equal, it would be useful if i could rank them
by the lesser matches attended score. So if the scores are tied then the
person who has attended the least matches gets the higher ranking. So the
ranking required is (Rank-Player-Points) 1-John-75, 2-Bob-31.5, 3-Dave-31.5,
4-Ian-18.

In the event that the score is the same and the matches attended is also
same, I would prefer a joint ranking.

How easy would that be?

Many thanks

 

[Pete_UK]

It's getting a bit late here, so all I can do is refer you to this
link:

http://www.cpearson.com/excel/rank.htm

where Chip Pearson gives many examples of ranking formulae. If you
still have queries outstanding in the morning, then I'll get back to
you.

Hope this helps.

Pete