Highlight only one occurrence of value in table

mbeardsl

New Member
Joined
Nov 28, 2016
Messages
12
I have a table of placings in a series of races and the associated points applicable to those placings. E.g.

Name | Place1 | Points1 | Place2 | Points2 | etc

We drop the lowest points score (best 4 of 5 races), and I'd like to highlight that points amount. But I only want to highlight it the first time it occurs. In other words, if someone earns 35,35,35,31,31 points I only want to highlight one of the 31 values.

Is this possible? I've been playing with MATCH() and conditional formatting but just can't figure it out. I feel like the data being organized in columns and separated by the placing columns is making it difficult.

Thanks for your help in advance.

MB
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
This is the basis of the formula you would use in Conditional Formatting...
=AND(I17=SMALL($I$17:$M$17,1),COUNTIF($I$17:I17,SMALL($I$17:$M$17,1))=1)
where I17 etc is the data range

See if you can adapt this, if not, let me know
 
Upvote 0

mbeardsl

New Member
Joined
Nov 28, 2016
Messages
12
Thank you for the quick reply, that works for the first row with dummy data but then fails to work properly as I paint the format down the table. What am I doing wrong? I follow how the formula works on it's own, but seem to get confused once I drop into the conditional formatting box.

Also, my data isn't in consecutive columns. In other words I have:

1,35,1,35,1,35,2,31,2,31 where the 1 and 2 are the placings and the 35 and 31 are the corresponding points using VLOOKUP. Impossible to apply the same formula you have above to the data laid out this way?
 
Upvote 0

mbeardsl

New Member
Joined
Nov 28, 2016
Messages
12
Ok, this works when I copy the format down the table with dummy data. Sorry, I should have looked at it more before posting so quickly above.

=AND(U12=SMALL($U12:$Y12,1),COUNTIF($U12:U12,SMALL($U12:$Y12,1))=1)

Still having trouble making it work with the data the way I have it though.
 
Upvote 0

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
If you are using that for subsequent rows, adjust the absoluting...
=AND(I17=SMALL($I17:$M17,1),COUNTIF($I17:I17,SMALL($I17:$M17,1))=1)

Catering for the placing could be a challenge, but see if this works for you. When faced with a tricky CF problem, I often construct the formula IN the worksheet, where is it is easier/quicker to edit and test. Then I transfer the formula to CF.

I​
J​
K​
L​
M​
N​
O​
P​
Q​
R​
21​
1​
35​
1​
35​
1​
35​
2​
31​
2​
31​
22​
FALSE​
FALSE​
FALSE​
FALSE​
FALSE​
FALSE​
FALSE​
TRUE​
FALSE​
FALSE​
I22=AND(I21=SMALL($I21:$R21,(COUNT($I21:$R21)/2)+1),COUNTIF($I21:I21,SMALL($I21:$R21,(COUNT($I21:$R21)/2)+1))=1)
copied across.

When you use in in the CF function, just adjust the refs as needed
 
Upvote 0

mbeardsl

New Member
Joined
Nov 28, 2016
Messages
12
Thank you for the help. I'm not sure exactly how it works yet, but working through it. At the phase where I've been looking at this for too long tonight I think.

It works wonderfully so far but running into an issue. At a point in the standings the placing is more than the points you earn (14th place earns 12 points) so it begins to highlight the placing instead of the points as it's looking for the 6th smallest number. That gets compounded if there is a placing even worse than that as now it's still stuck on the 6th instead of the 7th or 8th smallest etc.

I may have to come at this in a different way. Cell reference the scores off to the right and then conditionally format the original cells based on some criteria in the new array. More of an IF() based on the same formula above perhaps. Like if Z12="TRUE" then cell is blue, otherwise no formatting but then I'd have to conditionally format each cell that way as it would reference a specific cell?

Thanks for the tip on working my way into CF from the straight formula.
 
Upvote 0

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
Yes, the problem with having standings that increase to beyond the points earned, is, as you found, the standings tend to get viewed as points.

A possible way around that would be to use an ARRAY formula that will look at your headings and only consider columns with, say, Points as a heading. Then you could use this simpler array formula in CF...
B​
C​
D​
E​
F​
G​
H​
I​
J​
K​
1​
Standing​
Points​
Standing​
Points​
Standing​
Points​
Standing​
Points​
Standing​
Points​
2​
1​
35​
1​
35​
1​
35​
2​
31​
2​
31​
3​
FALSE​
FALSE​
FALSE​
FALSE​
FALSE​
FALSE​
FALSE​
TRUE​
FALSE​
FALSE​
=AND(COUNTIF($B2:B2,SMALL(IF($B$1:$K$1="Points",$B2:$K2),1))=1,B2=SMALL(IF($B$1:$K$1="Points",$B2:$K2),1))
Note: to use that as a formula IN the worksheet, you would need to enter it using CTRL SHIFT ENTER, not just enter, but CF does not need it entered that way
 
Upvote 0

mbeardsl

New Member
Joined
Nov 28, 2016
Messages
12
Wow, learning a lot tonight, thank you for being patient. Every time I think I'm pretty sharp with Excel I come up on something like this and realize how much I don't know again.

That solution works very well with 3 exceptions.

At 13th place you earn 13 points, and it's defaulting to highlight Place instead of Points.

When one racer earns the same points as they place (but in different races) it is defaulting to Place instead of Points as well. So 11th place gets 15pts, then next week they get 15th place which earns 11 points. It's highlighting the 11th place again, instead of the 11 points. Only happens in that order as it's searching across from Left to Right. If those two races were switched it acts correctly.

The easy fix I found is to simply add .001 to the points table at every level so there can't be a tie between place and points.

The third scenario is when the racer doesn't place at all (didn't show, had to quit etc). The empty placing highlights both the points for that race (0) and the next empty placing cell regardless of previous or later results. Can't figure out a quick fix, but it's not the end of the world.
 
Upvote 0

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
Untested, but try this...
=AND(b$1="Points",B2<>"",COUNTIF($B2:B2,SMALL(IF($B$1:$K$1="Points",$B2:$K2),1))=1,B2=SMALL(IF($B$1:$K$1="Points",$B2:$K2),1))
 
Last edited:
Upvote 0

Forum statistics

Threads
1,191,684
Messages
5,987,993
Members
440,124
Latest member
dippy_egg

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top