Highlighting high and low scores without duplicates

[kimm]

I'm trying to highlight high and low scores. I have duplicates in my scores and I just want to highlight the first one in the range. I'm using the MIN and MAX function to remove them from my analysis but I'd like to highlight them as well. If using the MIN or MAX function in conditional formating it highlights all of them if they are the lowest/highest score.

Example
1
1
2
3
3

I would like it to highlight the first 1 and not the second and the first 3 it finds and not the second 3. Is there a way to do this? I've tried the conditional formats of top and bottom but it highlights both numbers. Any suggestions to just highlighting the first number that applies to the formula?

[Tiresidedown]

Try the attached... requires sorting and adjacent col.

[zbor]

Similiar but don't need to be sorted and no extra column (sheet2):


firstmax(1).xls

[DonkeyOte]

Using zbor's approach of COUNTIF - and assuming you want to highlight only the MIN/MAX values:

Please Login or Register  to view this content.

Assumes A1 is the first value in the range - adjust ranges as appropriate.

[zbor]

Sorry, wasn't carefuly by reading issue :-S

I guess this is correct (didn't check DonkeyOte's)

firstmax(1).xls

Now are separate colors but you can make same...

[DonkeyOte]

zbor, I think OP is simply stating they wish to highlight MIN & MAX values in the range - where those values may be duplicated they wish only to highlight the first instance.

(ie - not the MIN/MAX of duplicated values - the Array in your file is I think an over-complication)

[zbor]

Then your formula is perfect authenticity

[kimm]

Thank you! This works great using my example!!. For some reason I can't make it work on my spreadsheet so I will try a few more things. It might be due to my linking of values from other spreadsheets. Thanks so much for your quick response!

[kimm]

Thank you so much for your help!! I thought I had it, but I'm still having issues and wondering if you could help further. The data that I'd like to highlight skips columns E12, G12, etc. I've been testing out your formula using different conditions and I think I found the problem. Wondering if there is a solution?

Using Column A, I used this formula into the Conditional formats and it worked:
=AND(OR($A1=MIN($A$1:$A$6),$A1=MAX($A$1:$A$6)),COUNTIF($A$1:$A1,$A1)=1)

Using Column B -- Since my data is in multiple areas, I wrote the formula to individually select the cells. I used this formula into the conditional formats. But for the "applies to" range I have B1:B6
=AND(OR($B1=MIN($B$1,$B$2,$B$3,$B$4,$B$5,$B$6),$B1=MAX($B$1,$B$2,$B$3,$B$4,$B$5,$B$6)),SUM(COUNTIF(B1, $B1),COUNTIF(B2, $B1),COUNTIF(B3,$B1),COUNTIF(B4,$B1),COUNTIF(B5,$B1),COUNTIF(B6,$B1))=1)

But when I spread my data apart by a row, and select it individually for the conditional formatting, it wouldn't work. Column C:
=AND(OR($C1=MIN($C$1,$C$3,$C$5,$C$7,$C$9,$C$11),$C1=MAX($C$1,$C$3,$C$5,$C$7,$C$9,$C$11)),SUM(COUNTIF(C1, $C1),COUNTIF(C3, $C1),COUNTIF(C5,$C1),COUNTIF(C7,$C1),COUNTIF(C9,$C1),COUNTIF(C11,$C1))=1)

Currently my spreadsheet has the data in Columns as shown E12, G12, etc - colored blue. I would like to highlight the high and low from the column data in a different color and can't get this formula to work. Is conditional formatting unable to work when skipping columns/rows?