Not intersect

3 followers
0
Avatar

Trying to figure out how I could take 2 lists of data, let's say [1, 2, 3, 4, 5] & [1, 2, 3, 5] and return the NON-intersecting values. In this case I need the final result to be [4] NOT the intersecting values of [1, 2, 3, 5].

I'm not seeing any formulas that address exactly this so I'm thinking it's going to be a multiple step process, and I'm trying to figure out the best way to approach it.

Thanks!

Brian Miller

3 comments

  • Avatar
    Alan Mark

    Hi Brian,

    I would suggest having the two lists on separate sheets and then using a Left Outer Join to get a list of items that only exists in the left table.  Then do a Right Outer Join on the same data to get a list of items that only exists in the right table.  Finally, you can union those two result sheets.  I believe this should give you the set that you're looking for.

    Alan

    0
    Comment actions Permalink
  • Avatar
    Brian Miller

    Hi Alan - Forgive me if I am misunderstanding, but if I am performing any join on a list, it's going to treat that full list as one variable, correct? So if I am performing outer joins, it will either return the full list when it is an exact match or it will return null because there is one tiny difference somewhere in there from list A to list B.

    In the example above, it won't return the [4] specifically. I believe the results that will return will just be null because List A does not equal exactly List B.

    0
    Comment actions Permalink
  • Avatar
    Joel Stewart

    Brian, you're correct that the Join approach takes the full records into account. One method to a element-by-element match is to use the INTERSECT and REMOVEALL functions to take the intersection and then remove the intersecting set from the LIST to produce the results that you're seeking.

    0
    Comment actions Permalink
Please sign in to leave a comment.