Again Need Help On Formula

[ranjith2523]

Hi Friends,

I need your help on formula to validate the product offer price combination.

I tried to modify "IF(INDEX("RANGE",MATCH" formula which provided by an expert FatBoyClam for another validation, but I'm failed it really a very challenging for me, hope some experts can help me out.

Condition Type: ZM03

Material(Goods)

Main_Item(Offer)

Rate with Unit/Curr

Currency

by

Eid

Del

Valid From

Valid To

Validation

Products

Start Eff. Date

HA151AC

HA155BC

HA360AC

HA156AC

HA158AC

R1N92A

HA151AC

25

USD

1

02/03/2020

12/31/9999

Passed

R1N92A

02/03/2020

25

18

R1N92A

HA360AC

16

USD

1

02/03/2020

12/31/9999

Failed

(Price Mismatch - It should be 18 as per request)

R1N96A

02/03/2020

12

R1N96A

HA151AC

12

USD

1

02/03/2020

12/31/9999

Passed

R1N97A

02/03/2020

12

R1N97A

HA155BC

12

USD

1

02/03/2018

12/31/9999

Failed

(Valid From Date Mismatch With Start Eff. Date)

R1N98A

02/03/2020

13

R1N98A

HA151AC

13

USD

1

02/03/2020

12/31/9999

Passed

R1N99A

02/03/2020

12

1

2

12

R1N99A

HA360AC

1

USD

1

02/03/2020

12/31/9999

Missing

For Product "R1N99A" HA158AC price given in request but missing in system result

R1N00A

02/03/2020

19

R1N99A

HA151AC

12

USD

1

02/03/2020

12/31/9999

Missing

R1N99A

HA156AC

2

USD

1

02/03/2020

12/31/9999

Missing

R1N00A

HA156AC

12

USD

1

02/03/2020

12/31/9999

Failed

(Price Mismatch - It should be 19 as per request)

R1N00A

HA155BC

6

USD

1

02/03/2020

12/31/9999

Failed

(No Price for R1N00A HA155BC - as per request)

I tried using "IF(INDEX("RANGE",MATCH" formula to perform validation but I'm failed it really a very challenging for me, hope some experts can help me out.

I will try to explain the as better as I can to avoid confusion and for better understanding.

Range L2 to R8 will have the request data which we load in system
And, Range A2 to I10 is the system report
From J3 to J10, we need to APPLY FORMULA to validate and ensure that the system data match with the request.

Criteria

Material (Column A) and Main_Item(Offer) and Rate (Column C) should be validated with Product (Column L) and Offer Range N2 to U2 with price range (N2 to R8) also the Column H value should match with Column M

Validation Logic

1 The result should be "Passed" in validation if Material, Good, Offer and Valid From date is same in system result as per request.
2 The result should be "Failed" in validation if there is any mismatch in Price or Start effective date.
3 The result should be "Missing" if price given in request but not exists in the submission. E.g. The product "R1N99A" have HA158AC with price 12 but it is missing in system result.

I am not sure whether the 3rd Validation logic can be done through formula. If 3rd logic is not possible through formula then please make the result as PASSED as per the validation logic 1

Regards,
Ranjith

 

[jasonb75]

There appear to be some conflicts in your notes and descriptions as to what is required. It is not clear if the existing records in J2:J11 are from an incorrect formula, or if they are the expected results.

Based on

Validation Logic

1 The result should be "Passed" in validation if Material, Good, Offer and Valid From date is same in system result as per request.
2 The result should be "Failed" in validation if there is any mismatch in Price or Start effective date.
3 The result should be "Missing" if price given in request but not exists in the submission. E.g. The product "R1N99A" have HA158AC with price 12 but it is missing in system result.

does this help?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Material(Goods)	Main_Item(Offer)	Rate with Unit/Curr	Currency	by	Eid	Del	Valid From	Valid To	Validation		Products	Start Eff. Date	HA151AC	HA155BC	HA360AC	HA156AC	HA158AC
2	R1N92A	HA151AC	25	USD	1			02/03/2020	12/31/9999	Passed		R1N92A	02/03/2020	25		18
3	R1N92A	HA360AC	16	USD	1			02/03/2020	12/31/9999	Failed		R1N96A	02/03/2020	12
4	R1N96A	HA151AC	12	USD	1			02/03/2020	12/31/9999	Passed		R1N97A	02/03/2020		12
5	R1N97A	HA155BC	12	USD	1			02/03/2018	12/31/9999	Failed		R1N98A	02/03/2020	13
6	R1N98A	HA151AC	13	USD	1			02/03/2020	12/31/9999	Passed		R1N99A	02/03/2020	12		1	2	12
7	R1N99A	HA360AC	1	USD	1			02/03/2020	12/31/9999	Passed		R1N00A	02/03/2020				19
8	R1N99A	HA151AC	12	USD	1			02/03/2020	12/31/9999	Passed
9	R1N99A	HA156AC	2	USD	1			02/03/2020	12/31/9999	Passed
10	R1N00A	HA156AC	12	USD	1			02/03/2020	12/31/9999	Failed
11	R1N00A	HA155BC	6	USD	1			02/03/2020	12/31/9999	Missing
Sheet3

