Conditional Formatting & Finding Duplicates

[mluetkem]

I have a 12 x 12 matrix of names. Columns represent INNINGS of a baseball game and rows represent their defensive POSITIONS played that inning. So, for each game I don't want to repeat names in an inning (column) but over a game I can repeat someone at a position (row) no more then twice. How can I use conditional formatting to help me identify when I have violated my constraints? When I repeat someone in a column I would like the person(s) name to show up red for the number of times in the column. For the rows, when I have repeated a name more then twice over 12 innings I would like the cell fill color to be light yellow.

Thanks,

Mike

[ConneXionLost]

Hi mluetkem,

Assuming the top left name in your matrix is in cell B2, select range B2:M13 then go to Conditional formatting.

Condition 1 (Red)

Formula is =COUNTIF(B$2:B$13,B2)>1

Condition 2 (Yellow)

Formula is =COUNTIF($B2:$M2,B2)>2

Cheers,

[darkyam]

If you're in 2003 or earlier, make sure the yellow rule is the top one. Pre-2007, Excel would find a rule that evaluates to true and then stop, so if the red rule is the top one, you would have no yellows. Perhaps a better way is to modify the first formula to be =2 instead of >1.

[ConneXionLost]

Darkyam,

I'm afraid I don't understand your reasoning. The red formula is counting through the column while the yellow formula is counting through the row. Either way, the result will only effect the individual cell at the intersection. Further, if red overrides yellow, there's a better chance to see other yellow cells (>2) than there is other red cells. Finally, it'd be a greater error to list one person in two positions than to let them play more than two games.

Please explain.

Cheers,

[darkyam]

The explanation is simple: I'm a dunce. I didn't see that one was doing rows and the other columns. It's what I get for not paying attention.