This can be done by macro

Sub Process()
Cells.Interior.ColorIndex = -4142
'Construct array of branches and count
Dim BranchArray()
ReDim BranchArray(2, 0)
For N = 2 To Cells(65536, 1).End(xlUp).Row
NewBranch = True
For M = 1 To UBound(BranchArray, 2)
If BranchArray(1, M) = Cells(N, 1) Then
NewBranch = False
BranchArray(2, M) = BranchArray(2, M) + 1
Exit For
End If
Next M
If NewBranch = True Then
ReDim Preserve BranchArray(2, UBound(BranchArray, 2) + 1)
BranchArray(1, UBound(BranchArray, 2)) = Cells(N, 1)
BranchArray(2, UBound(BranchArray, 2)) = 1
End If
Next N

'For each branch, find best performers
Dim SalesPersonArray
For M = 1 To UBound(BranchArray, 2)
ReDim SalesPersonArray(3, 0)
For N = 2 To Cells(65536, 1).End(xlUp).Row
If Cells(N, 1) = BranchArray(1, M) Then
ReDim Preserve SalesPersonArray(3, UBound(SalesPersonArray,
2) + 1)
SalesPersonArray(1, UBound(SalesPersonArray, 2)) = Cells(N,
2)
SalesPersonArray(2, UBound(SalesPersonArray, 2)) = Cells(N,
3) - Cells(N, 4)
SalesPersonArray(3, UBound(SalesPersonArray, 2)) = N
End If
Next N

'Sort SalesPersonArray
For P = 1 To UBound(SalesPersonArray, 2) - 1
For Q = 1 To UBound(SalesPersonArray, 2) - 1
If SalesPersonArray(2, P) SalesPersonArray(2, Q) Then
TempSalesPerson = SalesPersonArray(1, Q)
SalesPersonArray(1, Q) = SalesPersonArray(1, P)
SalesPersonArray(1, P) = TempSalesPerson
TempSalesAmount = SalesPersonArray(2, Q)
SalesPersonArray(2, Q) = SalesPersonArray(2, P)
SalesPersonArray(2, P) = TempSalesAmount
TempRow = SalesPersonArray(3, Q)
SalesPersonArray(3, Q) = SalesPersonArray(3, P)
SalesPersonArray(3, P) = TempRow
End If
Next Q
Next P

'Calculate how many Sales people are in the top 10%
Number = Int(UBound(SalesPersonArray, 2) / 10) + 1
For N = 1 To Number
Cells(SalesPersonArray(3, N), 3).Interior.ColorIndex = 6
Cells(SalesPersonArray(3, N), 4).Interior.ColorIndex = 6
Next N
Next M

'Now look for the to 10% overall


ReDim SalesPersonArray(3, 0)
For N = 2 To Cells(65536, 1).End(xlUp).Row
ReDim Preserve SalesPersonArray(3, UBound(SalesPersonArray, 2) +
1)
SalesPersonArray(1, UBound(SalesPersonArray, 2)) = Cells(N, 2)
SalesPersonArray(2, UBound(SalesPersonArray, 2)) = Cells(N, 3) -
Cells(N, 4)
SalesPersonArray(3, UBound(SalesPersonArray, 2)) = N
Next N

'Sort SalesPersonArray
For P = 1 To UBound(SalesPersonArray, 2) - 1
For Q = 1 To UBound(SalesPersonArray, 2) - 1
If SalesPersonArray(2, P) SalesPersonArray(2, Q) Then
TempSalesPerson = SalesPersonArray(1, Q)
SalesPersonArray(1, Q) = SalesPersonArray(1, P)
SalesPersonArray(1, P) = TempSalesPerson
TempSalesAmount = SalesPersonArray(2, Q)
SalesPersonArray(2, Q) = SalesPersonArray(2, P)
SalesPersonArray(2, P) = TempSalesAmount
TempRow = SalesPersonArray(3, Q)
SalesPersonArray(3, Q) = SalesPersonArray(3, P)
SalesPersonArray(3, P) = TempRow
End If
Next Q
Next P

'Calculate how many Sales people are in the top 10%
Number = Int(UBound(SalesPersonArray, 2) / 10) + 1
For N = 1 To Number
Cells(SalesPersonArray(3, N), 3).Interior.ColorIndex = 3
Cells(SalesPersonArray(3, N), 4).Interior.ColorIndex = 3
Next N


End Sub


A workbook is attached.

Hope this helps.


+-------------------------------------------------------------------+
|Filename: Book1.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4869 |
+-------------------------------------------------------------------+

--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=550756