Some help needed

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JustGolf - 16 Sep 2005 14:53 GMT

:confused: 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

Reply to this Message

Jon Quixley - 16 Sep 2005 15:15 GMT

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

Signature

Jon Quixley

Reply to this Message

David Billigmeier - 16 Sep 2005 15:34 GMT

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.

Signature

Regards,
Dave
<!--

> :confused: 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

Reply to this Message

JustGolf - 19 Sep 2005 17:45 GMT

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

Signature

JustGolf

Reply to this Message

BenjieLop - 19 Sep 2005 18:12 GMT

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

--
BenjieLo

Reply to this Message

JustGolf - 20 Sep 2005 17:10 GMT

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

Reply to this Message

BenjieLop - 20 Sep 2005 17:22 GMT

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

Regards.

Signature

BenjieLop

Reply to this Message

JustGolf - 20 Sep 2005 22:37 GMT

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

Reply to this Message

Morrigan - 20 Sep 2005 23:58 GMT

See attachment, hope it helps.

Hit F9 when you want to recalculate the page.

JustGolf Wrote:

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