Some help needed

[JustGolf]

I have 18 columns of numbers, I need to figure out how t
highlight the lowest number in each column, only if it is a uniqu
value... in other words, only if it doesn't match any other number i
that column. I'd also like that number somehow paired up with it'
row's indentifier and put somewhere in a report. Any help would b
appreciated. JustGol

 

[Jon Quixley]

I can help with the first part of this, but identifying the row is
beyond me.

Here's the first part (and its quite fun)
You're going to be looking for the minimum number in a column of
numbers - insert at the top or bottom (somewhere out of the way) the
following formula (I have assumed that your numbers start on row 2 and
that this formula goes in row 1

=MIN(a2:a100)

Next highlight all the cells between A2 and A100 (in this example) and
go to FORMAT / CONDITIONAL FORMATTING. Set the dialog box to look for
Cell Value EQUAL TO and in the final box put =$a$1
Next set the shading to a nice bright colour as a background
This will make all the cells from A2 to A100 look at the minimum that
the formula will find amongst the numbers between A2 and A100. Once it
finds that number, its mate in the column will illuminate to the colour
you set

Jon

 

[Guest]

This formula will highlight the lowest number in a column only if it is
unique. Enter it in the conditional formatting screen in the first cell of
your range and drag down: Format->Conditional Formatting....

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

I assumed your row range went from 1 to 20, change to fit your data.

 

[JustGolf]

Jon, thanks for the suggestion, I tried the "=MIN(a2:a100)" idea... I
already had that in my spreadsheet, I can find the smallest number in
each column but I need to only identify that number if it's a unique
value in that column...

David, I can seem to get your suggestion to work at all, can you be a
little more specific?


Thanks...

 

[BenjieLop]

Here is how David's suggestion will work.

1. Highlight your range (A1:A100)

2. Go to Format/Conditional Formatting

3. In the "Condition 1" box on the left, choose "Formula Is"

4. On the next box to the right, enter the formula suggested by David

5. Click on "Format"

6. Choose your highlighting color in the "Color" box

7. Click OK

8. Click OK

... you are now set.

Regards

 

[JustGolf]

Instead of opening my work spreadsheet and possibly screwing up th
data,
I started brand new sheet.

In column a1 thru a20 I typed "5"... in each cell.

in cell A-21 I have added the formula =MIN(a1:a20)... and it gives m
"5" in the special format I set up, bold text with yellow background.

when I change one of the cells to a "4", all the other cells go to "n
format" and the cell with the "4" value goes to Bold Text and Yello
highlight.
when I add another 4, they both are highlighted. ... that parts fine..
BUT

I only want it highlighted when "1" cell is low and unique.

I followed BenjiLop's step by step instructions and tried David'
formula... and nothing gets highlighted. Maybe I'm still not doing i
correctly, but

*PLEASE try it on a sheet*,

maybe there is a character missing somewhere or one that is inserte
incorrectly... But nothing works yet.

All help is appreciated... JustGol

 

[BenjieLop]

I do not know what else to say ... the formula worked when I tested it.

Regards.

 

[JustGolf]

Anybody else have any suggestions? or want to help?..

 

[Morrigan]

See attachment, hope it helps.

Hit F9 when you want to recalculate the page.

Anybody else have any suggestions? or want to help?...

+-------------------------------------------------------------------+
|Filename: Sample.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3844 |
+-------------------------------------------------------------------+

 

[JustGolf]

GREAT! That's exactly what I was looking for.... Thanks!