Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Rank Values By Bold Font

1. I agreed to these rules
Join Date
28th April 2012
Posts
1

## 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

Excel Video Tutorials / Excel Dashboards Reports

2. Excel Samurai
Join Date
19th July 2004
Location
Tokyo, Japan
Posts
9,423

## 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),"")

VB:
```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

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno