ranjith2523
Board Regular
- Joined
- Apr 29, 2014
- Messages
- 137
- Office Version
- 365
Hi Friends,
I need you help to validate horizontal data with vertical data.
Hope someone will help me on this data validation.
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
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