Ranking system from names and scores

E

EARTHWALKER

Thanks to Ron and AlfD, I have my new spreadsheet half done.

This will make it complete, although I won't matter a huge amount if n
one can work it out. I just like to keep your brains in tipto
condition :D

Ok, here we go.

Cell D2 contain someones name.
F20 contains this persons overall score.

also we have G2=name and final score in I20
so the names are in series, D, G, J, M, P, S and V (2)
The scores are in F, I, L, O, R, U and X (20)


in Cells A26 is "1st", A27 is "2nd" - to A32 which is "7th"
B26 to B32 will contain a name.
C26 to C32 will contain a score.

Now, what I want is for it to look at the scores and in C26 put th
best score in. Second best in C27 , 3rd best in C28 e.t.c
In B26 to B32 I need it to look at the scores in the C column, an
input the name of the person it relates to.

A very tall order I know and probably impossible. I think it woul
mean adding a few other hidden columns for calculation purposes as
can't even begin to think of a single formula that would sort that out
 
L

Leo Heuser

From your data here's one way to do it.
The solution doesn't take duplicates in the
score list into account.

In B26 enter this array formula:

=OFFSET($D$2,0,MATCH(C26,IF(MOD(COLUMN(
$F$20:$X$20)-COLUMN($F$20)+3,3)=0,$F$20:$X$20),0)-1)

In C26 enter this array formula:

=LARGE(IF(MOD(COLUMN($F$20:$X$20)-COLUMN(
$F$20)+3,3)=0,$F$20:$X$20),ROW()-ROW($C$26)+1)

Both formulae must be entered with <Shift><Ctrl><Enter>,
also if edited later.

Select B26:C26 and doubleclick the fill handle (the little square
in the lower right corner of the selection)
 
E

EARTHWALKER

That almost works Leo. In columns g, h, J, K, M, N, P, Q, S, T, V and
I have values so your formula picks those up too and puts those in a
the high scores as they are somewhat larger than the points. :D

Any chance you can edit it please. Also, not picking up duplicate
would be a pain as early on there most definately will be.

if it could look at the duplicates and just list them in series, tha
would be good.

So say F, I and J all had the same score, it would just list them i
that order, or aphabetical.

Many thanks though....ooh this is soo close. I can see you are hangin
on for that box of Jaffa Cakes :D:cool
 
A

AlfD

Hi!

A slightly different approach:

It isn't difficult to sort out the 1st, 2nd etc scores.

set B26 =LARGE($F$20:$F$50,1)
set B27 =LARGE($F$20:$F$50,2)
etc down to
set B32 =LARGE($F$20:$F$50,7)

If you have problems generalising this to other columns of data, com
back!

This deals with duplicates by listing all of the duplicate entries.

Now to bring in the names.

It is a pity your data structure doesn't keep the name and th
corresponding score in the same row. If you could achieve that, lif
would be simpler and probably less prone to error!

However: we can work with what you have.

I would create a "helper" lookup table containing the names and th
scores in neighbouring columns and _in_the_same_rows_ .

eg Set AB2:AB32 to be the same as D2:D32 and set AA2:AA32 to be th
same as F20:F50.
Then you can set C26 =vlookup(B26,AA2:AB32,2,FALSE) to get the name.

Al
 
E

EARTHWALKER

This one is just fictitous, but is basically a small template of a ver
much larger sheet, containing hundreds of names, score and other bit
'n' bobs. But it will mimic exactly what the finished product will be
 
A

AlfD

Hi!

Close.
I said B26: should have been C26 by the looks of your picture and you
earlier posts.

Do you want to send me a copy of a suitable version to look at?

Use alfreddearnley<a t>btinternet<d o t>c o m.

Al
 
L

Leo Heuser

Nothing like a good night's sleep :)

Try these formulae instead:

In B26:

=OFFSET($D$2,0,SMALL(IF(C26=IF(MOD(COLUMN($F$20:$X$20)-
COLUMN($F$20)+3,3)=0,$F$20:$X$20),COLUMN($F$20:$X$20)-
COLUMN($D$2)-2),COUNTIF($C$26:C26,C26)))

Will handle duplicates from left to right.
Please notice the use of mixed references in COUNTIF($C$26:C26,C26)

In C26:

=LARGE(IF(MOD(COLUMN($F$20:$X$20)-COLUMN($F$20)+3,3)=0,
$F$20:$X$20),MIN(ROW()-ROW($C$26)+1))

Just a slight correction from the earlier version: MIN(ROW()-ROW($C$26)+1)
instead of ROW()-ROW($C$26)+1, because the formula didn't like an array in
this situation.

Both formulae again to be entered with <Shift><Ctrl><Enter> and
copied down.
 
Top