██FR█████ █INTELL███████████

#34 — Remove Duplicates And Compute Intersection


This content originally appeared on DEV Community and was authored by Judith-Excel-Sharing

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)

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.


This content originally appeared on DEV Community and was authored by Judith-Excel-Sharing