Check Whether Each Value of a Specific Column Contains All Words in a Specified Cell
Problem description & analysis
Below is Excel file book1.xlsx:
| A |
B |
|
| 1 |
Systems Engineer |
Systems Product Engineer |
| 2 |
Systems Manager |
|
| 3 |
Manager Product |
|
| 4 |
Engineer Product |
|
| 5 |
Engineer Systems |
|
| 6 |
Systems Engineer |
|
| 7 |
Engineer Product Systems |
We are trying to check whether a column B value contains all words in A1 and return yes or no to corresponding column C. Below is the desired result:
| A |
B |
C |
|
| 1 |
Systems Engineer |
Systems Product Engineer |
yes |
| 2 |
Systems Manager |
no |
|
| 3 |
Manager Product |
no |
|
| 4 |
Engineer Product |
no |
|
| 5 |
Engineer Systems |
yes |
|
| 6 |
Systems Engineer |
yes |
|
| 7 |
Engineer Product Systems |
yes |
Solution & explanation
Load add-in ExcelRaq.xll to Excel, and
Enter the following formula in cell C1: =esproc("=if(?1.words()\?2.words()==[],""yes"",""no"")",$A$1,B1), and drag value of C1 down to C7 to get all results.
SPL Official Website 👉 http://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc
SPL Learning Material 👉 http://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/ydhVnFH9
Youtube 👉 https://www.youtube.com/@esProc_SPL
