Hi

You have probably deleted this reference by now but for interest I finally
created a solution using a formula and macro. The formula was in response to
TVAlco's post regarding duplicate values in Sales. I know it is highly
improbably, but still.

To find the Nominal Rank, nominal because it depends on who occurs first in
the list, add this formula:=
=SUMPRODUCT(--(risk=A2),--(Sales=C2))-COUNTIF($C$2:C2,C2)+1

The macro uses this formula to decide where to place the Name.

Sub RiskByRank()
Dim i As Integer, lr As Integer, r As Integer, j As Integer
Dim c, risk, rnk As Integer, col As Integer
Dim rng As Range
risk = Array("Low", "Mid", "High")
'clear old data
Range("F1").CurrentRegion.ClearContents
lr = Range("a1").CurrentRegion.Rows.Count
Range("F1:H1") = risk
Set rng = Range(Cells(2, 1), Cells(lr, 1))
col = 6
For i = LBound(risk) To UBound(risk)
r = 1
For Each c In rng
rnk = c.Offset(0, 3)
If c = risk(i) Then
Cells(r + rnk, col) = c.Offset(0, 1)
End If
Next c
col = col + 1
Next i

End Sub

Regards
Peter Atherton


"Christian" wrote:

Hi Dave,
it worked!!
I had included the first row in the ranges.
Many thanks,
Enjoy a virtual belgian beer on my behalf
regards Christian

"Dave" wrote:

Hi Christian,
I tried TM's formula, and it works for me.

You have to name ranges:
A2:A10 = Risk
B2:B10 = Name
C2:C10 = Sales

Then enter:
Low into E1
Mid into F1
High into G1

Then TM's formula in E2
Fill across, then down.

Any luck?
Dave.