+ Reply to Thread
Results 1 to 5 of 5

Rank or Conditional Formatting?

  1. #1
    Registered User
    Join Date
    02-10-2013
    Location
    Lyons, CO
    MS-Off Ver
    Excel 2003
    Posts
    4

    Rank or Conditional Formatting?

    I have a subtotaled list of customer's business by brand and I want to apply conditional formatting, or ranking to highlight the top ten customers by brand. For example:

    Col G Lists the customer name
    Col H lists the 5 different brands (customer name in G repeats)
    Col I lists the dollar volume per brand

    I want to highlight the top ten customers for brand A, brand B, brand C etc.

    Applying conditional formatting to only visible cells could get me there, but I am not having any luck. Any other ideas?

    I am open to adding a column to note the rank, or highlight the cells.

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,327

    Re: Rank or Conditional Formatting?

    Maybe...

    J2 Copied down >> =SUMPRODUCT(($H$2:$H$100=H2)*(I2<$I$2:$I$100))+1

    Then for the highlighting...

    Conditional Formatting
    • Highlight applicable range >> =$G$2:$J$100
    • Home Tab >> Styles >> Conditional Formatting
    • New Rule >> Use a formula to determine which cells to format
    • Edit the Rule Description: Format values where this formula is true: =$J2<=10
    • Format… [Number, Font, Border, Fill]
    • OK >> OK
    HTH
    Regards, Jeff

  3. #3
    Registered User
    Join Date
    02-10-2013
    Location
    Lyons, CO
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Rank or Conditional Formatting?

    This is perfect Jeff, Thank you!

    One more thing. After looking at the results, I would like to create another column next to J where we just created the ranking. And I would like this column to read "Brand A 1, Brand A 2, Brand C 1, etc where applicable. Leaving the columns blank if the customer is not rated in the top 10 and tagging each row that contains one of the top 10 customers by brand. Any ideas what this formula would look like?

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,327

    Re: Rank or Conditional Formatting?

    So in K2 copied down...

    =H2&" "&J2

  5. #5
    Registered User
    Join Date
    02-10-2013
    Location
    Lyons, CO
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Rank or Conditional Formatting?

    Thank you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1