Cell Formulas
Range	Formula
J2:J11	J2	{=IFERROR(INDEX({"Passed","Failed","Missing"},MATCH(IFNA(INDEX($N$2:$R$7,MATCH(A2&"|"&H2,$L$2:$L$7&"|"&$M$2:$M$7,0),MATCH(B2,$N$1:$R$1,0)),"F"),CHOOSE({1,2,3},C2,"F",0),0)),"Failed")}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[ranjith2523]

There appear to be some conflicts in your notes and descriptions as to what is required. It is not clear if the existing records in J2:J11 are from an incorrect formula, or if they are the expected results.

Based ondoes this help?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Material(Goods)	Main_Item(Offer)	Rate with Unit/Curr	Currency	by	Eid	Del	Valid From	Valid To	Validation		Products	Start Eff. Date	HA151AC	HA155BC	HA360AC	HA156AC	HA158AC
2	R1N92A	HA151AC	25	USD	1			02/03/2020	12/31/9999	Passed		R1N92A	02/03/2020	25		18
3	R1N92A	HA360AC	16	USD	1			02/03/2020	12/31/9999	Failed		R1N96A	02/03/2020	12
4	R1N96A	HA151AC	12	USD	1			02/03/2020	12/31/9999	Passed		R1N97A	02/03/2020		12
5	R1N97A	HA155BC	12	USD	1			02/03/2018	12/31/9999	Failed		R1N98A	02/03/2020	13
6	R1N98A	HA151AC	13	USD	1			02/03/2020	12/31/9999	Passed		R1N99A	02/03/2020	12		1	2	12
7	R1N99A	HA360AC	1	USD	1			02/03/2020	12/31/9999	Passed		R1N00A	02/03/2020				19
8	R1N99A	HA151AC	12	USD	1			02/03/2020	12/31/9999	Passed
9	R1N99A	HA156AC	2	USD	1			02/03/2020	12/31/9999	Passed
10	R1N00A	HA156AC	12	USD	1			02/03/2020	12/31/9999	Failed
11	R1N00A	HA155BC	6	USD	1			02/03/2020	12/31/9999	Missing
Sheet3

Cell Formulas
Range	Formula
J2:J11	J2	{=IFERROR(INDEX({"Passed","Failed","Missing"},MATCH(IFNA(INDEX($N$2:$R$7,MATCH(A2&"|"&H2,$L$2:$L$7&"|"&$M$2:$M$7,0),MATCH(B2,$N$1:$R$1,0)),"F"),CHOOSE({1,2,3},C2,"F",0),0)),"Failed")}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Thank you so much for getting this done so quickly. YES I CAN STRONGLY SAY IT HELPS FOR ME !!!

Really very sorry for the confusion caused.

If you look into the product "R1N99A" cell L6. This part number has 4 price (HA151AC - 12, HA360AC - 1, HA156AC - 2 and HA158AC - 12). If you look the same product in column A Range A7 to A9, the combination of R1N99A with HA158AC is missing. That is the reason i marked as Missing in my table.

You perfectly brought Missing for R1N00A with HA155BC in cell J11 since it is missing in range L to R, my concern is the cell J11 can be Failed since the price 6 not provided in range L to R.

The requirement is, even if single price is missing in range A to I for a product (e.g. R1N99A HA158AC Price 12 is missing) then MISSING comment should appear all rows for the product instead of Passed or Failed.

If this cannot be done through formula, please leave it I can manage with the formula which you provided.

Thanks a log again.

 

[jasonb75]

Having given it some thought, I don't think that what you ask is possible with the data layout that you have.

The formula is looking for entries in the list on the left that are missing from the table on the right, to look for entries in the table that are missing from the list, you would need to convert the table to a list, then use formulas to test the new list.

 

[jasonb75]

After my last post I went back for one last attempt. See if this gives the results that you're expecting, (needs to be array confirmed with Shift Ctrl Enter).

=IF(SUM(COUNTIFS($A$2:$A$11,A2,$B$2:$B$11,IF(INDEX($N$2:$R$7,MATCH(A2,$L$2:$L$7,0),0)<>"","Ignore this",$N$1:$R$1))),"Failed",IFERROR(INDEX({"Passed","Failed","Missing"},MATCH(IFNA(INDEX($N$2:$R$7,MATCH(A2&"|"&H2,$L$2:$L$7&"|"&$M$2:$M$7,0),MATCH(B2,$N$1:$R$1,0)),"F"),CHOOSE({1,2,3},C2,"F",0),0)),"Failed"))

 

[ranjith2523]

After my last post I went back for one last attempt. See if this gives the results that you're expecting, (needs to be array confirmed with Shift Ctrl Enter).

=IF(SUM(COUNTIFS($A$2:$A$11,A2,$B$2:$B$11,IF(INDEX($N$2:$R$7,MATCH(A2,$L$2:$L$7,0),0)<>"","Ignore this",$N$1:$R$1))),"Failed",IFERROR(INDEX({"Passed","Failed","Missing"},MATCH(IFNA(INDEX($N$2:$R$7,MATCH(A2&"|"&H2,$L$2:$L$7&"|"&$M$2:$M$7,0),MATCH(B2,$N$1:$R$1,0)),"F"),CHOOSE({1,2,3},C2,"F",0),0)),"Failed"))

Thank you and really sorry for the delay in response.