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
82
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
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

dappy

Board Regular
Joined
Apr 23, 2018
Messages
82
Office Version
  1. 2013
Platform
  1. Windows
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?
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
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

Well-known Member
Joined
Jun 29, 2014
Messages
5,146

ADVERTISEMENT

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

Sample below for ISBLANK and ISNUMBER:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";"></td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">9</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">9</td><td style="text-align: right;;">FALSE</td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet64</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A1</th><td style="text-align:left">=""</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B1</th><td style="text-align:left">=ISBLANK(<font color="Blue">A1</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=ISBLANK(<font color="Blue">A2</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B3</th><td style="text-align:left">=ISNUMBER(<font color="Blue">A3</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A4</th><td style="text-align:left">="9"</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B4</th><td style="text-align:left">=ISNUMBER(<font color="Blue">A4</font>)</td></tr></tbody></table></td></tr></table><br />
 

dappy

Board Regular
Joined
Apr 23, 2018
Messages
82
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Jun 29, 2014
Messages
5,146

ADVERTISEMENT

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

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
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

Board Regular
Joined
Apr 23, 2018
Messages
82
Office Version
  1. 2013
Platform
  1. Windows
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>
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Until you clarify my questions from post #8 , is this what you mean?

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">TCH_2</td><td style=";">TCH_3</td><td style=";">TCH_4</td><td style="text-align: right;;"></td><td style=";">TRX2</td><td style=";">TRX3</td><td style=";">TRX4</td><td style=";">TRX5</td><td style="text-align: right;;"></td><td style=";">TCH_2/TRX2</td><td style=";">TCH_3/TRX3</td><td style=";">TCH_4/TRX4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">55</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">99</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">leave</td><td style=";">delete</td><td style=";">delete</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">48</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">8</td><td style="text-align: right;;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">leave</td><td style=";">delete</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">34</td><td style="text-align: right;;">99</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">14</td><td style="text-align: right;;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">leave</td><td style=";">leave</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">78</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">20</td><td style="text-align: right;;">21</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">leave</td><td style=";">delete</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">84</td><td style="text-align: right;;"></td><td style="text-align: right;;">99</td><td style="text-align: right;;"></td><td style="text-align: right;;">26</td><td style="text-align: right;;">27</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">leave</td><td style=";">delete</td><td style=";">delete</td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet64</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">J2</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">A2="",E2=""</font>),"",IF(<font color="Red">OR(<font color="Green">A2="",E2=""</font>),"delete","leave"</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

J2 formula copied down and across.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,072
Messages
5,526,619
Members
409,713
Latest member
roman9980

This Week's Hot Topics

Top