excelexcel-formula

Compare both the columns from Text values

I have a data set having IN & OUT columns with text string.

Compared text "Yes", "NA", "N/A", "No", "Partial".

If it is matching both the columns then 0 else 1.

If any column having NA or N/A that also be considered as "Yes".

Some times data will receive with Comments.

Some how I managed with this formula. Any better solution rather than this.

Thank you.

=N(IF(ISERROR(IFERROR(SEARCH("Yes",E3),IFERROR(SEARCH("n/A",E3),SEARCH("NA",E3)))),IF(ISERR(SEARCH("Partial",E3)),"No","Partial"),"Yes")<>IF(ISERROR(IFERROR(SEARCH("Yes",F3),IFERROR(SEARCH("N/A",F3),SEARCH("NA",F3)))),IF(ISERR(SEARCH("Partial",F3)),"No","Partial"),"Yes"))

Preview

IN **OUT **
Partial- needs to be considered Yes - Worked well
Yes - worked well Yes
Partial- needs to be considered Partial - "applied"
NA Yes - Worked well
N/A - Not applicable Yes- Worked well
NA - Not applicable N/A
NA- not applicable NO - not applicable
No Yes
Solution

  • This is a solution that spills the result in Office 365:

    =LET(x,TEXTSPLIT(B3:C10,{" ","-"}),y,IF(ISNA(XMATCH(x,{"NA","N/A"})),x,"Yes"),N(TAKE(y,,1)<>DROP(y,,1)))

    enter image description here

    Where x is the Textsplit of the whole range on (first found) space character or minus sign in each string; resulting in the first word of each cell.

    y matches these strings to equal N/A or NA, if so return Yes, else return the string.

    Last we compare the strings of the first and last column of y