Help! Conditional formatting or "if"

[ttop1010]

Please help!

i have a spreadsheet i put together for a Manager (see below) Each column (H,M,R) represents locations on a job we need to close out.

I used "CONDITIONAL FORMATTING" > HIGHLIGHT > DUPLICATES"..... showed to my manager, he loved it and wanted to know if we could highlight the cells in the 3rd Column ("R") if they appear in all 3 Columns. When he asked me i thought of course! But when I went to do it, I realized "Duplicates" wouldn't work. Spoke with a friend of my told me "IF" function should work but did not have time to get into it.

this may be easy but any help would be a life saver!!!!!

"H" "M" "R"[TABLE="width: 343"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]01-1-1-Q[/TD]
[TD]01-143-2-Q[/TD]
[TD]1-37-1-Q[/TD]
[/TR]
[TR]
[TD]01-1-2-A[/TD]
[TD]01-143-4-A[/TD]
[TD]01-11-1-Q[/TD]
[/TR]
[TR]
[TD]01-174-2-Q[/TD]
[TD]01-165-2-Q[/TD]
[TD]1-54-1-Q[/TD]
[/TR]
[TR]
[TD]01-44-4-Q[/TD]
[TD]01-190-4-Q[/TD]
[TD]4-31-1-L[/TD]
[/TR]
[TR]
[TD]01-49-0-Q[/TD]
[TD]01-195-4-Q[/TD]
[TD]03-92-0-C[/TD]
[/TR]
[TR]
[TD]01-49-3-Q[/TD]
[TD]01-200-2-Q[/TD]
[TD]03-69-10-Q[/TD]
[/TR]
[TR]
[TD]01-5-1-Q[/TD]
[TD]01-200-4-Q[/TD]
[TD]03-96-1-L[/TD]
[/TR]
[TR]
[TD]01-64-1-Q[/TD]
[TD]01-235-01-A[/TD]
[TD]2-82-2-Q[/TD]
[/TR]
[TR]
[TD]01-64-6-Q[/TD]
[TD]01-235-0-Q[/TD]
[TD]01-64-1-Q[/TD]
[/TR]
[TR]
[TD]01-74-2-Q[/TD]
[TD]01-238-2-Q[/TD]
[TD]01-64-6-Q[/TD]
[/TR]
[TR]
[TD]01-84-4-Q[/TD]
[TD]01-241-0-Q[/TD]
[TD]1-69-1-Q[/TD]
[/TR]
[TR]
[TD]01-9-0-Q[/TD]
[TD]01-245-7-Q[/TD]
[TD]1-92-2-Q[/TD]
[/TR]
[TR]
[TD]01-9-2-Q[/TD]
[TD]01-44-4-Q[/TD]
[TD]2-44-0-Q[/TD]
[/TR]
[TR]
[TD]02-104-2-Q[/TD]
[TD]01-49-0-Q[/TD]
[TD]2-44-4-Q[/TD]
[/TR]
[TR]
[TD]02-34-4-L[/TD]
[TD]01-49-3-Q[/TD]
[TD]2-47-0-Q[/TD]
[/TR]
[TR]
[TD]02-34-5-L[/TD]
[TD]01-64-1-Q[/TD]
[TD]2-84-4-Q[/TD]
[/TR]
[TR]
[TD]02-37-1-L[/TD]
[TD]01-84-4-Q[/TD]
[TD]2-88-4-Q[/TD]
[/TR]
[TR]
[TD]02-37-3-L[/TD]
[TD]02-104-4-A[/TD]
[TD]2-93-2-Q[/TD]
[/TR]
[TR]
[TD]02-44-01-L[/TD]
[TD]02-130-2-A[/TD]
[TD]2-96-7-Q[/TD]
[/TR]
[TR]
[TD]02-44-0-Q[/TD]
[TD]02-133-2-Q[/TD]
[TD]2-98-5-Q[/TD]
[/TR]
[TR]
[TD]02-44-2-L[/TD]
[TD]02-143-2-Q[/TD]
[TD]2-103-1-A[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Welcome to the Board!

Assuming that your data starts on Row 1, highlight the data in column R from row 1 to the end, and enter this Conditional Formatting formula:

=AND(COUNTIF($H:$H,$R1)>0,COUNTIF($M:$M,$R1)>0)

and select your formatting color option.

 

[sriche01]

Highlight column R (or whatever part of it you want formatted)

In conditional formatting go to the section New Rule > Use a formula to determine which cells to format.

The formula would look like this =if(and(countifs($H$1:$H$10,R1)>0,countifs($M$1:$M$10,R1)>0),true,false)

Adjust the row numbers in the above formulas to fit your ranges. Pay attention to the dollar signs and do exactly as above. Then select your formatting.

Hope this helps

 

[Joe4]

The formula would look like this =if(and(countifs($H$1:$H$10,R1)>0,countifs($M$1:$M$10,R1)>0),true,false)

The solution is similar to the one I posted, except for one thing.
Note that the AND function will return a boolean value (True or False), by the nature of how the function works.
So, you do not need to wrap it in an IF function and explicitly tell it to return True or False (that is redundant and unnecessary).
So you can leave that part off, like I did.

 

[sriche01]

The solution is similar to the one I posted, except for one thing.
Note that the AND function will return a boolean value (True or False), by the nature of how the function works.
So, you do not need to wrap it in an IF function and explicitly tell it to return True or False (that is redundant and unnecessary).
So you can leave that part off, like I did.

Thanks for clarifying, Joe. I realized as soon as I posted then saw your post how redundant that was. Wonder how many formulas I have written that way.... That's why I love this forum.

 

[Joe4]

Thanks for clarifying, Joe. I realized as soon as I posted then saw your post how redundant that was. Wonder how many formulas I have written that way.... That's why I love this forum.

No problem! Years ago, I was doing the same thing when someone pointed it out to me.

 

[ttop1010]

thanks Joe for the quick response, it worked! Partially....i wonder if its because i had "Highlight Duplicates on all three rows?

 

[Joe4]

it worked! Partially...

Please explain what you mean by "partially".

Did it not highlight some values that it should?

Or did it highlight more values than you wanted?
If so, be sure that those values are not being highlighted by your other CF condition.
Note that if you did not remove the other condition, that condition will also still be in effect.

 

[ttop1010]

it highlighted a few, do you think the CONDITIONAL FORMATTING in "R" keeping it from working?

the goal was to highlight the cell in column "R" that is found in all three columns

btw i really appreciate your help

 

[Joe4]

Try removing ALL conditional formatting conditions first, then try applying my rule again.
That way we will be sure that no old stuff is interfering with it.