Conditional Format Rank query

[Rich]

Hi,

In the example data below, I have branches, salespeople , sales and refunds
in columns A B Cand D.

I want to conditionally format C & D so that if a Sales Person is in the top
10% of salespeople in their branch, they are highlighted yellow, but
highlighted red if top 10% of salespeople in the whole company.

Example

Branch SalesPerson Sales
Refunds
London Fred 2900
0128
London Agnes 4563
1029
London Mark 4560
1856
London Peter 3167
0462
London Paul 3268
0532
London Glen 4563
0126
London Aaron 2153
0000
London Antony 1567
0292
London Justine 4590
2901
London Richard 0530
6004
London Robert 5310
0029
London Jane 7500
4502
London Angela 3782
0355
London Roberta 7443
0012
London Amy 14543
2023
London Emily 4986
0102
London Phil 4160
0029
Birmingham Jim 1789
0765
Birmingham Joan 21789
0715
Birmingham Graeme 11780
2765
Birmingham Jenny 1765
6507
Birmingham Gerry 2568
0
Birmingham Tony B 7639
1376
Birmingham Gordon 0341
11896
Birmingham David 6285
0375
Birmingham Jimmy 1693
0567
Birmingham Bob 8345
1621
Birmingham Johnny 17891
1076
Birmingham Karl 21784
1721
Birmingham Ken 0028
0

Can any help with this ?

Thanks,

Rich

[mrice]

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.

[Rich]

"mrice" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

Thanks I'll give that a try and let you know. Unfortunately I cant access
that file...

Rich

[mrice]

Drop me an e-mail via my homepage and I'll send it to you.