If Formula

[eramirez148]

I need a formula that will give me the following results in column D. If there is multiple cells with in column A and column B and C show active for any of the repetative cells or non repetative cells then the results should be "OK" otherwise the results should be "Violation."

	Product name	CBIS	SAP	Results
​	Protein Yogurt	Active	Active	OK
2​	talian Beef	Active	Active	OK
3​	Chicken Drum	Active		Violation
4​	Chicken Drum	Active		Violation
5​	Chicken Drumsticks	Active		Violation
6​	Trail Mix	Active		OK
7​	Trail Mix	Active	Active	OK
8​	Grains Chicken	Active	Active	OK
9​	Popcorn	Active	Active	OK
10​	Red Pepper	Active	Active	OK
11​	Buns	Active	Active	OK

 

[awoohaw]

wait. Can you explain the difference with row 5 and row 6?

 

[awoohaw]

row 6 and 3 seem to be the same scenario? Please explain more.

 

[eramirez148]

Row five does not have a repetitive row and it also does not have "active" matching in column B and C.
Whereas row 6 repeats in row 7. And row 7 has "Active" match in column B and C. So I want the formula to also capture repetitive rows that contain "active" match in column B and C

 

[eramirez148]

I need a formula that will give me the following results in column D. If there is multiple cells with in column A and column B and C show active for any of the repetative cells or non repetative cells then the results should be "OK" otherwise the results should be "Violation."

	Column A	Column B	Column C	Column D
​	Protein Yogurt	Active	Active	OK
2​	talian Beef	Active	Active	OK
3​	Chicken Drum	Active		Violation
4​	Chicken Drum	Active		Violation
5​	Chicken Drumsticks	Active		Violation
6​	Trail Mix	Active		OK
7​	Trail Mix	Active	Active	OK
8​	Grains Chicken	Active	Active	OK
9​	Popcorn	Active	Active	OK
10​	Red Pepper	Active	Active	OK
11​	Buns	Active	Active	OK

 

[awoohaw]

I think i understand that. Will you ever have column B blank, and column C "Active"?

 

[dalvin647]

Here's a super janky formula that works up to 200 rows.

=IF(AND(ISNUMBER(SEARCH("Active",TEXTJOIN(",",TRUE,IF($A$2:$A$200=A2,$B$2:$B$200,"")))),ISNUMBER(SEARCH("Active",TEXTJOIN(",",TRUE,IF($A$2:$A$200=A2,$C$2:$C$200,""))))),"OK","Violation")

Paste it in D2. Good luck!

 

[eramirez148]

Hi dalvin657, this formula worked perfect. However, I came into another issue. If column B and C matched "Inactive" text can we have the formula also read these and the result be "OK"

 

[eramirez148]

Here's a super janky formula that works up to 200 rows.

=IF(AND(ISNUMBER(SEARCH("Active",TEXTJOIN(",",TRUE,IF($A$2:$A$200=A2,$B$2:$B$200,"")))),ISNUMBER(SEARCH("Active",TEXTJOIN(",",TRUE,IF($A$2:$A$200=A2,$C$2:$C$200,""))))),"OK","Violation")

Paste it in D2. Good luck!

View attachment 92254

Hi dalvin657, this formula worked perfect. However, I came into another issue. If column B and C matched "Inactive" text can we have the formula also read these and the result be "OK"

 

[eramirez148]

I think i understand that. Will you ever have column B blank, and column C "Active"?

Hello, yes there will be instances where column B is blank and column C active.

A different user provided the following formula below and it worked out out great. However, I ran into some instances were column B and C are both blank in such instances I want the result to be "OK" will this be possible to include?

=IF(AND(ISNUMBER(SEARCH("Active",TEXTJOIN(",",TRUE,IF($A$2:$A$200=A2,$B$2:$B$200,"")))),ISNUMBER(SEARCH("Active",TEXTJOIN(",",TRUE,IF($A$2:$A$200=A2,$C$2:$C$200,""))))),"OK","Violation")