Formula based on data in 2 column

[Chandresh]

HI ALL,

Could you please help me with formula which can give me result based on data from 2 different column.

[TABLE="width: 128"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"]ID[/TD]
[TD="width: 64, bgcolor: transparent"]Type[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A500[/TD]
[TD="bgcolor: transparent"]S[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A500[/TD]
[TD="bgcolor: transparent"]S[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A501[/TD]
[TD="bgcolor: transparent"]P[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A501[/TD]
[TD="bgcolor: transparent"]P[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A506[/TD]
[TD="bgcolor: transparent"]P[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A506[/TD]
[TD="bgcolor: transparent"]P[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A506[/TD]
[TD="bgcolor: transparent"]S[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A506[/TD]
[TD="bgcolor: transparent"]S[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A506[/TD]
[TD="bgcolor: transparent"]S[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A506[/TD]
[TD="bgcolor: transparent"]S[/TD]
[/TR]
</tbody>[/TABLE]

I have data in 2 columns A and B, I need a formula which can lookup the data in entire column and provide me the results.

- if data against ID is "S" then "OK"
- if data against ID is "P. then "OK"
- if the data against ID is "S" and "P" then "Check" - (ID A506 has both P and S)

Thanks
Chandresh

 

[Finalfight40]

Would this work? You would need to adjust the ranges to match your data but:

=IF(SUM(IF(COUNTIFS(A1:A10, A12, B1:B10, "S")>1, 1, 0)+IF(COUNTIFS(A1:A10, A12, B1:B10, "P")>1, 1, 0))=2, "CHECK", "OK")

Where:
A1:A10 is the range of ID
B1:B10 is the range of Type
A12 is the ID you are looking up

 

[Chandresh]

Would this work? You would need to adjust the ranges to match your data but:

=IF(SUM(IF(COUNTIFS(A1:A10, A12, B1:B10, "S")>1, 1, 0)+IF(COUNTIFS(A1:A10, A12, B1:B10, "P")>1, 1, 0))=2, "CHECK", "OK")

Where:
A1:A10 is the range of ID
B1:B10 is the range of Type
A12 is the ID you are looking up

Thanks for your reply , I have applied the formula in my working sheet however its working for some cases. I have added one more id "A501" in the data base now the output comes as "OK".
Ideally it should come as "Check" as there are 2"P" and 1 "S", could you please help.

[TABLE="width: 265"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]ID[/TD]
[TD]Type[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A500[/TD]
[TD]S[/TD]
[TD]OK[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A500[/TD]
[TD]S[/TD]
[TD]OK[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A501[/TD]
[TD]P[/TD]
[TD]OK[/TD]
[TD]output should be Check[/TD]
[/TR]
[TR]
[TD]A501[/TD]
[TD]P[/TD]
[TD]OK[/TD]
[TD]output should be Check[/TD]
[/TR]
[TR]
[TD]A501[/TD]
[TD]S[/TD]
[TD]OK[/TD]
[TD]output should be Check[/TD]
[/TR]
[TR]
[TD]A506[/TD]
[TD]P[/TD]
[TD]CHECK[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A506[/TD]
[TD]P[/TD]
[TD]CHECK[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A506[/TD]
[TD]S[/TD]
[TD]CHECK[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A506[/TD]
[TD]S[/TD]
[TD]CHECK[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A506[/TD]
[TD]S[/TD]
[TD]CHECK[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A506[/TD]
[TD]S[/TD]
[TD]CHECK[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks
chandresh

 

[Finalfight40]

Thanks for your reply , I have applied the formula in my working sheet however its working for some cases. I have added one more id "A501" in the data base now the output comes as "OK".
Ideally it should come as "Check" as there are 2"P" and 1 "S", could you please help.

[TABLE="width: 265"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Type[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A500[/TD]
[TD]S[/TD]
[TD]OK[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A500[/TD]
[TD]S[/TD]
[TD]OK[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A501[/TD]
[TD]P[/TD]
[TD]OK[/TD]
[TD]output should be Check[/TD]
[/TR]
[TR]
[TD]A501[/TD]
[TD]P[/TD]
[TD]OK[/TD]
[TD]output should be Check[/TD]
[/TR]
[TR]
[TD]A501[/TD]
[TD]S[/TD]
[TD]OK[/TD]
[TD]output should be Check[/TD]
[/TR]
[TR]
[TD]A506[/TD]
[TD]P[/TD]
[TD]CHECK[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A506[/TD]
[TD]P[/TD]
[TD]CHECK[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A506[/TD]
[TD]S[/TD]
[TD]CHECK[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A506[/TD]
[TD]S[/TD]
[TD]CHECK[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A506[/TD]
[TD]S[/TD]
[TD]CHECK[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A506[/TD]
[TD]S[/TD]
[TD]CHECK[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks
chandresh

Ah my bad i think i just missed off the = sign where the > signs are:

The corrected formula below:

=IF(SUM(IF(COUNTIFS(A1:A10, A12, B1:B10, "S")>=1, 1, 0)+IF(COUNTIFS(A1:A10, A12, B1:B10, "P")>=1, 1, 0))=2, "CHECK", "OK")

(If you have already adjusted to your range, then you can just add the = sign in the 2 places in red.)

 

[nikio8]

are you sure that rows are correct? in row 2
=IF(COUNTIFS(A:A, A2, B:B, "S")>1, 1, 0)+IF(COUNTIFS(A:A, A2, B:B, "P")>1, 1, 0)
1 ok
2 check
0 not found

 

[Chandresh]

Thanks for your help its working fine now

 

[Finalfight40]

You are welcome

 

[jtakw]

Hi,

A little simpler:

Book1
	A	B	C
1	ID	Type
2	A500	S	OK
3	A500	S	OK
4	A501	P	CHECK
5	A501	P	CHECK
6	A501	S	CHECK
7	A506	P	CHECK
8	A506	P	CHECK
9	A506	S	CHECK
10	A506	S	CHECK
11	A506	S	CHECK
12	A506	S	CHECK
Sheet161

Cell Formulas
Range	Formula
C2	=IF(AND(COUNTIFS(A$2:A$12,A2,B$2:B$12,"S"),COUNTIFS(A$2:A$12,A2,B$2:B$12,"P")),"CHECK","OK")

Formula copied down.