pratheesh1983
Board Regular
- Joined
- Aug 13, 2015
- Messages
- 57
- Office Version
- 365
- Platform
- Windows
I have a data set that contains data that has field tags, and each tag has the value mentioned. We need an Excel rule to check if a name mentioned in one field exists in another field. I have tried to explain the requirements in the table below. In the last column, I need to put a formula that will populate the value as "yes" if the name is available and "no" if the name is not available.
In some cases, if the name mentioned in the second field is missing one or two words from the name mentioned in the first field, it should also populate the value as Yes (refer to row two in the table). In those cases, it should also populate the value as Yes (refer to row two in the table), and this is only applicable for names that have more than three words. Also, in some cases, the AT19 field has special characters, but in the AT20 field, the name won't have special characters. In those cases, the formula should be able to consider the name as the same as mentioned in AT19. I know the conditions look quite complex, but I would appreciate it if anyone could help me define a rule for them. Thanks in advance.
In some cases, if the name mentioned in the second field is missing one or two words from the name mentioned in the first field, it should also populate the value as Yes (refer to row two in the table). In those cases, it should also populate the value as Yes (refer to row two in the table), and this is only applicable for names that have more than three words. Also, in some cases, the AT19 field has special characters, but in the AT20 field, the name won't have special characters. In those cases, the formula should be able to consider the name as the same as mentioned in AT19. I know the conditions look quite complex, but I would appreciate it if anyone could help me define a rule for them. Thanks in advance.
| Text Value | AT19
| AT20 (Field Content) | AT19 NAME IS AVAIABLE IN AT20-YES OR NO (formula column) | |||
| :AT01: 20303030 :AT02: HAMMER :AT19: MARKA, ANNA-MARIA AT20: 6785.89763.5005
|
| 6785.89763.5005
| YES | |||
| :AT01: 45678393 :AT02: HOLLOW :AT19: MARIA BLANCA ESCOBAR ALVARO :AT20: RTE MARIA BLANCA ESCOBAR FEB2002 | MARIA BLANCA ESCOBAR ALVARO | RTE MARIA BLANCA ESCOBAR FEB2002 | YES | |||
| :AT01: 23783 :AT02: PIILLOW :AT19: ANTONIA-FERNANDA, RITA :AT20: RTE ANTONIA FERNANDA RITA FEB2002 | ANTONIA-FERNANDA, RITA | RTE ANTONIA FERNANDA RITA FEB2002 | YES | |||
| :AT01: 267833 :AT02: POR :AT19: ANTHONY, JULIAN :AT20: THEGTTS FEB2002 | ANTHONY, JULIAN |
| NO |
