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>
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,968
Office Version
  1. 365
Platform
  1. Windows
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.
 

sriche01

Board Regular
Joined
Dec 24, 2013
Messages
136
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,968
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Dec 24, 2013
Messages
136
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,968
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 12, 2017
Messages
16

ADVERTISEMENT

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,968
Office Version
  1. 365
Platform
  1. Windows
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:

ttop1010

New Member
Joined
Nov 12, 2017
Messages
16
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,968
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,129
Messages
5,599,903
Members
414,345
Latest member
Jonathan43

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
Top