DEV Community

Cover image for #93 — Intersection, Union and Difference in the Case of Row-Based Data — Two Sets — by Key Column
Judith-Excel-Sharing
Judith-Excel-Sharing

Posted on

#93 — Intersection, Union and Difference in the Case of Row-Based Data — Two Sets — by Key Column

Problem description:

The following tables list the sales data of the top 10 products by sales in January and February:

source table 1

source table 2

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)
Enter fullscreen mode Exit fullscreen mode

result table 1

  • 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)
Enter fullscreen mode Exit fullscreen mode

result table 2

  • 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)
Enter fullscreen mode Exit fullscreen mode

result table 3


Download esProc Desktop for FREE and enhance your workflow today!!! 🚀🔥⬇️

✨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

Top comments (1)

Collapse
 
judith677 profile image
Judith-Excel-Sharing

Feel free to share your thoughts ad feedback with us🫶🏻🙌🏻

Discord
Reddit