Conditional format for unique values

[andiam24]

Hello,

I would like to set a conditional format to color each unique value a
different color. It will also be applied to multiple occurences of the value.
The problem is that the values are dynamic and may not always be the same
number of occurences, in the same cell, etc. I have used the formula
IF(ISNUMBER(COUNTIF($F$3:F3,F3)),ROW(),"")) to try to identify the occurences
but creating a conditional format has proven out of my league! Please help!
And thanks!!

 

[Jim Thomlinson]

Conditional formats are limited to 3 different formats in XL2003 or less. So
you can end up with a max of 3 different colours...

 

[andiam24]

Right, and that's ok. My problem is identifying the value and multiple
occurences of the same value.

i.e. If the following IDs were in column A, I would like to shade all of the
matching occurences the same color (e.g. QC0042- green, QC0061- blue, QC0065-
red, etc.)

Day1 Day2 Day 3
Row Col A Col A Col A
1 QC0042 QC0054 QC0055
2 QC0061 QC0054 QC0055
3 QC0061 QC0054 QC0058
4 QC0061 QC0069 QC0058
5 QC0065 QC0069 QC0058
6 QC0065 QC0072 QC0058
7 QC0065 QC0072 QC0079

The day (Day 1, Day2, etc) denotes entirely new entries - where you can omit
the other columns of values- which is why they are all labeled as Col A, and
not a spreadsheet as it appears. Notice, the position is the same for the
different days so I was trying to kinda focus on that - making the position
in relation to the preceding number the color changing element. However, I
can't work it. Any assistance you can provide would be awesome.

 

[Jim Thomlinson]

Yes but in your example you have used up your 3 colours. What happens for the
next ID? You could write a macro but you will still be limited to 56 colours
as that is the max number of colours in XL...

 

[David Biddulph]

3 + the default format, so 4 in total.

 

[T. Valko]

You can color band the groups:

1 QC0042 = green
2 QC0061 = default
3 QC0061 = default
4 QC0061 = default
5 QC0065 = green
6 QC0065 = green
7 QC0065 = green
8 QC0077 = default
9 QC0077 = default
etc
etc

However, the formula needed to do this is very calculation intensive so if
you have 1000's of rows of data it'll slow things to a crawl (literally).

 

[andiam24]

The way the spreadsheet is designed, the probability of more than 4 IDs being
used at any given time is slim, so the next time IDs are input, they can use
the same four colors because they will replace the previous IDs. I'm just not
sure how to word the conditional format to identify which values are
repeating and which are unique and color appropriately.