Conditional Formatting

[Neil Greenough]

I have three columns in a spreadsheet - name, address, telephone and reason.

Now in each column, I want to format the columns so that if the same, for
example, address appears twice within any row in the column, the cell colour
is changed to green. If it appears three time, it changes to amber and if it
appears four times it changes to red.

Any suggestions?

 

[JE McGimpsey]

One way:

Assume addresses in B2:Bxxx. With B2 the active cell:

CF1: Formula is =COUNTIF(B2,B:B)=4
Format1: <patterns>/<red>

CF2: Formula is =COUNTIF(B2,B:B)=3
Format2: <patterns>/<yellow>

CF3: Formula is =COUNTIF(B2,B:B)=2
Format3: <patterns>/<green>

 

[Dave Peterson]

Select the range (whole column???), I used column A.

Then with A1 the active cell
format|Conditional formatting
formula is:

=countif(a:a,a1)>1
give it a nice format.

====
Personally, I'd use a helper column to the right of each and put this formula in
it:
=countif(a:a,a1)
and drag down.

Then I could apply data|filter|autofilter to show the values greater than 1.

Formatting is nice, but about all you can do is look at it.

You may want to read some of Chip Pearson's techniques for dealing with
duplicates:
http://www.cpearson.com/excel/duplicat.htm

 

[Dave Peterson]

Oops. Never mind.

Select the range (whole column???), I used column A.

Then with A1 the active cell
format|Conditional formatting
formula is:

=countif(a:a,a1)>1
give it a nice format.

====
Personally, I'd use a helper column to the right of each and put this formula in
it:
=countif(a:a,a1)
and drag down.

Then I could apply data|filter|autofilter to show the values greater than 1.

Formatting is nice, but about all you can do is look at it.

You may want to read some of Chip Pearson's techniques for dealing with
duplicates:
http://www.cpearson.com/excel/duplicat.htm