Highlight nth duplicate in a list

t0azt

Board Regular
Joined
Aug 1, 2012
Messages
134
Hello,
I have a table from A1 to J76. Column E is for color and I can have a maximum of 15 of any given color. Is there a way to highlight the row of the 16th or more repetition of a color? Thanks!
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,314
I think this might be a good place to start:

=COUNTIF($E$2:$E2,E2)

The range $E$2:$E2 will get larger as it is filled down. So the resulting number will be the number of colours matching the one in that row above the current location.

You could then highlight the cell by using Conditional Formatting using that formula as a basis:
=COUNTIF($E$2:$E2,E2)>15
 

Watch MrExcel Video

Forum statistics

Threads
1,108,923
Messages
5,525,651
Members
409,658
Latest member
Yardcell

This Week's Hot Topics

Top