How to use one Excel to filter another
You can use functions like vlookup to solve this problem. Although the whole process is not too difficult, as long as the data changes, this process will be repeated every time, which is too cumbersome. VBA can make this process automatic, but the writing method is too complex for ordinary people to master.
It would have been much simpler if you had used esProc. Filtering can be done in just a few lines of code:
A |
B |
D |
|
1 |
=file("param.xlsx").xlsimport() |
=A1.(_1) |
/Open excel，take the first column |
2 |
=file("all.xlsx").xlsimport() |
/Open excel that holds the complete data. |
|
3 |
=A2.select(B1.contain(_2)) |
/ Filter column 2 of A2 with B1, and return qualified rows. |
|
4 |
=file("result.xlsx").xlsexport@A3) |
/ Write result to new excel |
Filtering by multiple columns is also simple. For example, param.xlsx has two columns, and the column names of the first row are cust and seller. To use these two columns to filter all.xlsx, the code should be written as follows:
A |
B |
D |
|
1 |
=file("param.xlsx").xlsimport@t() |
=A1.([cust.seller]) |
/@t indicates that the first row is the column name |
2 |
=file("all.xlsx").xlsimport() |
||
3 |
=A2.select(B1.contain([_2,_3])) |
/Filter columns 2 and 3 of A2 with B1 |
|
4 |
=file("result.xlsx").xlsexport@A3) |
esProc has a grid style desktop IDE. In fact, you can view the calculation result directly in IDE, and you do not have to save it.
For more cases regarding excel calculation, please refer to: Parsing and Exporting Excel Data in SPL
An Easy Way to Merge and Summarize Excel Files An Easy Way to Merge and Summarize Excel Files ]
Text files and excel are processed in the same way in esProc. Many algorithms can refer to each other: Structured Text Computing with esProc
esProc is a professional file processing script. It is easy to install and use, easy to debug, and rich in class libraries. For details, please refer to: http://www.raqsoft.com/html/file-processor.html
Read Getting Started with esProc to download and install esProc, get a license for free and find related documentation.
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_Desktop/
SPL Learning Material 👉 https://c.scudata.com
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProcDesktop
Linkedin Group 👉 https://www.linkedin.com/groups/14419406/
