| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional format for unique values
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!! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional format for unique values
Conditional formats are limited to 3 different formats in XL2003 or less. So
you can end up with a max of 3 different colours... -- HTH... Jim Thomlinson "andiam24" wrote: 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!! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional format for unique values
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" wrote: Conditional formats are limited to 3 different formats in XL2003 or less. So you can end up with a max of 3 different colours... -- HTH... Jim Thomlinson "andiam24" wrote: 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!! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional format for unique values
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... -- HTH... Jim Thomlinson "andiam24" wrote: 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" wrote: Conditional formats are limited to 3 different formats in XL2003 or less. So you can end up with a max of 3 different colours... -- HTH... Jim Thomlinson "andiam24" wrote: 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!! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional format for unique values
3 + the default format, so 4 in total.
-- David Biddulph "Jim Thomlinson" wrote in message ... Conditional formats are limited to 3 different formats in XL2003 or less. So you can end up with a max of 3 different colours... -- HTH... Jim Thomlinson "andiam24" wrote: 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!! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional format for unique values
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). -- Biff Microsoft Excel MVP "andiam24" wrote in message ... 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" wrote: Conditional formats are limited to 3 different formats in XL2003 or less. So you can end up with a max of 3 different colours... -- HTH... Jim Thomlinson "andiam24" wrote: 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!! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional format for unique values
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. "Jim Thomlinson" wrote: 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... -- HTH... Jim Thomlinson "andiam24" wrote: 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" wrote: Conditional formats are limited to 3 different formats in XL2003 or less. So you can end up with a max of 3 different colours... -- HTH... Jim Thomlinson "andiam24" wrote: 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!! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional count of unique values | Excel Discussion (Misc queries) | |||
| Excel conditional format only if all cells contain values? | Excel Worksheet Functions | |||
| format column for unique values only | Excel Worksheet Functions | |||
| Count unique values and create list based on these values | Excel Worksheet Functions | |||
| create list of unique values from a column with repeated values? | Excel Worksheet Functions | |||
Linear Mode
