Conditional Formatting lowest # if it only appears once
 

I've got several columns of numbers. I want to find the lowest number in
a column only if there are no others the same.
Example: In the first column of numbers below... nothing would be
highlighted because there are two #1's. In the second column the 1
would be highllighted because there are no others as low.
1 1
2 2
3 3
4 5
1 7

I'm currently using the below formula, however it highlights the lowest
number, even if there are more then one. Can someone please provide a
solution.

Click on A3

Format, Conditional formatting, Formula is: '=A3 = MIN($A$3:$D$3)'
{without the single quotes)
Select your formatting options, OK

Copy cell A3
Paste special, formats over the rest of the range.


--
dlvgolf
------------------------------------------------------------------------
dlvgolf's Profile: http://www.excelforum.com/member.php...fo&userid=6628
View this thread: http://www.excelforum.com/showthread...hreadid=383774

I put data in dells a1:a7 and used this formual in conditional
formatting

=AND(A1=MIN(A$1:A$10)=TRUE,COUNTIF(A$1:A$10,A1)=1)


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=383774

Perfect! Thanks.


--
dlvgolf
------------------------------------------------------------------------
dlvgolf's Profile: http://www.excelforum.com/member.php...fo&userid=6628
View this thread: http://www.excelforum.com/showthread...hreadid=383774

Now I'm probably pushing the envelope, but can this be added...
I've got a header at the top of each column of numbers. Once the
conditional formatting finds a cell to highlight... can the header cell
of that column also be highlighted? This keeps me from having to scan
over all the columns looking for the highlighted cells... I can just
scan across the header row, spot the highlighted header cell and then
scroll down to find the unique number.

Thanks again for your help.


--
dlvgolf
------------------------------------------------------------------------
dlvgolf's Profile: http://www.excelforum.com/member.php...fo&userid=6628
View this thread: http://www.excelforum.com/showthread...hreadid=383774

the only way i can figure out is two steps

1) in some row in the column (a in this case) put this array formula
(entered with control+shift+enter) - maybe in cell a2

=IF(COUNTIF(A3:A7,MIN(A3:A7))=1,1,2)

then, conditional format cell a1 as

=+A2-1=0

and if true choose format


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=383774

Hi!

=AND(A1=MIN(A$1:A$10)=TRUE,COUNTIF(A$1:A$10,A1)=1 )

You can shorten this formula a little:

=AND(A1=MIN(A$1:A$10),COUNTIF(A$1:A$10,A1)=1)

The "=TRUE" is redundant.

To apply cf to the column header:

=COUNTIF(A$1:A$10,MIN(A$1:A$10))=1

Biff

"dlvgolf" wrote in
message ...

Now I'm probably pushing the envelope, but can this be added...
I've got a header at the top of each column of numbers. Once the
conditional formatting finds a cell to highlight... can the header cell
of that column also be highlighted? This keeps me from having to scan
over all the columns looking for the highlighted cells... I can just
scan across the header row, spot the highlighted header cell and then
scroll down to find the unique number.

Thanks again for your help.


--
dlvgolf
------------------------------------------------------------------------
dlvgolf's Profile:
http://www.excelforum.com/member.php...fo&userid=6628
View this thread: http://www.excelforum.com/showthread...hreadid=383774

Once again the generous folks on the forum come through. It works like a
champ. Thanks again.

Dave


--
dlvgolf
------------------------------------------------------------------------
dlvgolf's Profile: http://www.excelforum.com/member.php...fo&userid=6628
View this thread: http://www.excelforum.com/showthread...hreadid=383774