Compare 3 columns

Nana - Sep 4, 2010 at 02:41 AM
 Nana - Sep 5, 2010 at 08:53 AM

Please help me. I stuck with excel formula. Need help....if two consecutive "y", the comment is "action".

A B C Comment
y y y -
y y n action
y n y -
n n y -
n y y action
n n n -

2 responses

RayH Posts 122 Registration date Tuesday August 31, 2010 Status Contributor Last seen June 20, 2016 26
Sep 4, 2010 at 09:09 AM
Why wouldn't the first line in your example qualify for the 'action' comment? There are two sets of consecutive 'y'.
I'm not so sure either above is a good example or not. It might not suitable to compare for 3 columns, perhaps 4 columns or something else, I need an advice please.
Hope this would give a better explanation.
The rules are; Say, in one row there are 1st "y" & 2nd "y" (could be 3rd "y" or 4th "y" etc). Once hit the 2nd "y", should be "action". Any consecutive twice of "y" in a row should be "action". If there is any "n" in between comment is "-".
Exceptional: If 2nd "y" is stated as "action" (as per above rule) and if there is any 3rd "y" occurred, this should be "-" as no action.
Once again the 3rd "y" is considered as 1st occurrence and the 4th "y" is for 2nd occurrence of "y" in a row that should be "action" again.
The hard is the 3rd "y". Some excel formula will read the 2nd "y" and 3rd "y" as two consecutive "y".
=IF((AND(A1="y",B1="y",C1="n")),"Action",(IF((AND(A1="n",B1="y",C1="y")),"Action","-"))) <-- this is not work for the 4th or more columns.
=IF(A1="y",IF(B1="y",IF(C1="y","-","Action"),"-"),"-") <-- this is also not working for "n" "y" y" set.
Please kindly need help.