Some help needed

[JustGolf]

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

[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

[David Billigmeier]

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.

--
Regards,
Dave
<!--


"JustGolf" wrote:

>
> I have 18 columns of numbers, I need to figure out how to
> highlight the lowest number in each column, only if it is a unique
> value... in other words, only if it doesn't match any other number in
> that column. I'd also like that number somehow paired up with it's
> row's indentifier and put somewhere in a report. Any help would be
> appreciated. JustGolf
>
>
> --
> JustGolf
> ------------------------------------------------------------------------
> JustGolf's Profile: http://www.excelforum.com/member.php...o&userid=27326
> View this thread: http://www.excelforum.com/showthread...hreadid=468204
>
>

[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 the 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 me "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 "no format" and the cell with the "4" value goes to Bold Text and Yellow 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's formula... and nothing gets highlighted. Maybe I'm still not doing it correctly, but

PLEASE try it on a sheet,

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

All help is appreciated... JustGolf

[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?...

[JustGolf]

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

[vbanovice]

See attachment, hope it helps.

Hit F9 when you want to recalculate the page.

I know this is old but anyway to get this code? I can open thezipped file worksheet but can't locate the code.

[shg]

Welcome to the forum.

There is no code in the workbook; the highlighting is done with conditional formatting.

If you have further questions, please take a few minutes to read the forum rules, and then start your own thread.

Thanks.

[royUK]

You can't see any code because there isn't any. It's Conditional Formatting