ranjith2523
Board Regular
- Joined
- Apr 29, 2014
- Messages
- 137
- Office Version
- 365
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.
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
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