#62 - Search for A Specified String across The Whole Table
esProc Desktop
Problem description & analysis:
We have two Excel sheets, and there are same-structure tables in the two sheets.
Sheet1
| A | B | C | |
| 1 | Col1 | Col2 | Col3 |
| 2 | foo | 1 | NA |
| 3 | bar | 2 | y |
| 4 | baz | 3 | bar |
| 5 | foo | 4 | z |
| 6 | bar | 5 | NA |
| 7 | baz | 6 | foo |
Sheet2
| A | B | C | |
| 1 | Col1 | Col2 | Col3 |
| 2 | foo | 7 | bar |
| 3 | bar | 8 | bar |
| 4 | baz | 9 | bar |
| 5 | foo | 10 | z |
| 6 | bar | 11 | y |
| 7 | baz | 12 | NA |
Task: Search every cell of each table and match their values with a specified string, such as foo. If the matching succeeds, list the whole current row.
| E | F | G | |
| 1 | |||
| 2 | foo | 1 | NA |
| 3 | foo | 4 | z |
| 4 | baz | 6 | foo |
| 5 | foo | 7 | bar |
| 6 | foo | 10 | z |
Solution:
Enter the following formula in SPL XLL:
=spl("=(?1|?2).select(~.contain(?3))",Sheet1!A2:C7,Sheet2!A2:C7,"foo")
As shown in the picture below:
Explanation:
The symbol | is used to concatenate two sequences.
The example was originally on StackOverflow. Feel free to compare the conventional solutions with the SPL approach to grasp the efficiency of esProc SPL. Download esProc Desktop and say hello to a streamlined table! 👋💻✨
SPL download address: esProc Desktop FREE Download
Plugin Installation Method: SPL XLL Installation and Configuration
References to other rich Excel operation cases: Desktop and Excel Data Processing Cases
YouTube FREE courses: SPL Programming
Subscribe to my newsletter
Read articles from esProc Desktop directly inside your inbox. Subscribe to the newsletter, and don't miss out.
Written by
esProc Desktop
esProc Desktop
esProc Desktop is a desktop data processing & analytics tool and is specifically designed for ordinary business people. It supports complex computations & spreadsheet data manipulation, can deal with tasks that are hard to accomplish in Excel, and in addition, generates more concise code than VBA and Python. esProc Desktop boasts all-around programming capabilities, supports multi-step interactive data analytics and is easy to use without configurations. This significantly lowers the technical threshold for data analytics.