Conditional Format - Formula

[Ash]

I have three columns of data for which I need to identify if there any are
"repeats". If the data is the same for all three columns, I need to
highlight the third column cell in one color, but if only the second two
columns are the same, I need to highlight the third column cell a different
color.

Problem 1) I've tried using the formula in conditional formatting - but to
no avail.

The formula I used: =p2=o2=n2 If this is true, I said to format a
color. First when I copy the formula down, it does fill down for each cell.
When I click manage rules, the formula shows as =$p$2=$o$2=$n$2 I tried
copy/paste special, and this worked for from one cell to one cell, but when I
copy/paste special for all rows of data, it again converted the formula to
have dollar signs.

Problem 2) I then went to a row where I knew the formula to be true and
applied to the cf formula to just that cell. I did not get any highlighting
of the cell. I confirmed that there were no dollar signs and that the
numberics matched the correct row number.

Problem 3) Once I solve the problem above, I then need to add a second
format that if all three are not the same, then tell me if two are the same.
I assume that if I check the "Stop if True" box next to the cf formula box,
then the second cf formula will only "kick in" if the first doesn't work. Is
this an accurate assumption?

Any and all help is appreciated!!
Thanks,
Ashley

 

[Sean Timmons]

let's say the columns are A B and C

in c2 format wil be

Formula is:

=AND(A2=C2,B2=C2)

2nd calc will be

=B2=C2

If the first formula works, the conditional format will never attempt the
2nd formula.

Leave the $'s out of it since copying the formatting down the column would
need the row numbers to change.

 

[Ash]

Hi Sean, Thank you for responding.

Okay I've used the formulas you provided.

1) I still have the copy paste error, in that the cf formula is not
"filling down" For example, I copied row 2 and paste specialed, formats only
on rows 3 - 708. Row 708 has the instance where all 3 columns are the same.
I looked at the formula in row 708 and it shows =AND(A3=C3,B3=C3)
2) Since I was still getting the error, I decided to try the formula on row
708 by entering the formula in directly. It still didn't work.

Any ideas where I'm going wrong?
I truly appreciate your help!

 

[Sean Timmons]

Not sure where this went wrong.

The formulas in row 708 should be

=AND(A708=C708,B708=C708)

and

=B708=C708

And you are changing the drop down under condition 1 and condition 2 to say
formula is.

and you don't have a $ in front of any of the numbers?

and you have changed the format to whatever color you want to show?

 

[Ash]

I did change the drop down to say Formual Is,
There was not a dollar sign in front of the numbers, and
I set the color to be something other than white.

Since I seem to be impaired , I've found an alternative solution:

I added an IF statement in another column to tell me if all three are the
same, then ## and if two are the same then YY. I used the conditional format
to read the new column and highlight based on ## or YY.

As a note, I'm wondering if the problem might be with the data. When I do
=a2=b2=c2, I get false. But if look at them in pairs, =a2=b2 , then b2=c2,
then a2=c2, I get true for all three. I found this out b/c I couldn't use
all three in the logic of the IF function.

Thank you again for trying to help!

 

[Sean Timmons]

yeah, Excel doesn't like = = =.

Need to use =AND(A=B,A=C) or something similar.

Guess you and conditional formatting need to go out back and settle your
differences.

 

[Ash]

Ha! As tough a gal as I am, I still think conditional formatting would give
me a swift kick! So, no way, uh-uh, no chance in me taking conditional
formatting out back... I'll stay right here and admit to my indadequacies!

Thanks again!

 

[Sean Timmons]

Wow, I just thought of something.. If you go to Tools - Options -
Calculation, is it on manual? If so, change to automatic...

 

[Ash]

In Excel 2007, so went to Excel options > Formulas > Calculation Options >
Workbook Calculation, Automatic is selected.

 

[Sean Timmons]

Ah well. That was my last hope.

In Excel 2007, so went to Excel options > Formulas > Calculation Options >
Workbook Calculation, Automatic is selected.