Need Help On Formula

[ranjith2523]

Hi Friends,

I am trying to perform some validation in excel and i need your help on formula. Let me very clearly explain my need for a better understanding and to avoid confusion.

Confble object	Profile Name	Org.area	Status config. prof.	Std class	Class	Status	Material MG4		Requested Products	Validation
344822-B21	300	S	1		W_HPQ_HW	1	JW		344822-B21	Passed
344822-B21	300	S	1		W_HPQ_SW	1	JW		BA369AA	Passed
BA369AA	300	S	1		W_HPQ_SW	1	SW		P26865-B21	Passed
P26865-B21	300	S	1		W_HPQ_HW	1	HW		RA531AA	Failed	(W_HPQ_SW Missing)
RA531AA	300	S	1		W_HPQ_HW	1	JW		XE544A1	Failed	W_HPQ_SW It should be there since it is HW product
XE544A1	300	S	1		W_HPQ_SW	1	HW		CE465A1	Failed	W_HPQ_HW It should be there since it is SW product
CE465A1	300	S	1		W_HPQ_HW	1	SW		BE809A1	Failed	Status is 0 hence it should Fail
BE809A1	300	S	1		W_HPQ_HW	0	HW

1 We have the data from Column A to H (System Result)

2 In Column J, we have the list of requested products.

3 In Column K, We need to apply FORMULA to perform validation.


Logical Checks


1 When the Material MG4 (Column H) is HW, then the Material Class (Column F) should only have W_HPQ_HW and the status (Column G) should be 1. Example - The Material "P26865-B21" MG4 for this product is HW with Class W_HPQ_HW and Status 1 hence the validation is Passed in Cell K3.

2 When the Material MG4 (Column H) is SW, then the Material Class (Column F) should only have W_HPQ_SW and the status (Column G) should be 1. Example - The Material "BA369AA" MG4 for this product is HW with Class W_HPQ_SW and Status 1 hence the validation is Passed in Cell K2.

3 When the Material MG4 (Column H) is JW, then the Material Class (Column F) should only have W_HPQ_SW and W_HPQ_HW the status (Column G) should be 1. Example - The Material "344822-B21" MG4 for this product is JW with Class W_HPQ_SW and W_HPQ_HW with Status 1 hence the validation is Passed in Cell K1.

4 The Material "RA531AA" result Failed since the Material MG4 is JW and W_HPQ_SW is Missing.

5 The Material "XE544A1" result Failed since the Material MG4 is HW and W_HPQ_HW is exists in Column F.

6 The Material "CE465A1" result Failed since the Material MG4 is SW and W_HPQ_HW is exists in Column F.

7 The Material "BE809A1" result Failed since the Status (column G) is 0


Basically the logic is if the Material MG4 is HW then it should have only the class W_HPQ_HW with Status 1
if the Material MG4 is SW then it should have only the class W_HPQ_SW with Status 1
if the Material MG4 is JW then it should have only the class W_HPQ_HW and W_HPQ_HW with Status 1

Hope I have clearly explained the expectation about how the formula should work, as usual I have tried by my own but failed hence seeking for expert help.


Best Regards,
Ranjith

 

[FatBoyClam]

if you're on an Office 365 subscription and are uptodate, you can use the IFS function;

=IFS(COUNTIFS(A:A,J3,G:G,0)>0,"Failed",AND(INDEX(H:H,MATCH(J3,A:A,0))="SW",COUNTIFS(A:A,J3,F:F,"W_HPQ_SW")>0),"Passed",AND(INDEX(H:H,MATCH(J3,A:A,0))="HW",COUNTIFS(A:A,J3,F:F,"W_HPQ_HW")>0),"Passed",AND(COUNTIFS(A:A,J3,F:F,"W_HPQ_SW")>0,COUNTIFS(A:A,J3,F:F,"W_HPQ_HW")>0),"Passed",TRUE,"Failed")

If not, a set of nested IF statements would do it, although personally I'd set up a series of columns each with one test and then one column to determine the pass/fail state based on the combination of tests, just because it's easier to read and troubleshoort.

=IF(COUNTIFS(A:A,J3,G:G,0)>0,"Failed",IF(AND(INDEX(H:H,MATCH(J3,A:A,0))="SW",COUNTIFS(A:A,J3,F:F,"W_HPQ_SW")>0),"Passed",IF(AND(INDEX(H:H,MATCH(J3,A:A,0))="HW",COUNTIFS(A:A,J3,F:F,"W_HPQ_HW")>0),"Passed",IF(AND(COUNTIFS(A:A,J3,F:F,"W_HPQ_SW")>0,COUNTIFS(A:A,J3,F:F,"W_HPQ_HW")>0),"Passed","Failed"))))

I'd like to think there's a more elegant way, perhaps one of the others has a better idea.

 

[CSmith]

Book1
	A	B	C	D	E	F	G	H	I	J	K
77	Confble object	Profile Name	Org.area	Status config. prof.	Std class	Class	Status	Material MG4		Requested Products	Validation
78	344822-B21	300	S	1		W_HPQ_HW	1	JW		344822-B21	Passed
79	344822-B21	300	S	1		W_HPQ_SW	1	JW		BA369AA	Failed
80	BA369AA	300	S	1		W_HPQ_SW	1	SW		P26865-B21	Passed
81	P26865-B21	300	S	1		W_HPQ_HW	1	HW		RA531AA	Passed
82	RA531AA	300	S	1		W_HPQ_HW	1	JW		XE544A1	Passed
83	XE544A1	300	S	1		W_HPQ_SW	1	HW		CE465A1	Failed
84	CE465A1	300	S	1		W_HPQ_HW	1	SW		BE809A1	Failed
85	BE809A1	300	S	1		W_HPQ_HW	0	HW
Sheet1

