If 2 cells have a value do nothing, if one cell has value and other cell has no value do this

[dappy]

Hi again folks.

I have a list of cells from lookups that result in this. these cells have ether equal too or vlookup results.

[TABLE="width: 512"]
<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]TCH_2[/TD]
[TD="width: 64"]TCH_3[/TD]
[TD="width: 64"]TCH_4[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]TRX2[/TD]
[TD="width: 64"]TRX3[/TD]
[TD="width: 64"]TRX4[/TD]
[TD="width: 64"]TRX5[/TD]
[/TR]
[TR]
[TD]55[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]48[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]14[/TD]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]78[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]20[/TD]
[TD]21[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]84[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]26[/TD]
[TD]27[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

What i would like to do is compare tch_2 cells to trx2 cells and if both have data then do nothing but if as in the case of tch_3 and trx3 there are blanks in tch_3 then another cell has an output text. unfortnately i can get a result for these BUT if both cells are blank i still get an output. it sees the lookups or equals as a content. I've tried Value and iferror but cant get to the state where no ouptut is given when both are blank

Hope this is clear folks and thanks in advance.

Carl

 

[cerfani]

use ISBLANK to check if the cell is empty or contains an empty string

you can use AND and OR functions to check any combination of conditions you like, example...

=IF(AND(ISBLANK(A2),ISBLANK(D2)),...,IF(AND(...,OR(...,...)),...,...))

https://exceljet.net/excel-functions/excel-isblank-function

 

[dappy]

use ISBLANK to check if the cell is empty or contains an empty string

you can use AND and OR functions to check any combination of conditions you like, example...

=IF(AND(ISBLANK(A2),ISBLANK(D2)),...,IF(AND(...,OR(...,...)),...,...))

https://exceljet.net/excel-functions/excel-isblank-function

Thanks for getting back to me, I cant get this to work.

I have

=IF(AND(ISBLANK(F2),L2>0),"delete","leave")

delete being when one of the cells is empty and the other not.

it always returns "leave" no matter if a value is showing in the cell or not.

would you be able to help further please?

 

[jtakw]

Hi,

Since you say the cells are populated by a formula, then a "Blank" cell is not "Truly" blank, try changing ISBLANK in your formula like this:

=IF(AND(F2="",L2>0),"delete","leave")

 

[jtakw]

Also, if the value in L2 is result of a formula, make sure the result is numeric.

Sample below for ISBLANK and ISNUMBER:

Book1
	A	B
1		FALSE
2		TRUE
3	9	TRUE
4	9	FALSE
Sheet64

Cell Formulas
Range	Formula
A1	=""
A4	="9"
B1	=ISBLANK(A1)
B2	=ISBLANK(A2)
B3	=ISNUMBER(A3)
B4	=ISNUMBER(A4)

 

[dappy]

thanks but its the same, i cant get any other result than Leave or Delete. I'm after a blank if one or the other doesnt have any results from the lookups. is this possible?

 

[jtakw]

Yes it's definitely possible, we need to figure out what're in those cells from the result of your lookup(s),

As my samples above, what do you get from:

=ISNUMBER(cell reference)
=ISBLANK(cell reference)
=LEN(cell reference)

Compared to what you see?

 

[jtakw]

May be I'm not understanding your requirements and/or you're not explaining it, under what circumstances do you want the result "delete", "leave", and may be ""?

In post #3 you say "=IF(AND(ISBLANK(F2),L2>0),"delete","leave")

delete being when one of the cells is empty and the other not."

In post #6 you say "I'm after a blank if one or the other doesnt have any results from the lookups"

Please explain what you're after, may be show a couple of samples.

 

[dappy]

Yes it's definitely possible, we need to figure out what're in those cells from the result of your lookup(s),

As my samples above, what do you get from:

=ISNUMBER(cell reference)
=ISBLANK(cell reference)
=LEN(cell reference)

Compared to what you see?

ok i get this

[TABLE="width: 412"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]55[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]55[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]55[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E7[/TD]
[TD]=isnumber(A7)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]F7[/TD]
[TD]=isnumber(B8)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E7[/TD]
[TD]=isblank(A7)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]F8[/TD]
[TD]==isblank(B8)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]G7[/TD]
[TD]=len(A7)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]G8[/TD]
[TD]=len(B8)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Until you clarify my questions from post #8 , is this what you mean?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	TCH_2	TCH_3	TCH_4		TRX2	TRX3	TRX4	TRX5		TCH_2/TRX2	TCH_3/TRX3	TCH_4/TRX4
2	55				2	3	99			leave	delete	delete
3	48				8	9				leave	delete
4	34	99			14	15				leave	leave
5	78				20	21				leave	delete
6	84		99		26	27				leave	delete	delete
Sheet64

Cell Formulas
Range	Formula
J2	=IF(AND(A2="",E2=""),"",IF(OR(A2="",E2=""),"delete","leave"))

J2 formula copied down and across.