#62 - Search for A Specified String across The Whole Table
esProc
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 directly inside your inbox. Subscribe to the newsletter, and don't miss out.
Written by
esProc
esProc
esProc SPL is a JVM-based programming language designed for structured data computation, serving as both a data analysis tool and an embedded computing engine. FREE download👉🏻: https://www.esproc.com/download-esproc