How do I conditional format for data that's repeated

[Putty]

I would like to highlight data using Conditional Formatting from a list of
numbers but only want to highlight the 2 smallest numbers.

E.g. Data list = 6,7,8,1,9,2,4,2

The result should highlight the number 1 BUT ONLY THE FIRST 2 - I don't want
both 2s highlighted.

Thanks,

 

[Frank Kabel]

Hi
if your data is in A1:A10 try the following:
- select this range
- goto the conditional format dialog
- enter the following formula:
=RANK(A1,$A$1:$A$10,TRUE)+COUNTIF($A$1:$A1,A1)-1<=2
- choose your color

 

[Putty]

This still highlights the repeated number (2 in my example). HELP!

 

[Frank Kabel]

Hi
I tested it for your data example and it worked for me:
- in which cells is your data exactly
- what is the exact formula you have entered

 

[Putty]

My data is in B3:R3 and I used:
=RANK(B3,$B$3:$R$3,TRUE)+COUNTIF($B$3:$B3,B3)-1<=2

and I will copy this formula to many rows below it.

Many thanks ...
Jeff

 

[Putty]

Woops I meant ...

=RANK(B3,$B3:$R3,TRUE)+COUNTIF($B3:$R3,B3)-1<=2

and my data is:
7,10,9,7,7,9,6,4,6,2,1,3,2,6,7,8,7

The 1 and the first 2 should get highlighted.

Thanks again.

 

[Putty]

To clarify ... it doesn't have to be the FIRST 2 ... but rather either 2.

 

[Frank Kabel]

Hi
use:
=RANK(B3,$B$3:$R$3,TRUE)+COUNTIF($B$3:B$3,B3)-1<=2

 

[Putty]

Frank ... Thank you so much! You the man!

Jeff

Hi
use:
=RANK(B3,$B$3:$R$3,TRUE)+COUNTIF($B$3:B$3,B3)-1<=2