Conditional Formatting - Highlight Cells

Hello. I need help with a conditional formatting formula. I have a 6000 plus row spreadsheet and I would like conditional formatting to highlight cells in one column based on criteria from two other columns. I want to highlight cells in column D if cells in column C begin with the letter B and end with the letter I, but ONLY if the cells in column B are identical.

snip1.JPG

I hope the attachment showing the scenario shows up.

Thank you.

Try

=AND(LEFT(C2)="b",RIGHT(C2)="i",COUNTIF($C$2:$C$1000,$C2)>1)

Thank you for the quick reply. In that situation nothing would be highlighted because there is only one "unit" in column C that begins with b and ends with i. I guess I should have made that part more clear.

Thank you.

Thank you JohnTopley. I used that formula in conditional formatting "use a formula to determine which cells to format" and used a fill color... The only cell that filled was A11 (as the image shows).

Try applying (copy) my formula to D2 as it is D you want highlighted.

Set applies to: $d$2:$d$1000

Thanks again JohnTopley. I see where I made the mistake of assigning the condition to all the cells. I then went back and only applied the condition to cells in column D. I came up with the same result as your attached file, however, I discovered that the formula found the duplicate "unit" in column C. It's the duplicate in column B that it should be finding. When I changed to ($B$2:$B$1000,$B2) it highlighted the cells that I originally expected and also cell D9 that I was not expecting.

Any thoughts?

Thank you.

I misread your original post: So we are looking for duplicates in B where corresponding cells in C start with B and end in I BUT C are not (necessarily) duplicates.

I'll have to think how we might do this: it's late UK time so I will look tomorrow. Someone else might come up with a solution.

Correct JohnTopley. In fact, there definitely will be duplicates in Col C that the formula should ignore. It's only col B duplicates.

I understand that it's getting late. I will check in on the thread tomorrow.

Thank you for all your help!

With a helper column in E

=IF(AND(LEFT(C2)="b",RIGHT(C2)="I"),B2&LEFT(C2)&RIGHT(C2),"")

CF formula

=AND(E2<>"",COUNTIF($E$2:$E$14,E2)>1)

@Tony

... never would have thought of using SUMPRODUCT as a way of "combining" the tests.

So much for me being an "expert" !!!

Try this formula for CF:

=COUNTIF(C2,"b*I")*COUNTIFS($B$2:$B$14,B2,$C$2:$C$14,C2)>1

A big thank you to all of you who contributed to solving my Conditional Formatting needs. Thank you Tony Valko for the formula!

telcocook

I discovered a small issue when I put this CF into practical application. Not only does column B need to have the same data to trigger it, so does column A. An example would be if there was only one "unit" with BxxxxI on "dwg" 74 which has a "term" a4, the current formula created by Tony would highlight the "qty" on that row as well (which it should not).

Something I overlooked in the beginning. Sorry.

Any further help would be greatly appreciated.

Thank You.

Thanks again for the quick reply Tony. I'm getting ready to go into a meeting. I will check it out in about an hour and let you know.

Thank you.

Or trry this ...
=COUNTIF(C2,"b*I")*COUNTIFS($A$2:$A$14,A2,$B$2:$B$14,B2)>1