ranjith2523
Board Regular
- Joined
- Apr 29, 2014
- Messages
- 137
- Office Version
- 365
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.
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
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
Last edited by a moderator: