In the below example i need to only rank the topline (bold records) and ignore all the other values from the value column when ranking. The excel rank formula ranks "office" as 3 because "detached" (which is a row to be ignored when ranking) has a higher value. It should however be ranked 2 as i have manually illustrated below...Note the bold rows are a total of the regular font rows directly below it...
Any ideas on a formula that can help me rank only the values in bold while ignoring all other values from the column?
Rank Description Value 1 House 5,000,000 Detached 3,000,000 Semi 1,000,000 Terrace 1,000,000 2 Office 2,000,000 1st Floor 1,000,000 2nd Floor 1,000,000
UDF, if you like
1) Hit Alt + F11 to open VBE
2) go to [Insert] - [Module] then paste the code onto the right pane.
3) hit Alt + F11 again to get back to excel
RankBold.xlsmVB:Function RankBold(myvalue, rng As Range, order) As Long Dim x As Range, r As Range, n As Long Application.Volatile For Each r In rng.Columns(1).Cells If r.Font.Bold Then If x Is Nothing Then Set x = r(, 2) Else Set x = Union(x, r(, 2)) End If End If Next RankBold = WorksheetFunction.Rank(myvalue, x, order) End Function
There are currently 1 users browsing this thread. (0 members and 1 guests)