Need Help On Formula _ Again

[ranjith2523]

Hi Friends,

I need you help to validate horizontal data with vertical data.

Hope someone will help me on this data validation.

Client

Object

Char. Value

Characteristic

Material

PLATFORM

MATERIAL_ENTITLEMENT

DOC_ONLY

OS_SS

REQUIRED_OPTIONS

Validation

900

344822-B21

0

REQUIRED_OPTIONS

344822-B21

XSYSTEM

Y

N

S

0

Passed

900

344822-B21

N

DOC_ONLY

BA369AA

XSYSTEM

Y

N

S

0

Passed

900

344822-B21

S

OS_SS

CG369AC

XSYSTEM

Y

N

S

0

Failed

(Since MATERIAL_ENTITLEMENT is N in System)

900

344822-B21

XSYSTEM

PLATFORM

DR370AA

XSYSTEM

N

N

S

0

Failed

(DOC_ONLY Is Missing In System)

900

344822-B21

Y

MATERIAL_ENTITLEMENT

900

BA369AA

0

REQUIRED_OPTIONS

900

BA369AA

XSYSTEM

PLATFORM

900

BA369AA

N

DOC_ONLY

900

BA369AA

N

TYPE_OF_REPLACEMENT

900

BA369AA

S

OS_SS

900

BA369AA

Y

MATERIAL_ENTITLEMENT

900

CG369AC

0

REQUIRED_OPTIONS

900

CG369AC

XSYSTEM

PLATFORM

900

CG369AC

N

DOC_ONLY

900

CG369AC

N

TYPE_OF_REPLACEMENT

900

CG369AC

S

OS_SS

900

CG369AC

N

MATERIAL_ENTITLEMENT

900

DR370AA

0

REQUIRED_OPTIONS

900

DR370AA

XSYSTEM

PLATFORM

900

DR370AA

S

OS_SS

900

DR370AA

N

MATERIAL_ENTITLEMENT

Let me clearly explain my requirement to avoid confusion

1 From Column F to K is the data which we received as standard request to load in system.
2 From Column B to D is the data which we download from system
3 In Column L, we need to apply FORMULA to validate the requested data is match with system.

Here the problem for us is we received the request in Horizontal Format whereas the system output will be in Vertical Format

Lets take an example, the product "344822-B21" will repeat in Column B to have the Char. Value (Column C) like 0,N,S,XYSTEM,Y.
The same product will be there in Column F with the same Char Value in single line (Range G2 to K2)
Here, the column D will have the Characteristic and the same we could see the same characteristic in Range G1 to K1
I am not sure whether I am confusing you but I just wanted to let you know that the data given in request (Column F to K) should match in system (Column B to D).

In L column I would like to apply formula to validate the data.

In my Example, the cell L2 and L3 is passed since the productS "344822-B21", "BA369AA" information from exactly match with the data which given in Column B to D
Cell L3 showing Failed since the material CG369AC is having MATERIAL_ENTITLEMENT - "N" Cell 18 (SYSTEM) where in request H4 given as "Y" for MATERIAL_ENTITLEMENT
Cell L5 also showing Failed since the material "DR370AA" is having DOC_ONLY - "N" in H5 but the Characteristic "DOC_ONLY" missing Column D for the product DR370AA

Is there any way to bring this validation in Excel FORMULA.

Thanks for all your help in advance.

Regards,
Ranjith

 

[cyrilbrd]

Maybe something along:

Book1
	G	H	I	J	K	L	M
1	Material	PLATFORM	MATERIAL_ENTITLEMENT	DOC_ONLY	OS_SS	REQUIRED_OPTIONS	Validation
2	344822-B21	XSYSTEM	Y	N	S	0	Passed
3	BA369AA	XSYSTEM	Y	N	S	0	Passed
4	CG369AC	XSYSTEM	N	N	S	0	Failed
5	DR370AA	XSYSTEM	N	not available	S	0	Failed
ranjith2523

