Iff Statements - Comparison

[gatefire08]

A l B l C
Michael l Michel l
James R l James l
Karl l Karl B l
John W l John W l
Above, everything is merged in. The "l" is supposed to divide each column.


The problem:


I want an iff statement in which will compare data from column A vs column B.

Michael vs Michel - (Mispelled) - Statement: "Mismatch on Name (Michael) vs (Michel)"
James R vs James - (Mismatch) - Statement: "Mismatch: Column A has a suffix"
Karl vs Karl B - (Mismatch) - Statement: "Mismatch: Column B has a suffix"
John W vs John W (Extra spaces) - "Mismatch on Name: Extra Spaces"

For the extra spaces, someone was able to help me with this IFF statement

"IF(AND(TRIM(A1)=TRIM(B1),A1<>B1),"Mismatch on Name: Extra Space","")"

Thank you for your help!

[mrice]

Try

=IF(AND(FIND(B2,A2),LEN(A2)>LEN(B2)),"Mismatch: Column A has a suffix","")

for one of them.

[simarui]

instead of "" try

if(lenA1>lenb1,"Mismatch: Column A has a suffix",if(lenb1>a1,"Mismatch: Column B has a suffix",if(a1<>b1,"Mismatch on Name ("&A1&") vs ("&b1&")","these are the same")))

[simarui]

good call mrice... misspellings would often trigger my "suffix" error...

add the and(find()) piece to both of my first two if statements...