Comparing to Arrays

[Marston]

I have two relatively large 1D arrays (I could make them into a two D
with a dummy column if that would help, but I can't imagine how). Both
arrays contain a series of strings, with each string being exactly the
same length (in this case 18 characters). The characters are numbers
as strings. Each array contains 20,000-30,000 elements.

The challenge is finding a fast way to compare each element in one
array to see if it exists in the second array. I find that if I do
this through any simple stepwise looping it takes forever. Any
suggestions?

 

[Tom Ogilvy]

unless the arrays are sorted, I would think you would have to loop and
compare on each element.

If they were on a worksheet, you could use the countif formula.

 

[Alan Beban]

I have two relatively large 1D arrays (I could make them into a two D
with a dummy column if that would help, but I can't imagine how). Both
arrays contain a series of strings, with each string being exactly the
same length (in this case 18 characters). The characters are numbers
as strings. Each array contains 20,000-30,000 elements.

The challenge is finding a fast way to compare each element in one
array to see if it exists in the second array. I find that if I do
this through any simple stepwise looping it takes forever. Any
suggestions?

What does "forever" mean? Approximately how long does it take?

Alan Beban

 

[Alan Beban]

I have two relatively large 1D arrays (I could make them into a two D
with a dummy column if that would help, but I can't imagine how). Both
arrays contain a series of strings, with each string being exactly the
same length (in this case 18 characters). The characters are numbers
as strings. Each array contains 20,000-30,000 elements.

The challenge is finding a fast way to compare each element in one
array to see if it exists in the second array. I find that if I do
this through any simple stepwise looping it takes forever. Any
suggestions?

And what version of Excel are you using?

Alan Beban

 

[Myrna Larson]

I don't know if this will be faster than looping through the 2nd array, but
you can try it:

Dim Found As Variant
For i = 1 to UBound(Array1)
Found=(Application.Match(Array1(i),Array2,0))
If IsError(Found) Then
'not there
Else
'a match
End If
Next i

 

[Myrna Larson]

BTW, the fastest way to search is a binary search, but it requires that the
array being searched is sorted in ascending order. Is your data sorted? If
not, can you sort it?

I can give you routines for sorting and for a binary search if this approach
is feasible.

 

[Tom Ogilvy]

Sub Tester9()
Dim myarray(1 To 5462)
For i = 1 To UBound(myarray)
myarray(i) = i
Next
res = Application.Match(Int(Rnd() * UBound(myarray)), myarray, 0)
If Not IsError(res) Then
MsgBox res
End If
End Sub

raises a type mismatch error. Match doesn't work with an array with more
than 5461 elements, at least not in xl2000 and below. One reason I didn't
suggest that.

It may be more robust in Excel 2002/3.

 

[Myrna Larson]

Hi, Tom:

I'm using XL 2002 (XP) and your code works fine there. I also tried it with an
array Dim'd 1 to 33,000 and there no problems. It's nice to see that MS has
actually fixed some bugs.

Thanks for the caveat for XL97 and 2000 users.

BTW, in some testing I did with either XL5 or 95, MATCH on a sorted array was
slower than a binary search, but that changed in a later version.

Myrna Larson