DEV Community

Cover image for #34 — Remove Duplicates And Compute Intersection
Judith-Excel-Sharing
Judith-Excel-Sharing

Posted on

#34 — Remove Duplicates And Compute Intersection

Problem description & analysis:

An Excel table has two columns of numbers, and there are duplicates in a column:

original table

Task: First remove duplicates from the columns and then compute their intersection:

desired table

Solution:

Use SPL XLL to do this:

=spl("=[?1,?2].isect(~.group().select(~.len()==1).conj())",A1:A5,B1:B4)
Enter fullscreen mode Exit fullscreen mode

As shown in the picture below:

result table with code entered

Explanation:

isect()computes the intersection. group() groups data but does not perform aggregation. select() performs filtering operation; ~ is the current member.

Top comments (2)

Collapse
 
judith677 profile image
Judith-Excel-Sharing

If you’re interested in more tips and tricks or need help with specific Excel tasks, feel free to reach out:
🤍Discord: discord.gg/hgbKEvJ4
🤍Reddit: reddit.com/r/esProc_Desktop/
Your problem might even be featured in our #ExcelHack series!

Collapse
 
judith677 profile image
Judith-Excel-Sharing

Please feel free to download SPL XLL and explore the potential of it ⬇️

💠SPL download address: scudata.com/download-Desktop

💠Plugin Installation Method: c.scudata.com/article/1652061135502

💠References to other rich Excel operation cases: c.raqsoft.com/article/1651916536524

💠SPL Programming (YouTube FREE courses): youtube.com/playlist?list=PLQeR-Ih...