Formula Required For Conditional Format

[hrayani]

Hello Friends,

I need a formula for conditional format for below

Sample DATA manually entered

A ....B
414 John
420 Christian
421 Christian
414 Mike
456 Charlie
465 Charlie
456 John


A ....B
414 Mike (414 contains Mike so the cell should remain same)
414 Charlie (414 does not contains Charlie so the cell should turn red)


Any help would be appreciated


Regards,

Humayun

 

[Joe4]

I am not quite clear on the data layout.
Are you comparing two sheets? So your first sheet looks like:

A ....B
414 John
420 Christian
421 Christian
414 Mike
456 Charlie
465 Charlie
456 John

and your second sheet looks like this:

A ....B
414 Mike
414 Charlie

If not, then where does this second part come from?

 

[hrayani]

Thanks for the reply Joe,

Yes 1 am comparing 2 sheets

Sheet 1 has the data

A ....B
414 John
420 Christian
421 Christian
414 Mike
456 Charlie
465 Charlie
456 John


Sheet 2

Cell A1 414
Cell B1 Charlie

I want the cell B1 to turn red coz on sheet 1 the number 414 does not have charlie in it

 

[Joe4]

OK, let's say that you have Sheet1 and Sheet2, and the data is in columns A and B, and starts on row 1 in each sheet.
Then, on Sheet2, select all your data in column B (starting at row 1) and enter this Conditional Formatting formula:

=COUNTIFS(Sheet1!$A:$A,$A1,Sheet1!$B:$B,$B1)=0

and choose your red formatting option.

 

[hrayani]

Hello Joe,

your formula is working just perfect...

can we add a condition to the formula

if cell A1 is blank ----> nothing should happen. I mean the cell should remain the same

I can do it by entering a seperate condition =isblank(a1)

but i was just wondering if its possible to merge these 2

Regards,

Humayun

 

[Joe4]

Use the AND function to test multiple conditions, i.e.

=[B]AND([/B][COLOR=#ff0000]$A1<>""[/COLOR],[COLOR=#0000ff]COUNTIFS(Sheet1!$A:$A,$A1,Sheet1!$B:$B,$B1[/COLOR][COLOR=#0000ff])=0[/COLOR][B])[/B]

 

[hrayani]

Sorry Joe I am a bit confused... So here i am stating all the conditions.


1) if A1 and B1 both are blank then nothing should happen
2) if A1 has data and B1 is blank then conditional format should trigger
3) if A1 is blank and B1 has data then conditional format should trigger

 

[Joe4]

OK, looks like you are changing conditions on me here:

First you said:

if cell A1 is blank ----> nothing should happen.

then you said:

3) if A1 is blank and B1 has data then conditional format should trigger

So, it looks like with these three conditions, we have to check a bunch of things.
Try this:

=OR([COLOR=#ff0000]AND($A1<>"",$B1="")[/COLOR],[COLOR=#0000ff]AND($A1="",$B1<>"")[/COLOR],[COLOR=#008000]AND($A1<>"",$B1<>"",COUNTIFS(Sheet1!$A:$A,$A1,Sheet1!$B:$B,$B1)=0)[/COLOR])

So, these is checking for the following situations, and will apply the formatting if ANY one of the three situations are met:
1. A1 is not blank, B1 is blank
2. A1 is blank, B1 is not blank
3. Both A1 and B1 and not blank, and the COUNTIF finds no records meeting the criteria (no matching A and B records compared to the other sheet)

 

[hrayani]

Hi Joe,

Thanks a lot.... Working PERFECT ... exactly what I wanted.

and yes - sorry for the confusion from my side.

Regards,

Humayun

 

[Joe4]

You are welcome.
Glad we were able to figure it out!