Ignore Duplicate Values In Conditional Formatting

[mightyeskimo]

I'm using Excel 2007. I'd like to know if there is a way to tell Excel to ignore duplicate values when applying a conditional format.

I've posted an example workbook.

[zbor]

Here, try this in CF:

=COUNTIF($U$5:$U6, $U6)=1

[NBVC]

It might be easier with the aid of a helper column..

Say in T5 you enter:

=IF(COUNTIF(S$5:S5,S5)=1,S5,"")

copied down,

Then the conditional format formula for S5:S23 would be:

=AND($T5<>"",S5>=LARGE($T$5:$T$23,10))

[mightyeskimo]

Can you elaborate on your reply zbor? I don't understand where you're going.

I really need a way to do this without a helper column NBVC because I would then have to create hundreds of helper columns for each column I want to format.

Thanks very much for your help!

[NBVC]

I think zbor's suggestion assumes the top 10 values are the actual top 10 items in your list... if that is true, then just select the range and apply the conditional format he supplied, and if you can sort the data this way, it might very well work for you...

I have a funny feeling, it isn't that straightforward, and than you can have duplicates within the first 10 values, and also that the numbers are always so sequential.. that is why I supplied my solution.. but I am having some difficulty getting it in a single formula... I can't account for 1 of the duplicates being a valid entry into an array... this may require VBA (which would be beyond me). Maybe someone knows of a trick that I can't yet figure out....

[WHER]

My take on this. I tweaked the data a little to make them less "straithforward".

[NBVC]

Well, there you go...

my excuse... it is late in the day and have been racking my brain in some crystal reports forms all day

Good job, WHER...

[mightyeskimo]

Thanks WHER! That's awesome! Would I be correct to assume that if I change LARGE to SMALL that the bottom 10 values would be formatted? Thanks NBVC and zbor too!

[WHER]

A quick test seems to confirm your assumption, but don't forget to also change the ">=" to "<="

[mightyeskimo]

Can I somehow use the fill handle to drag these rules to adjacent columns of the same size?

[WHER]

Not the fill handle, but the "format brush" if that still exists in Excel 2007?
But first a small modification to the formula is needed: select U5, then go to Format >> Conditional Format >> instead of "countif($U$5:U5,U5)" modify to "countif(U$5:U5,U5)"

[mightyeskimo]

I bumped this thread to ask two follow up questions. The number of rows that my data occupies will always be subject to change. As a result, I'd like to avoid the repetative task of dragging up or down the range to make sure the conditional formatting applies to the correct range of cells. I am able to estimate, with a very good amount of accuracy, the number of rows the data range will never exceed. What I'd like to do is have Excel ignore cells that are empty while performing the format.

My second question is much like the first. Some cells in the range, due to the formulas therein, will result in #N/A values. Is it possible to tell Excel to ignore these values as well?

[daddylonglegs]

Can you post a representative sample (doesn't have to be full size, just 20 or 30 values) showing numbers in the order you would expect them, with spaces and #N/As.

Also explain which values should be formatted - thanks

[mightyeskimo]

Updated Attachment as example.