How to tell if 2 arrays share the same element.

Question

So this is a simpler form of my problem. Lets say I have 2 arrays. A= {1,2} and B={2,4,6}. If A and B share an element then delete that element from B. I know you can loop through and compare each element in A to each element in B, but there's got to be a better way!

Answer 1

No there is not I think. You have to loop through.

Answer 2

If your arrays are sorted (or you can sort the arrays), you can then work through both arrays simultaneously. Starting at the beginning of both arrays and going until you would advance one of the pointers beyond the end of its respective array:

• If a < b then advance the a pointer
• If a = b then delete the element at b
• If a > b then advance the b pointer

Answer 3

If values inside a array are unique for its array, you could make the array indexes the actual values, allowing you to seek directly to its index, instead of scanning the entire array.

Start with the array that has fewer elements, and i assume you are wanting to do excel/VB? I made a picture to illustrate the idea.

http://img694.imageshack.us/img694/1503/hackmap.jpg

Instead of having two nested loops, you have one loop, and it only iterates as many times as the smallest array.

Answer 4

You have to write code, but it doesn't have to be a brute-force doubly nested loop and you don't have to do any messy removal of individual elements from arrays.

If you add a reference to the Microsoft Scripting Runtime (from the Tools menu in the VBE), you can use a Dictionary object to make this easier. It has methods for 'Exists', 'Remove', and 'Keys'. So you could loop through B and add the elements as keys in the Dictionary, and then loop through A, checking to see if those elements exist, and if so, removing them.

As pseudocode:

for each elem in b
    dict(elem)=0 
next elem

for each elem in a
    if dict.exists(elem)
        dict.remove(elem)
    end if
next elem

return dict.keys

The above approach also removes duplicates from B if there are any.

If you know your arrays don't have error values as elements, you can also use MATCH (and in VBA 'Application.Match' or 'Application.WorksheetFunction.Match'). Doing something like

=MATCH({2,4,6},{1,2},0)

will return {2,#N/A,#N/A}. Any position with #N/A is the position of an element of B that wasn't in A. If you do the match in the worksheet, you can then drag a formula like

=IF(ISNA(cell of match),corresponding cell of B,NA())

and then filter out the #N/As from that. In VBA, you could do (more pseudocode):

matches=application.match(b,a,0) 

for each elem in matches
    if iserror(elem)
        add corresponding element of b to result
    end
next elem

redim result to new smaller size

return result

Of course, then you have to worry about array starting bounds, etc.