Apply formatting to first instance of highest value wthin range (exclude duplicates)

**nurseydiamond** · 11-07-2015, 09:21 AM

I am using conditional formatting, applying it to a range of cells, such that the highest value is bolded and the cell color is changed. If I have three values I am comparing, but two are the same value and that is the highest value, then the formatting is applied to both cells that contain the value. How can I apply it to only the first instance of the highest value within the range I am comparing? I do not want to delete the duplicate; I just want it to apply the formatting to only the first instance of the highest value.

**Tony Valko** · 11-07-2015, 09:26 AM

If this is your data:

Data Range

	A
1	9
2	10
3	10
4	8
5	5
6	------

Select the entire range and apply the conditional formatting.

Use the "Use a formula..." option and use a formula like this:

=AND(A1=MAX(A$1:A$5),COUNTIF(A$1:A1,A1)=1)

**nurseydiamond** · 11-07-2015, 09:32 AM

Worked perfectly - thanks!

**Tony Valko** · 11-07-2015, 09:39 AM

You're welcome. Thanks for the feedback!

If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

**nurseydiamond** · 11-07-2015, 09:42 AM

Quick follow-up question: if I write the formula and apply to cells 1,2 and 3 in column A, is there an easy way to copy it to apply to cells 4,5, and 6, then 7,8, and 9, etc. without having to manually type the new range each time? I want to compare three sets at a time as individual groups, but apply the same formatting to each group. If I have to take the time to do it I will, but if there is an easier way, I'd love to learn it. Thanks!

**Tony Valko** · 11-07-2015, 10:50 AM

Unfortunately, you'll have to apply the formula to each separate range.

**nurseydiamond** · 11-07-2015, 11:01 AM

Boo, lol. Ok thanks!

**Tony Valko** · 11-07-2015, 11:05 AM

You're welcome!

Apply formatting to first instance of highest value wthin range (exclude duplicates)

LinkBack

Thread Tools

Rate This Thread

Display

Apply formatting to first instance of highest value wthin range (exclude duplicates)

Re: Apply formatting to first instance of highest value wthin range (exclude duplicates)

Re: Apply formatting to first instance of highest value wthin range (exclude duplicates)

Re: Apply formatting to first instance of highest value wthin range (exclude duplicates)

Re: Apply formatting to first instance of highest value wthin range (exclude duplicates)

Re: Apply formatting to first instance of highest value wthin range (exclude duplicates)

Re: Apply formatting to first instance of highest value wthin range (exclude duplicates)

Re: Apply formatting to first instance of highest value wthin range (exclude duplicates)

Thread Information

Users Browsing this Thread

Similar Threads

Want to multiply a range of cells by one number but I want to exclude the highest number

[SOLVED] Count duplicates and duplicates with suffix as one instance

Populating 1st and 2nd highest occurence of an instance per category

[SOLVED] How to apply Conditional Formatting to just ONE cell and NOT its duplicates in a range?

Excel 2007 : How to keep ONE instance of duplicates while removing duplicates AND unique values

[SOLVED] 2 rows, highest No in row 1, then highest number in row 2 relating to that column, possible duplicates

[SOLVED] Finding and Formatting the 5 highest values in a range?

Bookmarks

Bookmarks

Posting Permissions