If I have Y in A1 and Blank in B1 then Blank in A2 and Y in B2 How do I check with IF AND statement if there is a Y in either column and if there is Return Y
Many Thanks
Hi
If you already have cols A and B populated with Y's and blanks you can do this by creating either 1 or 2 new columns C / D. One additional column would signify a record that has at least one Y this way=if(OR(A1="Y", B1="Y"),"Y","")
Col A Col B "Y" Present? Y Y Y Y - Y - Y Y - - Y - Y - - - Y Y - - Y Y YIf you want to replicate cols A and B you would have the same as the above in both cols C and D
Col A Col B New A New B Y Y Y Y Y - Y Y - Y Y Y - - Y - Y Y - - - Y Y Y - - Y Y Y YIf you want to overwrite cols A and B you would need a Macro to do that although it is usually not a good idea to overwrite input data.
Here's a sneaky way to get the same results: =IF(COUNTIF(A1:B1,"Y"),"Y","")
Pop Quiz: Why does that work?
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.
The IF statement evaluates TRUE or FALSE if the COUNTIF statement is met (TRUE if there is a "Y" or FALSE if there is no "Y") Okay, now tell me the real reason.
But COUNTIF returns a numerical value, not TRUE or FALSE. Why would the IF evaluate to TRUE or FALSE based on the value that the COUNTIF returns? Here's a hint:
I'm sure you know that the ROW() function simply returns the number of the Row it is used in.
Ok, so knowing that fact, enter this in A1 and drag it down to A5:
=IF(ROW()-3,TRUE,FALSE)
The results might lead you to something interesting (weird?) about the inner workings of Excel and explain why the COUNTIF function works in the above situation.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.
If the result = 0 it equates to FALSE. Interesting.
Thanks for your help 100% Really appricate it
Great FAB Brilliant Thanks very much. This is the best site ever
Excel considers 0 to be FALSE and any non-zero value, positive or negative, to be TRUE. Therefore, if there is a "Y" in column A or B or both, the COUNTIF will return a non-zero value and the IF will evaluate to TRUE.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.
Hi I have been through and added col as suggested and now it works with
"=IF(OR(W15="Y",X15="N"),"N","Y") Many thanks
Is it possible for you please tell me the difference between the above and below. I have searched HELP and the forums
"=IF(AND(W15="Y",X15="N"),"N","Y")
The Excel help files explain both of these functions very well. I quote: OR: Returns TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE.
AND: Returns TRUE if all its arguments evaluate to TRUE; returns FALSE if one or more arguments evaluate to FALSE.
In other words...
OR will return TRUE if any one or more of the arguments are TRUE.
AND will return TRUE only if all of the arguments are TRUE.
For the OR to return TRUE:
W15 contains Y
OR
X15 contains N
OR
W15 contains Y and X15 contains NFor the AND to return TRUE:
W15 contains Y AND X15 contains N.
Therefore, you will see no difference between your formulas if both W15 contains Y and X15 contains N since both the OR and the AND functions will be TRUE.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.
Brilliantly explained I thought I was going mad I have discovered the problem I was getting the wrong answer because the data wasnt clean
I did check the forums for answers and other excel web sites BUT no I didnt ask Excel help
Many Thanks for your patience
Sometimes the answers are right in front of us! For simple explanations on how a function works or what arguments it needs, I often turn to Excel Help.
To find out how other people are using the functions in real life, I'll often search forums and other "tip" sites.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.