IF Statement Help Please :)

[jeffj13]

Hello, appreciate any help provided!

I want to create a statement that returns a yes or no response if certain criteria are met in another column. However, I want to only apply this argument within the same cell values of the corresponding ID. For the example below, for ID 123 I want to check if both "DI" and "OL" exist in the service code column. But I only want to check this for 123 and for the formula to switch the test for 456. I would like it to return no if " DI" and "OL" are present and yes if only "DI" is present. Not sure how to apply this to only the same ID in the first column without crossing over into the next ID. I should also add that the ID's are completely random and I never know exactly what numbers will be used.

ID	Service Code
123	RE
123	DI
123	OL
456	RE
456	DI

 

[Fluff]

What should be returned if neither value are present?
Also what should be returned if only OL exists?

 

[jeffj13]

What should be returned if neither value are present?
Also what should be returned if only OL exists?

Sorry for leaving that out. IF "RE" and "DI" then no. If only "DI" then yes. Any other condition outside of those two would be no. I added "OL" as an example, however it could be another 2 digit alpha character. Hopefully that makes sense.

 

[Fluff]

Thanks for that, I also forgot to ask what version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[jeffj13]

Thanks for that, I also forgot to ask what version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I am using Excel 2016 and I added that to my account details, thanks!

 

[Fluff]

Thanks for that.
How about

+Fluff 1.xlsm
	A	B	C
1	ID	Service Code
2	123	RE	no
3	123	DI	no
4	123	OL	no
5	456	OL	yes
6	456	DI	yes
7	789	OL	no
8	789	RE	no
9	789	AB	no
10
Data

Cell Formulas
Range	Formula
C2:C9	C2	=IF(COUNTIFS($A$2:$A$1000,A2,$B$2:$B$1000,"DI"),IF(COUNTIFS($A$2:$A$1000,A2,$B$2:$B$1000,"RE"),"no","yes"),"no")

 

[steve the fish]

If it doesnt matter what the other two digit code is but only that another exists then you could go:

=IF(COUNTIFS(A:A,A2,B:B,"DI")=COUNTIFS(A:A,A2),"Yes","No")

 

[jeffj13]

Thanks for that.
How about

+Fluff 1.xlsm
	A	B	C
1	ID	Service Code
2	123	RE	no
3	123	DI	no
4	123	OL	no
5	456	OL	yes
6	456	DI	yes
7	789	OL	no
8	789	RE	no
9	789	AB	no
10
Data

Cell Formulas
Range	Formula
C2:C9	C2	=IF(COUNTIFS($A$2:$A$1000,A2,$B$2:$B$1000,"DI"),IF(COUNTIFS($A$2:$A$1000,A2,$B$2:$B$1000,"RE"),"no","yes"),"no")

Thank you both, this worked!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[steve the fish]

Weird. Not sure it works regarding the opening post and post number 3.