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!

A	Y	OK
A	Y	OK
A	X	OK
B	Y	-
B	Y	-
C	Y	-

<tbody>
</tbody>

 

[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:

A	Y	OK
A	Y	OK
A	X	OK
B	Y	-
B	Y	-
B	Y	-
C	X	OK
C	Y	OK
C	X	OK
D	Y	-
D	Y	-
D	Y	-
E	Y	OK
E	X	OK
E	Y	OK

<tbody>
</tbody>

 

[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:
​

A	Y	A=X? NO, is there any other row where A=X? YES, then "OK"
A	Y	A=X? NO, is there any other row where A=X? YES, then "OK"
A	X	A=X? YES, then "OK"
B	Y	B=X? NO, is there any other row where B=X? NO, then "-"
B	Y	B=X? NO, is there any other row where B=X? NO, then "-"
B	Y	B=X? NO, is there any other row where B=X? NO, then "-"
C	X	C=X? YES, then "OK"
C	Y	C=X? NO, is there any other row where C=X? YES, then "OK"
C	X	C=X? YES, then "OK"
D	Y	D=X? NO, is there any other row where D=X? NO, then "-"
D	Y	D=X? NO, is there any other row where D=X? NO, then "-"
D	Y	D=X? NO, is there any other row where D=X? NO, then "-"
E	Y	E=X? NO, is there any other row where E=X? YES, then "OK"
E	X	E=X? YES, then "OK"
E	Y	E=X? NO, is there any other row where E=X? YES, then "OK"

<tbody>
</tbody>

 

[rpaulson]

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

fill down as needed