Cell Formulas
Range	Formula
K78:K84	K78	=IF(OR(G78*(H78=RIGHT(F78,2)),G78*AND(H78="JW","HW"=RIGHT(F78,2))),"Passed","Failed")

 

[FatBoyClam]

@CSmith - your formula does not result in the desired outcome for input values "BA369AA" which should have Passed or "RA531AA" or "XE544A1" both of which should have failed.

You're not accounting for the fact that products potentially have 2 lines in the table from Column A to H (where the Material is JW, there should be two lines, one for each value of Class), so after row 78 you're not looking at the right rows in the data table

 

[CSmith]

Basically the logic is if the Material MG4 is HW then it should have only the class W_HPQ_HW with Status 1
if the Material MG4 is SW then it should have only the class W_HPQ_SW with Status 1
if the Material MG4 is JW then it should have only the class W_HPQ_HW and W_HPQ_HW with Status 1

 

[ranjith2523]

if you're on an Office 365 subscription and are uptodate, you can use the IFS function;

=IFS(COUNTIFS(A:A,J3,G:G,0)>0,"Failed",AND(INDEX(H:H,MATCH(J3,A:A,0))="SW",COUNTIFS(A:A,J3,F:F,"W_HPQ_SW")>0),"Passed",AND(INDEX(H:H,MATCH(J3,A:A,0))="HW",COUNTIFS(A:A,J3,F:F,"W_HPQ_HW")>0),"Passed",AND(COUNTIFS(A:A,J3,F:F,"W_HPQ_SW")>0,COUNTIFS(A:A,J3,F:F,"W_HPQ_HW")>0),"Passed",TRUE,"Failed")

If not, a set of nested IF statements would do it, although personally I'd set up a series of columns each with one test and then one column to determine the pass/fail state based on the combination of tests, just because it's easier to read and troubleshoort.

=IF(COUNTIFS(A:A,J3,G:G,0)>0,"Failed",IF(AND(INDEX(H:H,MATCH(J3,A:A,0))="SW",COUNTIFS(A:A,J3,F:F,"W_HPQ_SW")>0),"Passed",IF(AND(INDEX(H:H,MATCH(J3,A:A,0))="HW",COUNTIFS(A:A,J3,F:F,"W_HPQ_HW")>0),"Passed",IF(AND(COUNTIFS(A:A,J3,F:F,"W_HPQ_SW")>0,COUNTIFS(A:A,J3,F:F,"W_HPQ_HW")>0),"Passed","Failed"))))

I'd like to think there's a more elegant way, perhaps one of the others has a better idea.

Thank your so much for your response.

But unfortunately it is not working as expected and got the below result. Actually the row 3 should not get fail since it is Material "P26865-B21" MG4 is HW and the class it "W_HPQ_HW" with Status 1.

Requested Products	Validation
344822-B21	Passed
BA369AA	Passed
P26865-B21	Failed
RA531AA	Failed
XE544A1	Failed
CE465A1	Failed
BE809A1	#N/A

 

[ranjith2523]

Book1
	A	B	C	D	E	F	G	H	I	J	K
77	Confble object	Profile Name	Org.area	Status config. prof.	Std class	Class	Status	Material MG4		Requested Products	Validation
78	344822-B21	300	S	1		W_HPQ_HW	1	JW		344822-B21	Passed
79	344822-B21	300	S	1		W_HPQ_SW	1	JW		BA369AA	Failed
80	BA369AA	300	S	1		W_HPQ_SW	1	SW		P26865-B21	Passed
81	P26865-B21	300	S	1		W_HPQ_HW	1	HW		RA531AA	Passed
82	RA531AA	300	S	1		W_HPQ_HW	1	JW		XE544A1	Passed
83	XE544A1	300	S	1		W_HPQ_SW	1	HW		CE465A1	Failed
84	CE465A1	300	S	1		W_HPQ_HW	1	SW		BE809A1	Failed
85	BE809A1	300	S	1		W_HPQ_HW	0	HW
Sheet1

Cell Formulas
Range	Formula
K78:K84	K78	=IF(OR(G78*(H78=RIGHT(F78,2)),G78*AND(H78="JW","HW"=RIGHT(F78,2))),"Passed","Failed")

Thank you so much for your response but actually it is not working as per the expectation.

I got the below result when i apply the formula.

FatBoyClam correctly mentioned that "the fact that products potentially have 2 lines in the table" for JW. Hope i will get some better solution

Validation

Passed

Failed

Passed

Passed

Passed

Failed

Failed

 

[ranjith2523]

Thank your so much for your response.

But unfortunately it is not working as expected and got the below result. Actually the row 3 should not get fail since it is Material "P26865-B21" MG4 is HW and the class it "W_HPQ_HW" with Status 1.

Requested Products	Validation
344822-B21	Passed
BA369AA	Passed
P26865-B21	Failed
RA531AA	Failed
XE544A1	Failed
CE465A1	Failed
BE809A1	#N/A

I just modified the cell from J3 to J2 in formula and it is working as Expected. Really very sorry for the confusion caused.

 

[FatBoyClam]

@CSmith - I'm confused.

How exactly does your formula test for the requirement of having "W_HPQ_HW" and "W_HPQ_SW" on two separate lines, when the Material MG4 value is "JW"?

@ranjith2523 - the results you posted in #8 do not appear to match your requirements.