May 20 2017
04:57 AM
- last edited on
Jul 25 2018
09:37 AM
by
TechCommunityAP
May 20 2017
04:57 AM
- last edited on
Jul 25 2018
09:37 AM
by
TechCommunityAP
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) | |
Condition | ID |
Apple | 000001 |
Berry | 000001 |
Coconut | 000001 |
Corn | 000001 |
Date | 000001 |
Melon | 000001 |
Orange | 000001 |
Peach | 000001 |
Pear | 000001 |
Tomato | 000001 |
Apple | 000002 |
Berry | 000002 |
Coconut | 000002 |
Date | 000002 |
Melon | 000002 |
Orange | 000002 |
Peach | 000002 |
May 20 2017 05:26 AM
Hello
=OR(A2="Corn",A2="Tomato") =LEFT(A2)="P"
May 20 2017 06:02 AM
May 20 2017 07:02 AM
Sorry, you were not very clear.
=AND(OR(A2="Corn",A2="Tomato"),LEFT(A2)="P")
May 20 2017 08:02 AM
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
May 20 2017 07:17 PM
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.
May 21 2017 12:15 AM
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!
May 21 2017 01:19 AM
Next guess.
=IF(SUM(COUNTIFS([ID],[@ID],[Condition],{"Tomato";"Corn"}))>1,"T","F")
May 21 2017 05:28 AM
SolutionHi, @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.
May 21 2017 06:01 AM
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.
May 22 2017 07:34 PM
I will test this tomorrow and get back to you. Thanks!
May 21 2017 05:28 AM
SolutionHi, @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.