Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Color Code Duplicate items.
Please help! I have a spread sheet that is massive. I have one column with a
million numbers in it. I need to color code it each time it shows up. The first instance should be white, the second should be yellow, and the third time it shows up it should be red. Is this possible? I have researched all morning long. Please help! Thank you so much! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Color Code Duplicate items.
Assuming you have your numbers in Col A in rows 1-1000, then enter the
following in B1; =COUNTIF($A$1:$A$1000,A1) - COUNTIF(A1:$A$1000,A1) + 1 and copy down to B1000. If you hae more than 100 then change 1000 to the required number in $A$1000 This will put 1 against the furst occurence, 2 against second on so on. If you don't care about the order then COUNTIF(A1:$A$1000,A1) will put 3 against fiirst, 2 agains second and 1 against third (assuming 3 occurences) Now you can color code your rows based on the number in Col B "Amy" wrote: Please help! I have a spread sheet that is massive. I have one column with a million numbers in it. I need to color code it each time it shows up. The first instance should be white, the second should be yellow, and the third time it shows up it should be red. Is this possible? I have researched all morning long. Please help! Thank you so much! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Color Code Duplicate items.
Format/ Conditional Formatting/ Formula Is:
=COUNTIF(A$1:A1,A1)=2 select your yellow colour. Add, and similarly for the red for 3. -- David Biddulph "Amy" wrote in message ... Please help! I have a spread sheet that is massive. I have one column with a million numbers in it. I need to color code it each time it shows up. The first instance should be white, the second should be yellow, and the third time it shows up it should be red. Is this possible? I have researched all morning long. Please help! Thank you so much! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Color Code Duplicate items.
This can be done with conditional formatting. Assuming we are dealing with
column A select cell A2. Select Format Conditional Formatting... Change Cell value is to Formula is and add this formula. =COUNTIF($A$1:$A1, A2)=1 Select format and Choose the appropriate fill colour as your pattern Add a second criteria. Change Cell value is to Formula is and add this formula. =COUNTIF($A$1:$A2, A2)1 Select format and Choose the appropriate fill colour as your pattern. Now just copy cell A2 and paste special - formats to the entire A column. -- HTH... Jim Thomlinson "Amy" wrote: Please help! I have a spread sheet that is massive. I have one column with a million numbers in it. I need to color code it each time it shows up. The first instance should be white, the second should be yellow, and the third time it shows up it should be red. Is this possible? I have researched all morning long. Please help! Thank you so much! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Color Code Duplicate items.
So far...It's working!! Thank you all for your help!! I do have another
question....what is the formula to pull out if it showed up a third time? It's one column of B I have a million account numbers. I need to show where it first showed up, then i need to show if it showed up for a 2nd time, and last a third time. I just need the code to show if they showed up for a 3rd time. Thank you all SO MUCH. My day is getting better! "Jim Thomlinson" wrote: This can be done with conditional formatting. Assuming we are dealing with column A select cell A2. Select Format Conditional Formatting... Change Cell value is to Formula is and add this formula. =COUNTIF($A$1:$A1, A2)=1 Select format and Choose the appropriate fill colour as your pattern Add a second criteria. Change Cell value is to Formula is and add this formula. =COUNTIF($A$1:$A2, A2)1 Select format and Choose the appropriate fill colour as your pattern. Now just copy cell A2 and paste special - formats to the entire A column. -- HTH... Jim Thomlinson "Amy" wrote: Please help! I have a spread sheet that is massive. I have one column with a million numbers in it. I need to color code it each time it shows up. The first instance should be white, the second should be yellow, and the third time it shows up it should be red. Is this possible? I have researched all morning long. Please help! Thank you so much! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Color Code Duplicate items.
Nevermind! I got it!!!! Thank you so much!!!!! My report is finally done!
"Amy" wrote: So far...It's working!! Thank you all for your help!! I do have another question....what is the formula to pull out if it showed up a third time? It's one column of B I have a million account numbers. I need to show where it first showed up, then i need to show if it showed up for a 2nd time, and last a third time. I just need the code to show if they showed up for a 3rd time. Thank you all SO MUCH. My day is getting better! "Jim Thomlinson" wrote: This can be done with conditional formatting. Assuming we are dealing with column A select cell A2. Select Format Conditional Formatting... Change Cell value is to Formula is and add this formula. =COUNTIF($A$1:$A1, A2)=1 Select format and Choose the appropriate fill colour as your pattern Add a second criteria. Change Cell value is to Formula is and add this formula. =COUNTIF($A$1:$A2, A2)1 Select format and Choose the appropriate fill colour as your pattern. Now just copy cell A2 and paste special - formats to the entire A column. -- HTH... Jim Thomlinson "Amy" wrote: Please help! I have a spread sheet that is massive. I have one column with a million numbers in it. I need to color code it each time it shows up. The first instance should be white, the second should be yellow, and the third time it shows up it should be red. Is this possible? I have researched all morning long. Please help! Thank you so much! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Duplicate items | Excel Discussion (Misc queries) | |||
Checking for duplicate items in list | Excel Worksheet Functions | |||
How to get rid off duplicate items? | Excel Discussion (Misc queries) | |||
How do I do a count sum that ignores duplicate items | Excel Worksheet Functions | |||
How do I color code items in a drop down list? | Excel Discussion (Misc queries) |