Comparing Variant Arrays

[Brock_Hardchest]

Hello,

I'm comparing two arrays to each other. If the element in the first array is in the second array then it loads a third array with the number "243". The first array is set to loop through the first 1000 elements right now and the second array has 95,000 elements to check which takes 27 seconds to complete the loop. During actual application the first array will loop 1 million elements and the second array will have 95,000 elements. Any ideas on how to make it faster?

For i = 1 To 1000
    For j = 1 To UBound(x243, 1)
        If lookup2(i, 1) = x243(j, 1) Then
            TypeArray(i, 1) = 243
            Exit For
            
        End If
    Next j
    
Next i

'write array to type column
Range("U2:U" & BottomRow).Value = TypeArray

 

[Fluff]

You could load the values in the x243 array into a dictionary & then compare the lookup2 array to the dictionary. That way you only loop through each set of data once.

 

[Fluff]

Further thought, what version of Excel are you using?

 

[Fluff]

Ignore post#3, what I was thinking of wont work.

But using a dictionary, you could do something like

   With CreateObject("scripting.dictionary")
      For i = 1 To UBound(x243)
         .Item(x243(i, 1)) = Empty
      Next i
      For i = 1 To UBound(Ary1)
         If .Exists(lookup2(i, 1)) Then TypeArray(i, 1) = 243
      Next i
   End With

 

[Brock_Hardchest]

This is great! Comparing a million cells to the dictionary only takes 5 seconds. I am assuming the first For loop is assigning the x243 array contents to the first column in the dictionary while also assigning the second column of the dictionary to nothing or as it says "Empty". Is that what is happening?

 

[Fluff]

A dictionary isn't like a normal array in that it doesn't have columns as such, instead it's got keys & items.
Other than that you have understood correctly.