Help Needed With Looking Up Data and Matching Name

[glenh9]

Hi. I am having some trouble finding the right formula to use and am
hoping for some help. I have a spreadsheet that is a statistical
database for a hockey team that I coach. I have made a new sheet to
track the leaders in each category and I have it set up so that in each
statistical category it will list the top five performences in rank
order. For Example goals leaders with #1 at the top and #5 at the
bottom looks as follows:

Goals Scored

12
9
7
6
4


I have no problems automating this part on my new sheet using the
data from the original sheet. Where I run into problems is that I want
my "leaders" sheet to not only list the top 5 totals of goals scored,
but I also want it to list the name of the player who has scored that
many goals next to it. Right now, I am having to put the player's name
in by hand, which is rather time consuming. Can anyone help me here?

 

[Domenic]

Have a look at Aladin's contribution here...

http://www.mrexcel.com/board2/viewtopic.php?t=69970&highlight=highest#jav
ascript:void(0);

Hope this helps!

 

[Gman]

Thanks for your help. That worked almost perfectly. There is only one
problem that I've not been able to resolve with this formula. Right
now if I have 2 values in my list that are tied, the formula will
always use the same first name associated with that value from the top
of my names list. I am presuming that at the end of your formula, I
should change the value from 0? I tried this, but with no success.
Any ideas? Thanks again for yhour help.

 

[Domenic]

Actually, this formula approach is specifically designed to deal with
such a situation. I have a feeling you may have missed the formula in
C3 of that example...

=RANK(B3,$B$3:$B$9)+COUNTIF(B3:$B$3,B3)-1

Is that the case?

 

[Gman]

You're right, I didn't look that far. However, after doing looking
further on that page, I realize that my understanding of Excel is not
enough to really follow what you're doing here. My first question is,
can I leave out column C, as I don't want that on my sheet? Second,
can you explain what these two parts of the formula are meant to do?
These are the parts I don't really understand:
E3<>""


ROW()-ROW($D$3)+1

Thanks again.

 

[Domenic]

...can I leave out column C, as I don't want that on my sheet?

Unfortunately, no. That's integral to the overall approach, which
accounts for ties.

However, you could hide the column. If that's not possible, you could
always hide the results using conditional formatting.

can you explain what these two parts of the formula are meant to do?
E3<>""

Without this, the formula would return unwanted results.

ROW()-ROW($D$3)+1

When the formula is entered in D3, it returns 1. As the formula is
copied down the column, the formula returns 2, then 3, and so on.

Hope this helps!

 

[JE McGimpsey]

One way, using a UDF:

This is a particularly inefficient sort, but for small numbers of
entries, it should work reasonably well.

Assume your names are in A2:A30 and goals scored in G2:G30, then select
a 5 row by 2 column area and array-enter (CTRL-SHIFT-ENTER or
CMD-RETURN):

=HighN(5, A2:A30, G2:G30)

If you're not familiar with UDF's see David McRitchie's "Getting Started
with Macros and User Defined Functions:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Put this in a regular code module:

Public Function HighN(_
n As Long, _
ByVal rng1 As Range, _
Optional ByVal rng2 As Range)
Dim vArr1 As Variant
Dim vArr2 As Variant
Dim vTemp As Variant
Dim i As Long
Dim bChanged As Boolean
If rng2 Is Nothing Then
If rng1.Columns.Count > 1 Then
Set rng2 = rng1.Offset(0, 1).Resize(, 1)
Else
HighN = CVErr(xlErrNum)
Exit Function
End If
End If
vArr1 = rng1.Value
vArr2 = rng2.Resize(rng1.Rows.Count, 1).Value
Do
bChanged = False
For i = 1 To UBound(vArr1, 1) - 1
If vArr2(i, 1) < vArr2(i + 1, 1) Then
vTemp = vArr1(i, 1)
vArr1(i, 1) = vArr1(i + 1, 1)
vArr1(i + 1, 1) = vTemp
vTemp = vArr2(i, 1)
vArr2(i, 1) = vArr2(i + 1, 1)
vArr2(i + 1, 1) = vTemp
bChanged = True
End If
Next i
Loop Until bChanged = False
ReDim vTemp(1 To n, 1 To 2)
For i = 1 To n
vTemp(i, 1) = vArr1(i, 1)
vTemp(i, 2) = vArr2(i, 1)
Next i
HighN = vTemp
End Function