Problem description:

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

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)

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

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

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