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!

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!

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!

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!

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!

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!