SOLVED
Home

Identify ID's meeting multiple criteria (Condition#1: Corn,Tomato - Cndition#2 starts with "P"

Highlighted
rcriceinc
Occasional Contributor

Identify ID's meeting multiple criteria (Condition#1: Corn,Tomato - Cndition#2 starts with "P"

This is probably easier than I think but at 4:30 in the morning not thinking clearly.

 

Test#1 = ID = "Corn" and "Tomato"
Test #2 = ID = (starts with "P")
Both test conditions should return a "T" or "F" result (Boolean is OK)
  
ConditionID
Apple000001
Berry000001
Coconut000001
Corn000001
Date000001
Melon000001
Orange000001
Peach000001
Pear000001
Tomato000001
Apple000002
Berry000002
Coconut000002
Date000002
Melon000002
Orange000002
Peach000002
10 Replies

Re: Identify ID's meeting multiple criteria (Condition#1: Corn,Tomato - Cndition#2 starts with &quot

Hello

 

=OR(A2="Corn",A2="Tomato")
=LEFT(A2)="P"

 

RE: Identify ID's meeting multiple criteria (Condition#1: Corn,Tomato - Cndition#2 starts with "P"

Does not work, remember, the ID values occur multiple times with different conditions and I want to identify ID's only where both conditions are true, not ID's where either condition is =T. Same thing for the second one (All = "P" start).

Re: RE: Identify ID's meeting multiple criteria (Condition#1: Corn,Tomato - Cndition#2 starts with &

Sorry, you were not very clear.

 

=AND(OR(A2="Corn",A2="Tomato"),LEFT(A2)="P")

 

Re: RE: Identify ID's meeting multiple criteria (Condition#1: Corn,Tomato - Cndition#2 starts with &

Don't know how to be more clear. The "And" in test #1 should have been clear. Both conditions must exist before a "T" result is returned. If both conditions are not true, then return "F".

 

Test#1 = ID = "Corn" and "Tomato"
test condition should return a "T" or "F" result (Boolean is OK) 

 

Treat test#2 seperately using the same data.

 

EX:

ID#1 occurs multiple times with multiple conditions.

Return "F" unless an ID has instances with both Corn and Tomato

Re: RE: Identify ID's meeting multiple criteria (Condition#1: Corn,Tomato - Cndition#2 starts with &

Just see the attached file.

 

I corrected formula in D column only.

 

=AND(OR(B2="Corn",B2="Tomato",LEFT(B2,1)="P"))

Confirm whether i understood your question. Hope, I can solve next one too.

Re: RE: Identify ID's meeting multiple criteria (Condition#1: Corn,Tomato - Cndition#2 starts with &

I think it needs an array of some kind. I submit an updated excel sample with comments add that may clarify. The basic problem with your sample is that we are not searching for a single cell value but if x instances of an ID occur, and two or more conditions are met, flag the entire ID subset as suspect "T" or "F" identifer. Thanks! 

Re: RE: Identify ID's meeting multiple criteria (Condition#1: Corn,Tomato - Cndition#2 starts with &

Next guess.

 

=IF(SUM(COUNTIFS([ID],[@ID],[Condition],{"Tomato";"Corn"}))>1,"T","F")

 

Solution

Re: RE: Identify ID's meeting multiple criteria (Condition#1: Corn,Tomato - Cndition#2 starts with &

Hi, @rcriceinc

 

For Test Condition 1, apply @Detlef Lewin 's formula

 

=IF(SUM(COUNTIFS([ID],[@ID],[Condition],{"Tomato";"Corn"}))>1,"T","F")

For Test Condition 2, use this.

 

=IF(COUNTIFS([ID],[@ID],[Condition],"P*")>=1,"T","F")

Hope both will solve your problem.

Re: RE: Identify ID's meeting multiple criteria (Condition#1: Corn,Tomato - Cndition#2 starts with &

Thank you!!!

 

I have to give it an actual test on my daily data feed results, but using my simple test file data, looks like both formulas works perfectly, just as intended. I see no reason why it will not work equally well on my current project.

 

I see the trick now, putting Tomato and Corn in an array. Very neat. I need to work harder on increasing my knowledge of array usage.

 

 

Thanks again on this great response. 

Re: RE: Identify ID's meeting multiple criteria (Condition#1: Corn,Tomato - Cndition#2 starts with &

I will test this tomorrow and get back to you. Thanks!

Related Conversations
Availability
Yinghua Zeng  in  System Center AMA  on
6 Replies
Conditional policies in Azure AD vs. Intune
Robert Woods  in  Microsoft Intune  on
8 Replies
Add a Room to a Teams meeting location
Jose Miguel Sanchez  in  Microsoft Teams  on
2 Replies
Condition Access Question
Vineet Arora  in  Microsoft Intune  on
7 Replies