If 2 cells have a value do nothing, if one cell has value and other cell has no value do this
Thanks Thanks:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

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

  1. #1
    New Member
    Join Date
    Apr 2018
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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_2 TCH_3 TCH_4 TRX2 TRX3 TRX4 TRX5
    55 2 3
    48 8 9
    34 14 15
    78 20 21
    84 26 27

    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

  2. #2
    Board Regular
    Join Date
    Dec 2014
    Posts
    1,133
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

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

    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...blank-function
    Last edited by cerfani; May 24th, 2018 at 12:20 PM.

  3. #3
    New Member
    Join Date
    Apr 2018
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by cerfani View Post
    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...blank-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?

  4. #4
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    3,291
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)

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

    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")

  5. #5
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    3,291
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)

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

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

    Sample below for ISBLANK and ISNUMBER:

    AB
    1FALSE
    2TRUE
    39TRUE
    49FALSE

    Sheet64



    Worksheet Formulas
    CellFormula
    A1=""
    B1=ISBLANK(A1)
    B2=ISBLANK(A2)
    B3=ISNUMBER(A3)
    A4="9"
    B4=ISNUMBER(A4)


  6. #6
    New Member
    Join Date
    Apr 2018
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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?

  7. #7
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    3,291
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)

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

    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?

  8. #8
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    3,291
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  9. #9
    New Member
    Join Date
    Apr 2018
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by jtakw View Post
    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

    A B E F
    55 FALSE FALSE
    55 FALSE FALSE
    55 2 0
    E7 =isnumber(A7)
    F7 =isnumber(B8)
    E7 =isblank(A7)
    F8 ==isblank(B8)
    G7 =len(A7)
    G8 =len(B8)

  10. #10
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    3,291
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)

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

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

    ABCDEFGHIJKL
    1TCH_2TCH_3TCH_4TRX2TRX3TRX4TRX5TCH_2/TRX2TCH_3/TRX3TCH_4/TRX4
    2552399leavedeletedelete
    34889leavedelete
    434991415leaveleave
    5782021leavedelete
    684992627leavedeletedelete

    Sheet64



    Worksheet Formulas
    CellFormula
    J2=IF(AND(A2="",E2=""),"",IF(OR(A2="",E2=""),"delete","leave"))



    J2 formula copied down and across.

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

DMCA.com