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

dappy

Board Regular
Joined
Apr 23, 2018
Messages
124
Office Version
  1. 2013
Platform
  1. Windows
Hi again folks.

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

TCH_2TCH_3TCH_4TRX2TRX3TRX4TRX5
5523
4889
341415
782021
842627

<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>
</tbody>

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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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...

Code:
=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?
 
Upvote 0
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")
 
Upvote 0
Also, if the value in L2 is result of a formula, make sure the result is numeric.

Sample below for ISBLANK and ISNUMBER:


Book1
AB
1 FALSE
2TRUE
39TRUE
49FALSE
Sheet64
Cell Formulas
RangeFormula
A1=""
A4="9"
B1=ISBLANK(A1)
B2=ISBLANK(A2)
B3=ISNUMBER(A3)
B4=ISNUMBER(A4)
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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

ABEF
55FALSEFALSE
55FALSEFALSE
5520
E7=isnumber(A7)
F7=isnumber(B8)
E7=isblank(A7)
F8==isblank(B8)
G7=len(A7)
G8=len(B8)

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
Until you clarify my questions from post #8 , is this what you mean?


Book1
ABCDEFGHIJKL
1TCH_2TCH_3TCH_4TRX2TRX3TRX4TRX5TCH_2/TRX2TCH_3/TRX3TCH_4/TRX4
2552399leavedeletedelete
34889leavedelete
434991415leaveleave
5782021leavedelete
684992627leavedeletedelete
Sheet64
Cell Formulas
RangeFormula
J2=IF(AND(A2="",E2=""),"",IF(OR(A2="",E2=""),"delete","leave"))


J2 formula copied down and across.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,220
Members
448,554
Latest member
Gleisner2

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