Highlighting lowest unique number in a column of 30 numbers

[sugurray4u]

I am looking for a Conditioning formula to find the lowest Unique number in a column. I found out how to Highlight a unique number in the column, but not JUST the lowest number. I run Golf Tournaments and so I have Rows 1-18, thats for the holes, and I usually have 20 to 30 teams in columns, and i put the score down. So lets say row 1 is Hole 1, and I have 20 teams, there are thirteen 4, one 5, three 6, one 3, and two 7....I am wanting it to highlight ONLY the 3 and Right now it highlights the 3 and and the 5. Thanks

 

[shg]

Select the used cells in row 1 (e.g., B2:H2), Conditional Formatting > Manage Rules > New Rule > Use a formula > Format values where this is true,

=B2=MIN($B2:$H2)

and set a format you like.

Copy to the other rows.

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][td="bgcolor:#C0C0C0"]

F​

[/td][td="bgcolor:#C0C0C0"]

G​

[/td][td="bgcolor:#C0C0C0"]

H​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Hole​

[/td][td="bgcolor:#F3F3F3"]

Alan​

[/td][td="bgcolor:#F3F3F3"]

Barb​

[/td][td="bgcolor:#F3F3F3"]

Cain​

[/td][td="bgcolor:#F3F3F3"]

Dana​

[/td][td="bgcolor:#F3F3F3"]

Eric​

[/td][td="bgcolor:#F3F3F3"]

Fran​

[/td][td="bgcolor:#F3F3F3"]

Gary​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

1​

[/td][td]

4​

[/td][td]

4​

[/td][td]

6​

[/td][td]

7​

[/td][td="bgcolor:#99FF99"]

3​

[/td][td]

5​

[/td][td]

5​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

2​

[/td][td]

4​

[/td][td]

7​

[/td][td]

7​

[/td][td]

6​

[/td][td]

5​

[/td][td]

6​

[/td][td="bgcolor:#99FF99"]

3​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

3​

[/td][td]

5​

[/td][td="bgcolor:#99FF99"]

3​

[/td][td]

4​

[/td][td]

5​

[/td][td="bgcolor:#99FF99"]

3​

[/td][td="bgcolor:#99FF99"]

3​

[/td][td]

4​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

4​

[/td][td]

6​

[/td][td="bgcolor:#99FF99"]

3​

[/td][td]

7​

[/td][td="bgcolor:#99FF99"]

3​

[/td][td]

6​

[/td][td]

5​

[/td][td="bgcolor:#99FF99"]

3​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

5​

[/td][td="bgcolor:#99FF99"]

3​

[/td][td]

5​

[/td][td]

6​

[/td][td]

7​

[/td][td="bgcolor:#99FF99"]

3​

[/td][td]

5​

[/td][td]

4​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

6​

[/td][td="bgcolor:#99FF99"]

3​

[/td][td="bgcolor:#99FF99"]

3​

[/td][td]

6​

[/td][td]

4​

[/td][td]

6​

[/td][td]

7​

[/td][td="bgcolor:#99FF99"]

3​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

7​

[/td][td]

5​

[/td][td]

4​

[/td][td]

6​

[/td][td]

4​

[/td][td="bgcolor:#99FF99"]

3​

[/td][td]

6​

[/td][td="bgcolor:#99FF99"]

3​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

8​

[/td][td]

6​

[/td][td]

6​

[/td][td]

6​

[/td][td="bgcolor:#99FF99"]

3​

[/td][td]

5​

[/td][td]

6​

[/td][td]

6​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

9​

[/td][td]

6​

[/td][td]

5​

[/td][td="bgcolor:#99FF99"]

4​

[/td][td]

7​

[/td][td]

6​

[/td][td]

7​

[/td][td]

7​

[/td][/tr]
[/table]

 

[Swapnil Shah]

Hi

Just a point to ponder upon ...what of the lowest value have occurrence of more than one time... I.e. two or more player completed hole 1 in suppose say 1 shot.in such case if you want
1) to highlight cells pertaining to a hole for both the players by conditional formatting use following formula
=B2=min ($B2:$U2)
2) highlight only the first occurrence of the minimum shots by conditional formatting use following formula
=And ( B2=min($B2:$U2),countif($B2:B2,min($B2:$U2))=1)

Above formulas are for conditional formatting , assuming 20 teams and data starts from B2... so select cells B2 to U19 and put formula in conditional formatting

 

[Marcelo Branco]

Welcome to Mr Excel

Assuming
Headers in row 1: A1="Hole", B1="Team1", C1="Team2".....AE1="Team30"
in A2:A19 the holes' numbers: 1, 2, ....18

Select B2:AE19 being B2 the active cell (the one not shaded after the selection)

Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format
insert this formula
=AND(B2<>"",B2=MIN($B2:$AE2))
Format button and pick the format you want (Fiil--> yellow, for example)
Ok, Ok

Done!

Hope this helps

M.

 

[sugurray4u]

Select the used cells in row 1 (e.g., B2:H2), Conditional Formatting > Manage Rules > New Rule > Use a formula > Format values where this is true,

=B2=MIN($B2:$H2)

and set a format you like.

Copy to the other rows.

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #C0C0C0"][/TD]
[TD="bgcolor: #C0C0C0"]

A​

[/TD]
[TD="bgcolor: #C0C0C0"]

B​

[/TD]
[TD="bgcolor: #C0C0C0"]

C​

[/TD]
[TD="bgcolor: #C0C0C0"]

D​

[/TD]
[TD="bgcolor: #C0C0C0"]

E​

[/TD]
[TD="bgcolor: #C0C0C0"]

