#93 — Intersection, Union and Difference in the Case of Row-Based Data — Two Sets — by Key Column
esProc Desktop
Problem description:
The following tables list the sales data of the top 10 products by sales in January and February:
Solutions:
Use SPL XLL to tackle the following tasks respectively:
A. Find out the sales data of the products that make the top 10 in both January and February (the ranking and sales in January are required only):
=spl("=[E(?1),E(?2)].merge@oi(ProductName)",Jan!A1:C11,Feb!A1:C11)
B. Find out the sales data of products that make the top 10 once or more (the ranking and sales that appear for the first time are required only):
=spl("=[E(?1),E(?2)].merge@ou(ProductName)",Jan!A1:C11,Feb!A1:C11)
C. Find out the sales data of products that make the top 10 in January but fail to make the top 10 in February:
=spl("=[E(?1),E(?2)].merge@od(ProductName)",Jan!A1:C11,Feb!A1:C11)
Download esProc Desktop for FREE and take your skills to the next level!!! 🚀🔥⬇️
✨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.