Finding common elements across arrays in VBA

[wizuriel]

Hello.

So having some trouble comparing datasets in VBA and hoping can find a more efficient way of doing this.

I have my datasheet with a bunch of ID's and donation amounts. What I need to do is search in N workbooks to see if any previous years have any of those ID's and develop a new dataset with the common ID's from the primary year.

So for example if in 2012 I have
1
2
3

I need to see if those ID's appear in 2009-2011 data (so 1 might appear in all 3, but 2 might only appear in 2010 and 3 doesn't appear in any).

So right now I have taken this years dataset and put it in a matrix. I have than loaded the other years data and stored it into a dictionary that holds a matrix of the dataset. Each dataset seems to be around 6k-30k unique unsorted entries.

What I have right now:

    For Each k In funreports.keys 'funreports is my dictionary that holds the matrix of each previous years data
        conarray = Application.index(funreports(k), 0, conidcol) 'getting an array of just the ID's
        counter = 2
        For x = 2 To max 'looping through this years dataset
            mycon = fundraisingdata(x, conidcol) 'ID I'm working with
            mymatch = Application.Match(mycon, conarray, False)
            If (IsError(mymatch)) Then GoTo nextfor 'if no match, than continue
                If Not (data.exists(mycon)) Then 'creating the new dataset with the common ID's
                    ReDim foo(Size)
                    data.Add (mycon), foo
                    data(mycon)(1) = CDbl(fundraisingdata(x, verasiedcol))
                    If (fundraisingdata(x, mobilecol) <> Empty) Then
                        data(mycon)(2 + funreports.Count) = 1
                    Else
                        data(mycon)(2 + funreports.Count) = 0
                    End If
                End If
                data(mycon)(counter) = CDbl(funreports(k)(mymatch, verasiedcol))
                If (funreports(k)(mymatch, mobilecol) <> Empty) Then
                    data(mycon)(counter + 1 + funreports.Count) = 1
                Else
                    data(mycon)(counter + 1 + funreports.Count) = 0
                End If
nextfor:
        Next x
        counter = counter + 1
    Next k

Now this code does work, but it runs very slow. I can in fact manually just copy each dataset into Excel and use VLookUp faster than it takes my code to execute . So figure that means there has to be a better way of doing this.

Also asked here

 

[Jerry Sullivan]

Hi and Welcome to the Board,

It looks like you have one level of redundant comparison steps.

The outer loop steps through each key in funreports.keys and
1. Makes conarray of funreports ID's
2. Goes to each ID in fundraisingdata and compares it to the entire conarray

That outer loop appears unnecessary and if that's true, that section of the code is taking 6K-30K times longer to run than needed.

 

[wizuriel]

unfortunately I think the loop is needed. Each key in funreports is used to access a different previous years workbook of data.

for example key 1 could be 2011 data and key 2 would be 2010 data. I need to compare every previous year with the current year and find the intersections with the current years ID's.