F​

[/TD]
[TD="bgcolor: #C0C0C0"]

G​

[/TD]
[TD="bgcolor: #C0C0C0"]

H​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

1​

[/TD]
[TD="bgcolor: #F3F3F3"]

Hole​

[/TD]
[TD="bgcolor: #F3F3F3"]

Alan​

[/TD]
[TD="bgcolor: #F3F3F3"]

Barb​

[/TD]
[TD="bgcolor: #F3F3F3"]

Cain​

[/TD]
[TD="bgcolor: #F3F3F3"]

Dana​

[/TD]
[TD="bgcolor: #F3F3F3"]

Eric​

[/TD]
[TD="bgcolor: #F3F3F3"]

Fran​

[/TD]
[TD="bgcolor: #F3F3F3"]

Gary​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

2​

[/TD]
[TD]

1​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

6​

[/TD]
[TD]

7​

[/TD]
[TD="bgcolor: #99FF99"]

3​

[/TD]
[TD]

5​

[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

3​

[/TD]
[TD]

2​

[/TD]
[TD]

4​

[/TD]
[TD]

7​

[/TD]
[TD]

7​

[/TD]
[TD]

6​

[/TD]
[TD]

5​

[/TD]
[TD]

6​

[/TD]
[TD="bgcolor: #99FF99"]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

4​

[/TD]
[TD]

3​

[/TD]
[TD]

5​

[/TD]
[TD="bgcolor: #99FF99"]

3​

[/TD]
[TD]

4​

[/TD]
[TD]

5​

[/TD]
[TD="bgcolor: #99FF99"]

3​

[/TD]
[TD="bgcolor: #99FF99"]

3​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

5​

[/TD]
[TD]

4​

[/TD]
[TD]

6​

[/TD]
[TD="bgcolor: #99FF99"]

3​

[/TD]
[TD]

7​

[/TD]
[TD="bgcolor: #99FF99"]

3​

[/TD]
[TD]

6​

[/TD]
[TD]

5​

[/TD]
[TD="bgcolor: #99FF99"]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

6​

[/TD]
[TD]

5​

[/TD]
[TD="bgcolor: #99FF99"]

3​

[/TD]
[TD]

5​

[/TD]
[TD]

6​

[/TD]
[TD]

7​

[/TD]
[TD="bgcolor: #99FF99"]

3​

[/TD]
[TD]

5​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

7​

[/TD]
[TD]

6​

[/TD]
[TD="bgcolor: #99FF99"]

3​

[/TD]
[TD="bgcolor: #99FF99"]

3​

[/TD]
[TD]

6​

[/TD]
[TD]

4​

[/TD]
[TD]

6​

[/TD]
[TD]

7​

[/TD]
[TD="bgcolor: #99FF99"]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

8​

[/TD]
[TD]

7​

[/TD]
[TD]

5​

[/TD]
[TD]

4​

[/TD]
[TD]

6​

[/TD]
[TD]

4​

[/TD]
[TD="bgcolor: #99FF99"]

3​

[/TD]
[TD]

6​

[/TD]
[TD="bgcolor: #99FF99"]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

9​

[/TD]
[TD]

8​

[/TD]
[TD]

6​

[/TD]
[TD]

6​

[/TD]
[TD]

6​

[/TD]
[TD="bgcolor: #99FF99"]

3​

[/TD]
[TD]

5​

[/TD]
[TD]

6​

[/TD]
[TD]

6​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

10​

[/TD]
[TD]

9​

[/TD]
[TD]

6​

[/TD]
[TD]

5​

[/TD]
[TD="bgcolor: #99FF99"]

4​

[/TD]
[TD]

7​

[/TD]
[TD]

6​

[/TD]
[TD]

7​

[/TD]
[TD]

7​

[/TD]
[/TR]
</tbody>[/TABLE]

Ok, thank you so much for the reply, however, I didn't explain it very good, sorry. Using your example going across should read holes 1-18, and going down should be the team names. Lets say B1 is hole 2, and the numbers going down are 4,5,3,3,4,4,4,5,6,2. There are 2 numbers in that string that are unique, the #2 and the #6 . I need a conditioning formula that would highlight JUST the 2(lowest unique#). Right now, the way I have it set up, both the 6 and the 2 are being highlighted as each of them are unique. I hope that explains it better.

 

[Swapnil Shah]

hi,

Try below formula in your conditional formatting ( Assuming Holes are in Columns B:S and Team scores are in Rows starting from row 2)

=B2=MIN(IF(COUNTIF($B2:$S2,$B2:$S2)=1,$B2:$S2,""))

Thanks
Swapnil Shah

 

[shg]

I need a conditioning formula that would highlight JUST the 2(lowest unique#).

What if there were no number that appeared only once?

EDIT: Is this for skins? If so, you would want to highlight the number that both appears only once and is the minimum.

 

[Chippy68]

So glad I found this as I have a similar question, but different. I run some very large golf pools and have the conditional formatting to take the lowest 7 scores of 12 available. The entrants like to see those 7 highlighted, which I can do, BUT, if the 7th score has multiple players with that score it highlights all of them and I only want one, or two if scores 6,7,8 are the same number.

Player A -7
Player B -6
Player C -5
Player D -4
Player E -3
Player F -2
Player G -1
Player H -1
Player I -1

In this scenario the first 6 are easy but player G, H and I are all the 7th lowest score and I only want to highlight one of their cells. I don't care which one either. If Player F happened to be at -1 then I would need to highlight 2 of the last 4, again, doesn't matter which two.

What is then the most efficient way to take that formula and enter it into each entrants cell for their total score?