Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting Error | Excel Worksheet Functions | |||
difficulty with conditional formatting | Excel Discussion (Misc queries) | |||
conditional formatting question | Excel Discussion (Misc queries) | |||
Determine cells that drive conditional formatting? | Excel Discussion (Misc queries) | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) |