Remove duplicates and compute intersection

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


A

B

1

20

28

2

30

38

3

30

45

4

45

58

5

58


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


D

1

45

2

58

Use SPL XLL to do this:

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

Picture1png

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

Souce:https://www.reddit.com/r/excel/comments/1cndzmh/how_do_i_find_duplicates_across_2_columns_but_not/