Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Format Rank query
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Format Rank query
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Format Rank query
"mrice" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Format Rank query
Drop me an e-mail via my homepage and I'll send it to you. -- 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Format - Format Transfer To Chart | Excel Discussion (Misc queries) | |||
Copying a conditional format to several cells | Excel Discussion (Misc queries) | |||
AHHHH-Get Data from Multiple Excel workbooks | Excel Discussion (Misc queries) | |||
Office2000: Conditional format behaves strangely | Excel Discussion (Misc queries) | |||
Conditional format | Excel Discussion (Misc queries) |