Identify ID's meeting multiple criteria

[rcriceinc]

Task: Identify ID's meeting multiple criteria:

Use same test data for both tests (or combine both test under 1 formula if you are an Excel god)

(Test #1: If(ID has condition =Corn and Tomato Then Return "T", else "F")
Test #2: If(ID has condition =Corn and Tomato Then Return "T", else "F")starts with "P"

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



Test#1 = ID Condition = "Corn" and "Tomato"
Test #2 Condition = ID = (starts with "P")
Both condition tests 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

 

[AlanSidman]

I don't understand your second test. Please explain in simple English what you are trying to achieve. Don't show a formula but explain it to me as if we were standing in a coffee shop line.

 

[vletm]

rcriceinc
Maybe later, after You have slept well, You would think again few things?
Condition-column has only one 'fruit'.
ID = six numbers (text) ...
"P" hmm like 'Peach row'?
#1) When Your 'ID has condition =Corn and Tomato' would be TRUE?
#2) Condition = ID = (starts with "P")
Try to write, what would You like to get without ... formula.

 

[rcriceinc]

Task: Identify ID's meeting multiple criteria:

Use same test data for both tests (or combine both test under 1 formula if you are an Excel god)

test 1 and 2 are independent tests related to the same data-set The attached workbook shows the entire data-set and column positions. In both test cases I am trying to identify ID groupings that only meet a very specific range of criterion, but more than just a single value. In test #1 (=T tomato "AND" corn) are not fruits and this condition would warrant review. If an ID could only match on tomato "OR" corn, it does not qualify (=F).
Test #2 is a simple variant that searches in a similar fashion using a less limited criteria range.

#1
(Test #1: If(ID has condition =Corn and Tomato Then Return "T", else "F")

Design a test that can look at a column of ID's (Column B), which contains multiple instances of ID's due to variations in condition (column A) and return a T/F string
#2
Test #2: If(ID has condition =Corn and Tomato Then Return "T", else "F")starts with "P"

Design a test that can look at a column of ID's (Column B), which occur multiple times due to variation of condition (column A) and return a T/F string only if multiple conditions, related to a single ID, contains values that starts with the letter "P"

Sample ID 1 qualifies a "T" for test#1 (contains both corn and tomato but ID 2 would not (contains 1 or less references to tomato or corn) once the condition count reaches 2 (T/T , C/C, C/T) or more test 1 reslut would be "T"

row id condition
1 1 berry
2 1 Corn
3 1 Tomato
4 2 Peach
5 2 Pear
6 2 Corn

The reverse is true for test 2 ID 2 qualifies (contains a value starting with "P" but "F" for test 1 as contains only a reference to corn OR tomato.

 

[rcriceinc]

Task: Identify ID's meeting multiple criteria:
Use same test data for both tests (or combine both test under 1 formula if you are an Excel god)

test 1 and 2 are independent tests related to the same data-set The attached workbook shows the entire data-set and column positions. In both test cases I am trying to identify ID groupings that only meet a very specific range of criterion, but more than just a single value. In test #1 (=T tomato "AND" corn) are not fruits and this condition would warrant review. If an ID could only match on tomato "OR" corn, it does not qualify (=F).
Test #2 is a simple variant that searches in a similar fashion using a less limited criteria range.

#1
(Test #1: If(ID has condition =Corn and Tomato Then Return "T", else "F")

Design a test that can look at a column of ID's (Column B), which contains multiple instances of ID's due to variations in condition (column A) and return a T/F string
#2
Test #2: If(ID has condition =Corn and Tomato Then Return "T", else "F")starts with "P"
Design a test that can look at a column of ID's (Column B), which occur multiple times due to variation of condition (column A) and return a T/F string only if multiple conditions, related to a single ID, contains values that starts with the letter "P"

Sample ID 1 qualifies a "T" for test#1 (contains both corn and tomato but ID 2 would not (contains 1 or less references to tomato or corn) once the condition count reaches 2 (T/T , C/C, C/T) or more test 1 reslut would be "T"

row id condition
1 1 berry
2 1 Corn
3 1 Tomato
4 2 Peach
5 2 Pear
6 2 Corn

The reverse is true for test 2 ID 2 qualifies (contains a value starting with "P" but "F" for test 1 as contains only a reference to corn OR tomato.

I don't understand your second test. Please explain in simple English what you are trying to achieve. Don't show a formula but explain it to me as if we were standing in a coffee shop line.

 

[rcriceinc]

attached is an updated file with comments added. This may add clarity to the two different test requirements.

 

[NARAYANK991]

Hi ,

Can you please simplify your posts ?

1. The file you uploaded has data in column B , consisting of single words , such as Apple , Berry , Coconut , Corn , Date.

It is not clear whether this data is just something you have put together for this upload , or whether it is the actual real-life data you are going to work with.

2. You wish to test each cell , starting from B2 , through to B86 , for two conditions :

• Whether the cell in column B is either Corn or Tomato
• Whether the cell in column B starts with the letter P

It is clear that both the above conditions cannot be satisfied at the same time.

So , it can only happen that a cell in column B satisfies the first condition or it satisfies the second condition.

So what exactly do you want should happen if either of the above conditions is satisfied ?

In which column should the formula be entered , and what should be the output ?

It would help if you could manually put in a few output values in the desired cells , so that it is easier to verify whether the thought of formula will return the required result or not.

Narayan

 

[rcriceinc]

Maybe you did not see the updated worksheet.

2. You wish to test each cell , starting from B2 , through to B86 , for two conditions :

• Whether the cell in column B is either Corn or Tomato
• Whether the cell in column B starts with the letter P

It is clear that both the above conditions cannot be satisfied at the same time.

***(your premise is incorrect as both conditions are met by ID# 000001, out of ten records (C2:C11) with 10 distinct conditions (B2:B11) , 4 records contain one each of the 4 test conditions
(test 1 = corn,tomato (B5,B11) and test#2 =Peach,Pear (B9:B10))

all 10 instances of ID# 000001 should = "T" (for both column "D" and "E" test because ID 000001 failed both validation tests #1 and #2.
So , it can only happen that a cell in column B satisfies the first condition or it satisfies the second condition. Incorrect: The test is not on the cell condition in column "B". The test is on the otder ID grouping level. The conditions (B2:B11) must be checked for and met within each specific ID grouping level, using the conditions in column "B".

So what exactly do you want should happen if either of the above conditions is satisfied ?

• Whether the two or more cells in column B related by ID# 000001 is either Corn or Tomato any combination
• Test #1 (Corn/Corn or Tomato/Tomato or Tomato/Corn)
• Test #2 (Starts with "P")

"D" will house the formula for test condition #1
"E" will house the formula for test condition #2
return value "T" if for [ID] condition test is true, else "F"


In which column should the formula be entered , and what should be the output ?

"D" will house the formula for test condition #1
"E" will house the formula for test condition #2
return value "T" if for [ID] condition test is true, else "F"

comments and expected results for record ID's 000001 - 000004 as shown in workbook.

 

[rcriceinc]

Potential solution provided in excel workbook. Test#1 seems to require use of array formula while test 2 was simpler.

Feel free to test for logic flaws or to offer simplified or alternate formula versions

 

[rcriceinc]

see potential solution noted

 

[rcriceinc]

see potential solution noted.

 

[NARAYANK991]

Hi ,

Shouldn't this array formula work ?

=(SUMPRODUCT(--(IF(Table1[ID]=Table1[@ID],Table1[Condition])="Corn"))>0) * (SUMPRODUCT(--(IF(Table1[ID]=Table1[@ID],Table1[Condition])="Tomato")) > 0) * (SUMPRODUCT(--(LEFT(IF(Table1[ID]=Table1[@ID],Table1[Condition]),1)="P"))>0)

The first part checks whether there is at least one instance of an ID which has Corn as the condition ; the second part checks whether there is at least one instance of an ID which has Tomato as the condition ; the third part checks whether there is at least one instance of an ID which has P as the starting character as the condition.

Only when all three conditions are satisfied , will the result be 1.

Narayan