ttop1010

New Member
Joined
Nov 12, 2017
Messages
16
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"
01-1-1-Q01-143-2-Q1-37-1-Q
01-1-2-A01-143-4-A01-11-1-Q
01-174-2-Q01-165-2-Q1-54-1-Q
01-44-4-Q01-190-4-Q4-31-1-L
01-49-0-Q01-195-4-Q03-92-0-C
01-49-3-Q01-200-2-Q03-69-10-Q
01-5-1-Q01-200-4-Q03-96-1-L
01-64-1-Q01-235-01-A2-82-2-Q
01-64-6-Q01-235-0-Q01-64-1-Q
01-74-2-Q01-238-2-Q01-64-6-Q
01-84-4-Q01-241-0-Q1-69-1-Q
01-9-0-Q01-245-7-Q1-92-2-Q
01-9-2-Q01-44-4-Q2-44-0-Q
02-104-2-Q01-49-0-Q2-44-4-Q
02-34-4-L01-49-3-Q2-47-0-Q
02-34-5-L01-64-1-Q2-84-4-Q
02-37-1-L01-84-4-Q2-88-4-Q
02-37-3-L02-104-4-A2-93-2-Q
02-44-01-L02-130-2-A2-96-7-Q
02-44-0-Q02-133-2-Q2-98-5-Q
02-44-2-L02-143-2-Q2-103-1-A

<colgroup><col><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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:
Code:
=AND(COUNTIF($H:$H,$R1)>0,COUNTIF($M:$M,$R1)>0)
and select your formatting color option.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
thanks Joe for the quick response, it worked! Partially....i wonder if its because i had "Highlight Duplicates on all three rows?
 
Upvote 0
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.
 
Last edited:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,584
Messages
6,114,509
Members
448,575
Latest member
hycrow

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top