Hello,
See the below data example.
Purpose: Output a 'Y' or 'N' based on a look-up. If the data is found in the columns in the look-up, a 'Y' is output, else 'N'.
This is probably a IF/THEN type logic, but your assistance is greatly appreciated!
Logic/Rules:
1) If the look-up matches 'Account Name' in Column A of the 'Data' tab to 'Account Name' in the 'ReClass' tab, output a 'Y'. If the value of 'Y' is matched, no other work is required, but if the look-up doesn't find a match, the values of 'ID' and 'VER' need to be checked.
Examples:
- Row 2 outputs a 'Y' because 'Account Name' is found in the look-up column. When this occurs, no additional checks are needed.
- Row 3 would output a ' N' because 'Account Name' is NOT found in the look-up column. When this occurs, move on the to the next checks. The 'ID' or 'VER' is not found either so a value of 'N' is output.
- Row 4 would output a 'Y' because 'Account Name' is NOT found in the look-up column. When this occurs, move on the to the next checks. The 'ID' was found so a value of 'Y' is output.
When the first search doesn't find a match, check.....
It is possible to have a 'Y' value when the 'ID' and 'VER' fields have values found in the look-up, however, there will be situations where the 'VER' field is found in the look-up, but the 'ID' is not, and thus, outputs a 'N' value so these need to be checked independently else this would over count.
2) IF Column B ('ID') in 'Data' tab is found in the look-up in Column B in the 'ReClass' tab, output a 'Y' in Column D in the 'Data' tab. If it's not found, move to the next set of logic for a 2nd chance.
Examples:
- Account Name was not found in the look-up, but Row 5 of the 'Data' tab matches on Column B (ID) to Column B of the 'ReClass' tab. Since this value matches, the evaluation is complete and a 'Y' is output in Column D of the 'Data' tab.
- Account Name was not found in the look-up, but Row 7 of the 'Data' tab matches on Column B (ID) in 'Data' tab. Since this value matches, the evaluation is complete and a 'Y' is output in Column D of the 'Data' tab.
- Row 6 and 8 had different ID values that were not found in the look-up so output a 'N' value.
3) Check Column C (Ver) column. If this field's value is found in the lookup, a 'Y' is output in Column C of the 'Data' tab.
Examples:
- Row 9 output a 'Y'. Although the 'ID' field (Column B) is blank and the account name also didn't match, Column C ('Ver') is found in the look-up.
- Row 10 outputs a 'Y'. Although the 'ID' field (Column B) is present it isn't found in the look-up and the account name also didn't match. However, Column C ('Ver') is found in the look-up.
- Row 11 outputs a 'N'. Neither the Account Name and 'ID' field are found in the look-up. Column C ('Ver') is also not found in the look-up.
Data Tab:
<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
ReClass Tab:
<colgroup><col><col span="2"></colgroup><tbody>
</tbody>
See the below data example.
Purpose: Output a 'Y' or 'N' based on a look-up. If the data is found in the columns in the look-up, a 'Y' is output, else 'N'.
This is probably a IF/THEN type logic, but your assistance is greatly appreciated!
Logic/Rules:
1) If the look-up matches 'Account Name' in Column A of the 'Data' tab to 'Account Name' in the 'ReClass' tab, output a 'Y'. If the value of 'Y' is matched, no other work is required, but if the look-up doesn't find a match, the values of 'ID' and 'VER' need to be checked.
Examples:
- Row 2 outputs a 'Y' because 'Account Name' is found in the look-up column. When this occurs, no additional checks are needed.
- Row 3 would output a ' N' because 'Account Name' is NOT found in the look-up column. When this occurs, move on the to the next checks. The 'ID' or 'VER' is not found either so a value of 'N' is output.
- Row 4 would output a 'Y' because 'Account Name' is NOT found in the look-up column. When this occurs, move on the to the next checks. The 'ID' was found so a value of 'Y' is output.
When the first search doesn't find a match, check.....
It is possible to have a 'Y' value when the 'ID' and 'VER' fields have values found in the look-up, however, there will be situations where the 'VER' field is found in the look-up, but the 'ID' is not, and thus, outputs a 'N' value so these need to be checked independently else this would over count.
2) IF Column B ('ID') in 'Data' tab is found in the look-up in Column B in the 'ReClass' tab, output a 'Y' in Column D in the 'Data' tab. If it's not found, move to the next set of logic for a 2nd chance.
Examples:
- Account Name was not found in the look-up, but Row 5 of the 'Data' tab matches on Column B (ID) to Column B of the 'ReClass' tab. Since this value matches, the evaluation is complete and a 'Y' is output in Column D of the 'Data' tab.
- Account Name was not found in the look-up, but Row 7 of the 'Data' tab matches on Column B (ID) in 'Data' tab. Since this value matches, the evaluation is complete and a 'Y' is output in Column D of the 'Data' tab.
- Row 6 and 8 had different ID values that were not found in the look-up so output a 'N' value.
3) Check Column C (Ver) column. If this field's value is found in the lookup, a 'Y' is output in Column C of the 'Data' tab.
Examples:
- Row 9 output a 'Y'. Although the 'ID' field (Column B) is blank and the account name also didn't match, Column C ('Ver') is found in the look-up.
- Row 10 outputs a 'Y'. Although the 'ID' field (Column B) is present it isn't found in the look-up and the account name also didn't match. However, Column C ('Ver') is found in the look-up.
- Row 11 outputs a 'N'. Neither the Account Name and 'ID' field are found in the look-up. Column C ('Ver') is also not found in the look-up.
Data Tab:
| Account Name | ID | VER | Match |
| ATT | ATT01 | XXX | Y |
| SNAP | SNAP01 | XXX | N |
| TRAP | TRAP01 | XXX | Y |
| AAA | CIT111 | XXX | Y |
| STAT | STAT111 | YYY | N |
| STAT | STAT112 | YYY | Y |
| STAT | STAT113 | YYY | N |
| TOB | COM | Y | |
| TOB | TOB1A | COM | Y |
| TAT | TOB1C | XXX | N |
| ATT | STAT112 | COM | Y |
| ATT | STAT113 | COM | Y |
| ATT | STAT113 | XXX | Y |
<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
ReClass Tab:
| Account Name | ID | VER |
| ATT | TRAP01 | ZZZ |
| BCD | CIT111 | YYY |
| ECF | STAT112 | COM |
| DGDE | TOB1D | BXZ |
| DRDS | TRAP02 | ZXB |
| DED | TRAP03 | AZF |
<colgroup><col><col span="2"></colgroup><tbody>
</tbody>
