Announcement

Collapse
No announcement yet.

Rank Values By Bold Font

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Rank Values By Bold Font

    Hi all!

    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?

    Example:

    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

  • #2
    Re: Rank select cells only from a column

    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

    Use like

    =IFERROR(RankBold(C2,$B$2:$C$8,0),"")

    Code:
    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
    RankBold.xlsm

    Comment

    Working...
    X