Cell Formulas
Range	Formula
I2:I5	I2	=IFERROR(INDEX(Table_client[Char. Value],MATCH(1,INDEX((Table_client[Object]=[@Material])*(Table_client[Characteristic]=Table_results[[#Headers],[MATERIAL_ENTITLEMENT]]),0),0)),"not available")
J2:J5	J2	=IFERROR(INDEX(Table_client[Char. Value],MATCH(1,INDEX((Table_client[Object]=[@Material])*(Table_client[Characteristic]=Table_results[[#Headers],[DOC_ONLY]]),0),0)),"not available")
K2:K5	K2	=IFERROR(INDEX(Table_client[Char. Value],MATCH(1,INDEX((Table_client[Object]=[@Material])*(Table_client[Characteristic]=Table_results[[#Headers],[OS_SS]]),0),0)),"not available")
L2:L5	L2	=IFERROR(INDEX(Table_client[Char. Value],MATCH(1,INDEX((Table_client[Object]=[@Material])*(Table_client[Characteristic]=Table_results[[#Headers],[REQUIRED_OPTIONS]]),0),0)),"not available")

 

[ranjith2523]

Maybe something along:

Book1
	G	H	I	J	K	L	M
1	Material	PLATFORM	MATERIAL_ENTITLEMENT	DOC_ONLY	OS_SS	REQUIRED_OPTIONS	Validation
2	344822-B21	XSYSTEM	Y	N	S	0	Passed
3	BA369AA	XSYSTEM	Y	N	S	0	Passed
4	CG369AC	XSYSTEM	N	N	S	0	Failed
5	DR370AA	XSYSTEM	N	not available	S	0	Failed
ranjith2523

Cell Formulas
Range	Formula
I2:I5	I2	=IFERROR(INDEX(Table_client[Char. Value],MATCH(1,INDEX((Table_client[Object]=[@Material])*(Table_client[Characteristic]=Table_results[[#Headers],[MATERIAL_ENTITLEMENT]]),0),0)),"not available")
J2:J5	J2	=IFERROR(INDEX(Table_client[Char. Value],MATCH(1,INDEX((Table_client[Object]=[@Material])*(Table_client[Characteristic]=Table_results[[#Headers],[DOC_ONLY]]),0),0)),"not available")
K2:K5	K2	=IFERROR(INDEX(Table_client[Char. Value],MATCH(1,INDEX((Table_client[Object]=[@Material])*(Table_client[Characteristic]=Table_results[[#Headers],[OS_SS]]),0),0)),"not available")
L2:L5	L2	=IFERROR(INDEX(Table_client[Char. Value],MATCH(1,INDEX((Table_client[Object]=[@Material])*(Table_client[Characteristic]=Table_results[[#Headers],[REQUIRED_OPTIONS]]),0),0)),"not available")

Thank you so much for your response and hard work.

The formula which you provided is not working as expected.

I would need to apply formula in COLUMN L to compare and validate the data from COLUMN F to K to COLUMN B to D.

 

[ranjith2523]

Can someone provide your suggestions to achieve the validation.. If it is not possible to achieve through formula please provide your inputs to achieve this.

 

[FatBoyClam]

Enter this as an Array formula (enter the formula in the formula bar and press Ctrl+Shift+Enter)

=IFERROR(IF(AND(INDEX($C$1:$C$100,MATCH(1,($B$1:$B$100=$F2)*($D$1:$D$100=$G$1),0))=$G2,INDEX($C$1:$C$100,MATCH(1,($B$1:$B$100=$F2)*($D$1:$D$100=$H$1),0))=$H2,INDEX($C$1:$C$100,MATCH(1,($B$1:$B$100=$F2)*($D$1:$D$100=$I$1),0))=$I2,INDEX($C$1:$C$100,MATCH(1,($B$1:$B$100=$F2)*($D$1:$D$100=$J$1),0))=$J2,INDEX($C$1:$C$100,MATCH(1,($B$1:$B$100=$F2)*($D$1:$D$100=$K$1),0))=$K2),"Passed", "Failed"),"Failed")

 

[ranjith2523]

Enter this as an Array formula (enter the formula in the formula bar and press Ctrl+Shift+Enter)

=IFERROR(IF(AND(INDEX($C$1:$C$100,MATCH(1,($B$1:$B$100=$F2)*($D$1:$D$100=$G$1),0))=$G2,INDEX($C$1:$C$100,MATCH(1,($B$1:$B$100=$F2)*($D$1:$D$100=$H$1),0))=$H2,INDEX($C$1:$C$100,MATCH(1,($B$1:$B$100=$F2)*($D$1:$D$100=$I$1),0))=$I2,INDEX($C$1:$C$100,MATCH(1,($B$1:$B$100=$F2)*($D$1:$D$100=$J$1),0))=$J2,INDEX($C$1:$C$100,MATCH(1,($B$1:$B$100=$F2)*($D$1:$D$100=$K$1),0))=$K2),"Passed", "Failed"),"Failed")

Thank you so much for your reply.

It is working as expected. I am really interested to learn formulas and help other like you, Can you provide me any suggestion where to start and how to learn it .

 

[FatBoyClam]

Forums such as this have always been the best learning tool for me. Skim through threads if you have time to see what people are doing. Try to think of real-world use cases for the tips you find. Come back and ask for help, ask for explanations if the solutions presented work, but don't make sense.

The above formula breaks down like this - search support.office.com for details on each function

There are 5 INDEX/MATCH functions which look for the 5 Characteristic values matching to the column headers in your right hand table. See here for an explanation of how the Index Match pairing works with multiple criteria (in this case matching the value in the Object Column and the Characteristic column to return the value from the Char. Value column)

These are all in an AND function which takes a set of expressions that need to evaluate to TRUE or FALSE. If all are TRUE, the AND returns TRUE, otherwise it will return false. Research Logical functions for more info (e.g. here)

That AND function is tested in the IF function, which returns "Passed" if the AND function returns TRUE, and "Failed" if not.

Finally, that is wrapped in an IFERROR function that will return "Failed" when the IF function returns an error (in the 4th example, the DOC_ONLY characteristic is missing from the left hand data set, so the INDEX/MATCH function looking for it returns an error, which means the AND function also errors, and the IF function errors)

 

[ranjith2523]

Forums such as this have always been the best learning tool for me. Skim through threads if you have time to see what people are doing. Try to think of real-world use cases for the tips you find. Come back and ask for help, ask for explanations if the solutions presented work, but don't make sense.

The above formula breaks down like this - search support.office.com for details on each function

There are 5 INDEX/MATCH functions which look for the 5 Characteristic values matching to the column headers in your right hand table. See here for an explanation of how the Index Match pairing works with multiple criteria (in this case matching the value in the Object Column and the Characteristic column to return the value from the Char. Value column)

These are all in an AND function which takes a set of expressions that need to evaluate to TRUE or FALSE. If all are TRUE, the AND returns TRUE, otherwise it will return false. Research Logical functions for more info (e.g. here)

That AND function is tested in the IF function, which returns "Passed" if the AND function returns TRUE, and "Failed" if not.

Finally, that is wrapped in an IFERROR function that will return "Failed" when the IF function returns an error (in the 4th example, the DOC_ONLY characteristic is missing from the left hand data set, so the INDEX/MATCH function looking for it returns an error, which means the AND function also errors, and the IF function errors)

Thank you .. You are awesome Very clear explanation.. Hope i will learn soon and start helping peoples.