DEV Community

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

Posted on

#94 — Intersection, Union, and Difference in the Case of Row-Based Data — Two Sets — by Whole Row

Problem description:

The following tables list the data of the products and salespersons that make the top 10 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 data of products and salespersons that make the top 10 in both January & February.
=spl("=[E(?1),E(?2)].merge@oi()",Jan!B1:C11,Feb!B1:C11)
Enter fullscreen mode Exit fullscreen mode

result table 1

  • B. Find out the data of the products and salespersons that make the top 10 once or more.
=spl("=[E(?1),E(?2)].merge@ou()",Jan!B1:C11,Feb!B1:C11)
Enter fullscreen mode Exit fullscreen mode

result table 2

  • C. Find out the data of products and salespersons that make the top 10 in January but fail to make the top 10 in February:
=spl("=[E(?1),E(?2)].merge@od()",Jan!B1:C11,Feb!B1:C11)
Enter fullscreen mode Exit fullscreen mode

result table 3

Notes:
The merge()function without parameter means the whole row will be taken as the matching criterion, and the merge() function with parameter means the parameter value will be taken as the matching criterion.


Download esProc Desktop for FREE and make your data work smarter for you!!! 🚀🔥⬇️

✨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)

Some comments may only be visible to logged-in visitors. Sign in to view all comments.