repeated values in cloumn A, return if column B=X at least in one row

[epardo87]

Hello there,

So I have:

• column A where a value can be repeated unlimited times
• column B can vary between X or Y
• Column C is where the formula needs to go:

If value in column A contains an "X" in column B, in any of the rows where it appears, then "OK"

​

I'd really appreciate your help! regards!
[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]A[/TD]
[TD]Y[/TD]
[TD]OK[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Y[/TD]
[TD]OK[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]X[/TD]
[TD]OK[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Y[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Y[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Y[/TD]
[TD]-[/TD]
[/TR]
</tbody>[/TABLE]

 

[rpaulson]

Not sure I fully understand, Is this what you want?

assuming about data is in A1:B6 then in C1 put =if(B1="X","OK","")
fill down as needed

Ross

 

[epardo87]

thanks for your answer Ross,

if I do that, I will get "OK" only in row 3, and I also need that: given the fact that there is one row where A=X, then every other row with A has to be "OK" even if that specific column has a "Y"

 

[rpaulson]

So if first column has an "A" or second column has an "X"?

try =IF(OR(B1="X",A1="A"),"OK","")

Ross

 

[epardo87]

Well not necessary.

in this example is "A" because it has an X in the third Row, so all other "A" will now have the "OK".
but if "A" doesn't have "X" then none should have "B"
here a larger example of the results required:

[TABLE="class: grid, width: 150"]
<tbody>[TR]
[TD]A[/TD]
[TD]Y[/TD]
[TD]OK[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Y[/TD]
[TD]OK[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]X[/TD]
[TD]OK[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Y[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Y[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Y[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]X[/TD]
[TD]OK[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Y[/TD]
[TD]OK[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]X[/TD]
[TD]OK[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]Y[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]Y[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]Y[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]Y[/TD]
[TD]OK[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]X[/TD]
[TD]OK[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]Y[/TD]
[TD]OK[/TD]
[/TR]
</tbody>[/TABLE]

 

[epardo87]

I'm sorry, I meant "OK" instead of "B"...

 

[rpaulson]

In you latest sample , why does group "E" have OK since it does NOT have an "X" in the third row??

 

[epardo87]

well, the "X" in column B could appear anywhere, in the first, third, fifth, etc.that's the main problem, I need the formula to tell me if that value in column A, has an "X" in any other appearance of same value at the table​

 

[epardo87]

This is what I need the formula to evaluate:
​[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]Y[/TD]
[TD]A=X? NO, is there any other row where A=X? YES, then "OK"[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Y[/TD]
[TD]A=X? NO, is there any other row where A=X? YES, then "OK"[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]X[/TD]
[TD]A=X? YES, then "OK"[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Y[/TD]
[TD]B=X? NO, is there any other row where B=X? NO, then "-"[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Y[/TD]
[TD]B=X? NO, is there any other row where B=X? NO, then "-"[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Y[/TD]
[TD]B=X? NO, is there any other row where B=X? NO, then "-"[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]X[/TD]
[TD]C=X? YES, then "OK"[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Y[/TD]
[TD]C=X? NO, is there any other row where C=X? YES, then "OK"[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]X[/TD]
[TD]C=X? YES, then "OK"[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]Y[/TD]
[TD]D=X? NO, is there any other row where D=X? NO, then "-"[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]Y[/TD]
[TD]D=X? NO, is there any other row where D=X? NO, then "-"[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]Y[/TD]
[TD]D=X? NO, is there any other row where D=X? NO, then "-"[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]Y[/TD]
[TD]E=X? NO, is there any other row where E=X? YES, then "OK"[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]X[/TD]
[TD]E=X? YES, then "OK"[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]Y[/TD]
[TD]E=X? NO, is there any other row where E=X? YES, then "OK"[/TD]
[/TR]
</tbody>[/TABLE]

 

[rpaulson]

=if(countifs(a:a,a1,b:b,"x")>=1,"ok","")

fill down as needed