Joint ranking (never seen a satisfactory answer!)

U

uberyes

How on EARTH can I do this:

Name CVS JOBS INTERVIEWS OFFERS STARTS
Peter 9 5 1 0 1
Paul 9 5 1 0 1
Susan 4 1 3 0 0
Jane 3 3 4 1 0
Andrew 3 4 2 0 0
John 2 4 2 1 0
Roger 2 3 1 1 0
Lewis 2 6 3 0 0
Rob 2 4 4 2 0
Keith 2 3 3 2 2
Jason 2 6 3 0 0
Jim 2 6 4 1 0
Ken 2 1 1 0 1
Matt 2 0 0 0 0


Top CVs: Peter and Paul
Top Jobs: Jason and Jim and Lewis
Interviews: Jane and Rob and Jim
Top Offers: Rob and Keith
Top Starts: Keith

Many thanks,

Rob
 
B

bpeltzer

Can you add a helper column for each metric?
Let's just look at Name and CVs, assuming that 'Name' is in A1, etc.
Then clear C1 and in C2 put =IF(RANK(B2,B$2:B$6)=1,C1 & $A2 & " and ",C1).
Fill that formula through column C, and replace the range B$2:B$6 with the
appropriate ending row. If your final row is 6, then put this formula
anywhere to see the result: ="Most CVs: " & LEFT(C6,LEN(C6)-5)
The same approach would work for each metric, just changing the references
to B and C to point to the raw data and the new helper column, respectively.
HTH. --Bruce
 
B

Bernie Deitrick

Rob,

You need to use a User-Derfined-Function (or a WHOLE lot of worksheet functions).

Copy the code below into a code module in the workbook where you want the results, and then use the
function like:

=Top(B2:B15,$A$2:$A$15)

This example usage is for your example table starting in cell A1. Copy the cell with the formula to
the right to match your other columns.

HTH,
Bernie
MS Excel MVP


Function Top(Scores As Range, Names As Range) As String
Dim i As Integer
Dim oldi As Integer
Dim myMax As Double
myMax = Application.Max(Scores)
i = Application.Match(myMax, Scores, False)
On Error GoTo AllFound
Start:
If Top = "" Then
Top = Names(i).Value
Else
Top = Top & " and " & Names(i).Value
End If
oldi = i
i = Application.Match(myMax, Scores.Offset(i, 0). _
Resize(Scores.Cells.Count - i), False)
i = i + oldi
GoTo Start
AllFound:
End Function
 
R

Ron Rosenfeld

How on EARTH can I do this:

Name CVS JOBS INTERVIEWS OFFERS STARTS
Peter 9 5 1 0 1
Paul 9 5 1 0 1
Susan 4 1 3 0 0
Jane 3 3 4 1 0
Andrew 3 4 2 0 0
John 2 4 2 1 0
Roger 2 3 1 1 0
Lewis 2 6 3 0 0
Rob 2 4 4 2 0
Keith 2 3 3 2 2
Jason 2 6 3 0 0
Jim 2 6 4 1 0
Ken 2 1 1 0 1
Matt 2 0 0 0 0


Top CVs: Peter and Paul
Top Jobs: Jason and Jim and Lewis
Interviews: Jane and Rob and Jim
Top Offers: Rob and Keith
Top Starts: Keith

Many thanks,

Rob
Click to expand...

That's tough to do, I think, without using VBA.

To come up with a usable output, though, is not as difficult.

1. Select your table and Insert/Name/Create and check Top Row.
2. Copy your column names to your results area. I used O1:S1 (no need to copy
the Name: Name)

3. Select a range O2:On where n is the maximum number of candidates you might
be looking at.

4. Format/Conditional Formatting (assuming O2 is the active cell)
Condition 1: Formula Is =ISNA(O2)
Format Font Color = White (or whatever your background color is)
Add
Condition 2: Cell Value Is Equal To =O1
Format Font Color = White (or whatever your background color is)
OK

5. Select O2:On
6. Paste the following formula into the formula bar. Then hold down
<ctrl><shift> while you hit <enter>. Excel should place braces {...} around
the formula:

=INDEX(Name,LARGE((MAX(INDIRECT(O1))=
INDIRECT(O1))*ROW(INDIRECT(O1)),ROW(
INDIRECT("1:"&COUNTIF(INDIRECT(O1),MAX(
INDIRECT(O1))))))-1)

7. Copy/drag across to column S.

If you require a different type of output, post back as it could be done using
VBA.


--ron
 
U

uberyes

Let's make this even harder then....

I was wondering if it was possible to say

Top CVs: Peter and Paul - 9 each
Top Jobs: Jason and Jim and Lewis - 6 each

etc?
 
B

Bernie Deitrick

Function Top(Scores As Range, Names As Range) As String
Dim i As Integer
Dim oldi As Integer
Dim myMax As Double
myMax = Application.Max(Scores)
i = Application.Match(myMax, Scores, False)
On Error GoTo AllFound
Start:
If Top = "" Then
Top = Names(i).Value
Else
Top = Top & " and " & Names(i).Value
End If
oldi = i
i = Application.Match(myMax, Scores.Offset(i, 0). _
Resize(Scores.Cells.Count - i), False)
i = i + oldi
GoTo Start
AllFound:
If InStr(1, Top, " and ") > 0 Then
Top = Top & " - " & myMax & " each"
Else
Top = Top & " - " & myMax
End If
End Function

HTH,
Bernie
MS Excel MVP
 
Top