I often run into the problem of having to compare two lists in Excel, to see what items are in the list to **look for** that can’t be found in the list to **look in**.

So when I saw Dick Kusleika’s post on Daily Dose of Excel I thought it was time I got my act together and worked out the best way of tackling this problem.

I decided to write a VBA UDF called **IsInList2** that called 6 methods:

- Sorting the the LookIn list and using Binary Search to compare the items in the LookFor list
- Using linear search of the LookIn list for each item in the LookFor list
- Create a Collection containing the LookIn list and check it for each item in the LookFor list
- Create a Dictionary containing the LookIn list and check it for each item in the LookIn list
- Use an already sorted LookIn List and Binary Search
- Look for partial matches using InStr

### Overview of the IsInList 2 UDF

The **IsInList2** UDF is written as an array function returning an array of True/False. Its designed to be called as a multi-cell array function entered in the column next to the LookFor list, so that you can Filter for False to find all the items in the LookFor list that don’t exits in the LookIn list.

For simplicity the UDF is written assuming that both lists are Ranges with at least 2 items: so the first task is to get the values from the Ranges into variant arrays.

(I have ignored the optimisation of using MATCH directly on the Range).

Then the output array is created as the smaller of the calling cells and the the LookFor list

Then if its an exact match the data is either Sorted, added to a Collection or to a Dictionary

Then the function iterates through the LookFor list using the appropriate method subroutine and stores the result in the output array

### The QuickSort Sub

Here is the code for a QuickSort of a variant containing an array. The Quicksort would be substantially faster if it was strongly typed and handled a vector rather than a 1-column matrix.

Sub QSortVar(InputValues As Variant, jStart As Long, jEnd As Long) Dim jStart2 As Long Dim jEnd2 As Long Dim v1 As Variant Dim v2 As Variant jStart2 = jStart jEnd2 = jEnd ' ' choose random pivot ' v1 = InputValues((jStart + (jEnd - jStart) * Rnd()), 1) While jStart2 < jEnd2 While InputValues(jStart2, 1) < v1 And jStart2 < jEnd jStart2 = jStart2 + 1 Wend While InputValues(jEnd2, 1) > v1 And jEnd2 > jStart jEnd2 = jEnd2 - 1 Wend If jStart2 < jEnd2 Then v2 = InputValues(jStart2, 1) InputValues(jStart2, 1) = InputValues(jEnd2, 1) InputValues(jEnd2, 1) = v2 End If If jStart2 <= jEnd2 Then jStart2 = jStart2 + 1 jEnd2 = jEnd2 - 1 End If Wend If jEnd2 > jStart Then QSortVar InputValues, jStart, jEnd2 If jStart2 < jEnd Then QSortVar InputValues, jStart2, jEnd End Sub

### The Binary Search Function

For simplicity this function is also written to handle a variant containing an array, and would be faster if more strongly typed.

Its not designed as a UDF: its called from IsInList2.

Function BSearchMatch(LookupValue As Variant, LookupArray As Variant) As Boolean ' ' use binary search to find if lookupvalue exists in lookuparray ' Dim low As Long Dim high As Long Dim middle As Long Dim varMiddle As Variant Dim jRow As Long ' jRow = 1 BSearchMatch = False low = 0 high = UBound(LookupArray) Do While high - low > 1 middle = (low + high) \ 2 varMiddle = LookupArray(middle, 1) If varMiddle >= LookupValue Then high = middle Else low = middle End If Loop ' If (low > 0 And high <= UBound(LookupArray)) Then If LookupArray(high, 1) > LookupValue Then jRow = low Else jRow = high End If End If If LookupValue = LookupArray(jRow, 1) Then BSearchMatch = True End Function

### The Exact Linear Search Function

LMatchExactV is not designed as a UDF: its called from IsInList2.

Function LMatchExactV(LookupValue As Variant, LookupArray As Variant) As Boolean ' ' use linear search to find if LookupValue exists in LookupArray ' LookupArray must be a a 2-dimensional variant array of N rows and 1 column ' Dim j As Long ' LMatchExactV = False For j = 1 To UBound(LookupArray) If LookupValue = LookupArray(j, 1) Then LMatchExactV = True Exit For End If Next j End Function

### The Partial Match Linear Search Function

This function use InStr to check for partial match. It is not designed as a UDF: its called from IsInList2.

Function LMatchInV(LookupValue As Variant, LookupArray As Variant) As Boolean ' ' use linear search and Instr to find if LookupValue is within any value in LookupArray ' LookupArray must be a a 2-dimensional variant array of N rows and 1 column ' Dim j As Long Dim strLook As String ' LMatchInV = False strLook = CStr(LookupValue) For j = 1 To UBound(LookupArray) If InStr(LookupArray(j, 1), strLook) > 0 Then LMatchInV = True Exit For End If Next j End Function

### The IsInList2 Array Function

Because the function uses the Dictionary Object from VBScript you need to **add a reference to the Microsoft Scripting Runtime**.

The Function takes 2 optional parameters which control the method used:

**jSorted** – which Sort/Lookup?Match method to use

**FindExact** – True for an exact match, False for a partial match

Public Function IsInList2(LookFor As Variant, LookIn As Variant, Optional jSorted As Long = 0, Optional FindExact As Boolean = True) ' ' jSorted ' =0 data not sorted but sort it ' =1 data already sorted - use binary search ' =-1 use linear search ' =2 use collection ' =3 use dictionary ' Dim nLookFor As Long Dim nLookIn As Long Dim nOut As Long Dim vOut() As Variant Dim j As Long Dim coll As New Collection Dim dict As New dictionary ' ' coerce ranges to values ' LookFor = LookFor.Value2 LookIn = LookIn.Value2 ' ' get row counts ' nLookFor = UBound(LookFor) nLookIn = UBound(LookIn) nOut = Application.Caller.Rows.Count ' If nLookFor < nOut Then nOut = nLookFor ReDim vOut(nOut, 1) ' create output array ' If FindExact Then If jSorted = 0 Then QSortVar LookIn, LBound(LookIn), UBound(LookIn) ' quicksort jSorted = 1 ElseIf jSorted = 2 Then On Error Resume Next For j = 1 To nLookIn coll.Add LookIn(j, 1), CStr(LookIn(j, 1)) ' collection Next j ElseIf jSorted = 3 Then On Error Resume Next For j = 1 To nLookIn dict.Add LookIn(j, 1), LookIn(j, 1) ' dictionary Next j End If On Error GoTo 0 End If ' For j = 1 To nOut If Not FindExact Then vOut(j, 1) = LMatchInV(LookFor(j, 1), LookIn) 'instr linear search ElseIf jSorted = 1 Then vOut(j, 1) = BSearchMatch(LookFor(j, 1), LookIn) ' binary search ElseIf jSorted = 2 Then ' ' use collection ' On Error Resume Next Err.Clear vOut(j, 1) = coll.Item(CStr(LookFor(j, 1))) If CLng(Err.Number) = 5 Then vOut(j, 1) = False Else vOut(j, 1) = True End If ElseIf jSorted = 3 Then vOut(j, 1) = dict.Exists(LookFor(j, 1)) ' Dictionary ElseIf jSorted = -1 Then vOut(j, 1) = LMatchExactV(LookFor(j, 1), LookIn) ' exact linear Else vOut(j, 1) = CVErr(xlErrValue) End If Next j ' IsInList2 = vOut ' return output End Function

### Performance Comparison

The performance tests are all done using ranges containing a character followed by 5 digit random integer numbers.

The timings are all in Milliseconds.

LookFor and LookIn give the number of rows.

Linear Search timings on smaller numbers of rows are slightly pessimistic because of a high % of miss-matches

BSearchOnly gives timings for Binary Search on already sorted data.

Partial gives timings for Linear Search with partial matching using InStr.

Because Excel VBA array formulas cannot return more than 64K rows you cannot use the function directly for more than 64K rows of LookFor without using more than one array formula.

LookFor |
LookIn |
Linear |
QSortBsearch |
Collection |
Dict |
BsearchOnly |
Partial |

2 | 2 |
0.29 |
0.29 |
0.29 |
0.46 |
0.29 |
0.29 |

50 | 50 |
4.42 |
4.14 |
4.05 |
4.06 |
3.91 |
4.43 |

200 | 200 |
12.2 |
5.59 |
5.08 |
4.40 |
4.49 |
14.2 |

500 | 500 |
55.5 |
8.9 |
7.3 |
4.9 |
5.66 |
68.0 |

2000 | 2000 |
824 |
27.0 |
19.9 |
8.03 |
12.2 |
995 |

50000 | 50000 |
– |
800 |
515 |
252 |
279 |
– |

50000 | 200000 |
– |
2583 |
1290 |
1044 |
350 |
– |

50000 | 1048756 |
– |
14204 |
6720 |
23650 |
– |
– |

Overall the sequence from fastest method to slowest method is:

- Dictionary
- Binary Search Only (but the data has to already be sorted)
- Collection
- Quick Sort plus Binary Search
- Linear Search
- Partial Linear Search

## Conclusion

The best method is to use the VBScript Dictionary object, unless you have more than 500K rows after which the Collection object starts to win.

The coding for Dictionary is very simple and it has an Exists method which Collection lacks.

Below about 2000 rows you probably won’t notice the difference between any of the methods except for Linear search.

**So now I just have to try the C++ XLL version … I reckon that will beat Dictionary! (Yes, looks like XLL wins: 1923 millisecs for 50000 in 1048756)
**

This is a great bit of work Charles,

I can now give myself a pat on the back, as I correctly picked the collection class for my 1million rows project! I must have the coding instincts of some sort of binary spiderman, I was biten by a radio active byte…

or i just lucked out!!!

Thanks Charles, as always excellent post!

Hmm… If it was me I would have said too lazy to add the VBScripting reference and lookup the Dictionary syntax …

Well truth be told, I would have liked to use as Dictionary, but I wanted to use as little “none native” as possible… although in the same app I’ll have to install an .OXC, so I’m not sure what sense that makes!

Hi Charles. Do you have a worksheet you could post with this set up somewhere?

I cut and pasted the code into a module, and cleaned the code, but for some reason I can’t get any of the approaches to return any matches, even though I know there are matches.

Reason I’m interested is i have a couple of alternative approaches that I’d like to try out.

Hope you can help

Jeff

I have loaded BSearch_MatchSpeed2.xlsb to my public folder on Skydrive .. Note this does not include the XLL version.

Ahhh…I didn’t realise that you need an OPTION BASE 1 statement in the module. Now it seems to be working. Still, let me know if you can share a workbook.

Cheers

I always (well nearly always) use Option Base 1 in VBA because most Excel objects apart from Forms use 1-base. But zero-based everything in C … OK so I lack consistency.

Me again. I get a type mismatch on UBound(LookupArray) for BSearchMatch.

Okay, I get BSearchMatch to work as a UDF if I make these two changes:

Function BSearchMatch(LookupValue As Range, LookupRange As Range) As Boolean

LookupArray = LookupRange

i.e. explicitly type the input as a range, and then send that range to a variant array.

Am I right that as you wrote the function above, it WONT work as a UDF?

Sorry for the questions

Yes the only function written as a UDF is IsInList2 and there is no error checking at all: its all designed as a speed test rather than production code!!! So IsInList2 only works when fed Multi-cell ranges, and there is no checking whether the functions are given ranges or arrays or vectors or scalars etc etc. And the BSearch routine assumes a binary case-sensitive Quicksort rather than an Excel Sort, and it probably won’t work with Unicode etc.

Okay, my apologies…I didn’t understand until just now that BSearchMatch is NOT one of the tests, but instead is a function CALLED by one of the tests. Sorry for all the comments. Feel free to delete them.

No problem: I have edited the post to make this clearer.

Pingback: Arrays vs Collections vs Dictionary Objects (and Dictionary help) | Newton Excel Bach, not (just) an Excel Blog

Hi,

Interesting stuff. Yesterday I ran into your blog while I was looking what the Internet provides us with concerning the comparison of 2 lists by means of VBA Dictionary Scripting.

Hereunder is what I myself wrote down yesterday, but without making use of VBS.

It might be slightly off topic because I do not understand e.g. the meaning of an expression like “ranges containing a character followed by 5 digit random integer numbers”. I took 6 digit integers between 100000 and 999999. I placed them in columns 1 and 2 of an Excel 2007 spreadsheet and ran my script. I measured time from the moment on that all the numbers were read into the memory and ready for processing. I added some small particles of script to print the numbers that the 2 lists have in common, but treated them so far as comment.

1 Sub CompareLongListsByIndex()

2 Dim StartTime As Double

3 Dim arrLISTS As Variant

4 Dim A() As Byte, B() As Byte

5 Dim C() As Byte, D() As Long

6 Dim i As Long, r As Long, rr As Long

7 Dim min As Long, max As Long

8 Dim t As Variant

9 min = 100000

10 max = 999999

11 r = 0

12 arrLISTS = Sheets(“SORT”).Cells(1, 1).CurrentRegion

13 StartTime = Timer

14 r = UBound(arrLISTS, 1)

15 ReDim A(min To max)

16 ReDim B(min To max)

17 For i = min To max

18 A(i) = 0

19 B(i) = 0

20 Next

21 For i = 1 To r

22 A(arrLISTS(i, 1)) = 1

23 B(arrLISTS(i, 2)) = 1

24 Next

25 Set arrLISTS = Nothing

26 ReDim C(min To max)

27 ‘ReDim D(0 To r)

28 For i = min To max

29 C(i) = A(i) * B(i)

30 j = j + C(i)

31 ‘D(j) = D(j) + i * C(i)

32 ‘Debug.Print j ‘; D(j)

33 Next

34 t = Format((Timer – StartTime) * 1000, “00.000000000000”) & ” ms”

35 Debug.Print t; j

36 End Sub

Of course things can be improved.

You can surpass the limits of the spreadsheet by providing your input as text in e.g. a notepad file (.txt). By those means you can also surpass the limits of numbers that VBA puts, i.e. the limited number of digits and the use of a comma or point as the indicator for non-integer numbers. And of course comparison of text is possible too.

The limits of the script above are the wideness of the range in which the numbers of the 2 lists are contained.

For these limits there are possibly other, slightly different indexing approaches.

I added my scores to your table with the result as follows:

Qsort Bsearch CntSort

LookFor LookIn Linear Bsearch Collection Dict Only Partial Index

2 2 0,29 0,29 0,29 0,46 0,29 0,29 93,75

50 50 4,42 4,14 4,05 4,06 3,91 4,43 187,50

200 200 12,2 5,59 5,08 4,4 4,49 14,2 187,50

500 500 55,5 8,9 7,3 4,9 5,66 68 187,50

2000 2000 824 27 19,9 8,03 12,2 995 187,50

50000 50000 – 800 515 252 279 – 218,75

50000 200000 – 2583 1290 1044 350 – N/A

50000 1048756 – 14204 6720 23650 – – N/A

200000 200000 250,00

500000 500000 390,62

1048756 1048756 625,00

Of course it is no problem if you remove my post because of offtopicness.

If it is on topic, I might post an improved script that is faster for the larger lists and does not have the limitations mentioned above.

Regards,

Stephan

Hi Stephan,

Thats an interesting approach.

To compare with the code I posted could you change it so that:

– the numbers in the lists are prefixed by a non-numeric character

– an array of True/False is returned to the sheet containing the data (true for every item in the first list that appears in the second list, else false)

– the 2 lists do not have to be the same length

The time to beat is 2.2 seconds to compare a list containing 1 million items with another list containing 1 million items and return the corresponding 1 million true/falses.

Hi Charles,

Merry Christmas (and a Happy New Year). The code follows your directions, except that it throws out the double values (I will give that a thought):

1

Const r = 1048576, min = 10000, max = 99999 2

Dim p As Long, q As Long 3

Dim arrLISTSstr() As String 4

Dim arrRSLT01() As Long, arrRSLT02() As Boolean 5

Dim LISTS As Variant 6

Dim rngLISTS As Range 7

Dim LFtf(min To max) As Boolean, LItf(min To max) As Boolean 8

Dim rngCOMPLookF As Range, rngCOMPLookFI As Range 9

Dim i As Long, j As Long, t As Variant 10

Sub CompareListsByIndex() 11

ReDim arrLISTSstr(1 To r, 1 To 2) 12

For i = 1 To r 13

p = WorksheetFunction.RandBetween(min, max) 14

q = WorksheetFunction.RandBetween(min, max) 15

arrLISTSstr(i, 1) = “‘” & p 16

arrLISTSstr(i, 2) = “‘” & q 17

Next 18

p = 0 19

q = 0 20

Set rngLISTS = Sheets(“SORT”).Range(Cells(1, 1), Cells(r, 2)) 21

rngLISTS = arrLISTSstr 22

Erase arrLISTSstr 23

LISTS = Sheets(“SORT”).Cells(1, 1).CurrentRegion 24

For i = 1 To r 25

LFtf(Val(LISTS(i, 1))) = True 26

LItf(Val(LISTS(i, 2))) = True 27

Next 28

ReDim LISTS(0 To 0, 0 To 0) 29

ReDim arrRSLT01(1 To max – min + 1, 1 To 2) 30

ReDim arrRSLT02(1 To max – min + 1, 1 To 2) 31

j = 1 32

i = min 33

For i = min To max 34

arrRSLT01(j, 1) = (LFtf(i) = True) * -1 * i 35

arrRSLT02(j, 1) = (LFtf(i) = True) * (LFtf(i) * LItf(i)) 36

j = j + (LFtf(i) = True) * -1 37

Next 38

Set rngCOMPLookF = Sheets(“SORT”).Range(Cells(1, 4), Cells(j – 1, 4)) 39

Set rngCOMPLookFI = Sheets(“SORT”).Range(Cells(1, 5), Cells(j – 1, 5)) 40

rngCOMPLookF = arrRSLT01 41

rngCOMPLookFI = arrRSLT02 42

End Sub

Regards,

Stephan From: info@stephanpaus.nl To: comment+r144dyyzbgkuwe4sfy-fyomqh4wqdumqrepv1pich2f@comment.wordpress.com Subject: RE: [New comment] Comparing Two Lists – VBA UDF shootout between Linear Search, Binary Search, Collection and Dictionary Date: Wed, 18 Dec 2013 20:48:17 +0000

Hi,

Now I understand, that is to say, not exactly, but nevertheless how. It seems that the time spent on this operation is mainly dependant on the time consumed by printing the array with the results. Yet another question: the numbers with the prefixes may be split up in non-numerical data and numerical data to process and then to be joint again to present the result? If necessary I can give each number a different prefix as to show I am not deceiving. I will give it a try, because it is possible.

regards,

Stephan Date: Wed, 18 Dec 2013 17:04:54 +0000 To: info@stephanpaus.nl

Hi Charles,

Here’s the code that does not throw the redundant (?) data out.

1

Const r = 1048576, min = 10000, max = 99999 2

Dim p As Long, q As Long 3

Dim arrLISTSstr() As String 4

Dim arrRSLT01() As Long, arrRSLT02() As Boolean 5

Dim LISTS As Variant 6

Dim rngLISTS As Range 7

Dim LFtf(min To max) As Long, LItf(min To max) As Long 8

Dim rngCOMPLookF As Range, rngCOMPLookFI As Range 9

Dim i As Long, j As Long, t As Variant 10

Sub CompareListsByIndex() 11

ReDim arrLISTSstr(1 To r, 1 To 2) 12

For i = 1 To r 13

p = WorksheetFunction.RandBetween(min, max) 14

q = WorksheetFunction.RandBetween(min, max) 15

arrLISTSstr(i, 1) = “‘” & p 16

arrLISTSstr(i, 2) = “‘” & q 17

Next 18

p = 0 19

q = 0 20

Set rngLISTS = Sheets(“SORT”).Range(Cells(1, 1), Cells(r, 2)) 21

rngLISTS = arrLISTSstr 22

Erase arrLISTSstr 23

LISTS = Sheets(“SORT”).Cells(1, 1).CurrentRegion 24

For i = 1 To r 25

LFtf(Val(LISTS(i, 1))) = LFtf(Val(LISTS(i, 1))) + 1 26

LItf(Val(LISTS(i, 2))) = LItf(Val(LISTS(i, 2))) + 1 27

Next 28

ReDim LISTS(0 To 0, 0 To 0) 29

ReDim arrRSLT01(1 To r, 1 To 2) 30

ReDim arrRSLT02(1 To r, 1 To 2) 31

j = 1 32

i = min 33

For i = min To max 34

Do While LFtf(i) > 0 35

arrRSLT01(j, 1) = (LFtf(i) > 0) * -1 * i 36

arrRSLT02(j, 1) = (LFtf(i) * LItf(i) 0) 37

j = j + (LFtf(i) > 0) * -1 38

LFtf(i) = LFtf(i) – (LFtf(i) > 0) * -1 39

Loop 40

Next 41

Set rngCOMPLookF = Sheets(“SORT”).Range(Cells(1, 4), Cells(j – 1, 4)) 42

Set rngCOMPLookFI = Sheets(“SORT”).Range(Cells(1, 5), Cells(j – 1, 5)) 43

rngCOMPLookF = arrRSLT01 44

rngCOMPLookFI = arrRSLT02 45

End Sub

The code above takes about 3 times as much time to process the data as the code below does. I used VBA’s Timer to that, starting on line 24 of both code sets. The difference might be caused not only by the amount of data to be processed, but mainly, I suppose, by the double loop printed on lines 33 through 40 in the code above. Just one loop is possible at the cost of the use of an IfThenElse-construct, which on the average I consider to be slowlier than conditionally handling data by Boolean-constructs. The code above is almost the same as the so called Count Sort method. Its limits are soon reached in case the actual numbers have many digits or when you have to compare two lists that contain real text. The advantage is that the length of the LookFor list has hardly any consequences for the speed of data handling. In case the limits are surpassed, there are 2 main approaches if one wants to stick to the indexing method: split up both lists by their last digit: you get 10 pairs of lists to be treated as one pair is (this makes of course n sense for text) process the data according to the Radix Sort method, using the ASCII-code; as far as sorting data is concerned, there is hardly any limit to the length of the lists or the number of characters used for the individual items; this method is very attractive when you have to sort e.g. a very wide range of values, say 10^6 numbers of uneven length in terms of digits contained by an array starting at 1 end ending at 10^100; this can be done on almost any personal computer that has VBA or something like that, although it is recommendable to import your data to and export your data from a spreadsheet but preferably from and to e.g. Notepad.

Regards,

Stephan

From: info@stephanpaus.nl To: comment+r144dyyzbgkuwe4sfy-fyomqh4wqdumqrepv1pich2f@comment.wordpress.com Subject: RE: [New comment] Comparing Two Lists – VBA UDF shootout between Linear Search, Binary Search, Collection and Dictionary Date: Wed, 25 Dec 2013 00:33:32 +0000

Hi Charles,

Merry Christmas (and a Happy New Year). The code follows your directions, except that it throws out the double values (I will give that a thought):

1

Const r = 1048576, min = 10000, max = 99999 2

Dim p As Long, q As Long 3

Dim arrLISTSstr() As String 4

Dim arrRSLT01() As Long, arrRSLT02() As Boolean 5

Dim LISTS As Variant 6

Dim rngLISTS As Range 7

Dim LFtf(min To max) As Boolean, LItf(min To max) As Boolean 8

Dim rngCOMPLookF As Range, rngCOMPLookFI As Range 9

Dim i As Long, j As Long, t As Variant 10

Sub CompareListsByIndex() 11

ReDim arrLISTSstr(1 To r, 1 To 2) 12

For i = 1 To r 13

p = WorksheetFunction.RandBetween(min, max) 14

q = WorksheetFunction.RandBetween(min, max) 15

arrLISTSstr(i, 1) = “‘” & p 16

arrLISTSstr(i, 2) = “‘” & q 17

Next 18

p = 0 19

q = 0 20

Set rngLISTS = Sheets(“SORT”).Range(Cells(1, 1), Cells(r, 2)) 21

rngLISTS = arrLISTSstr 22

Erase arrLISTSstr 23

LISTS = Sheets(“SORT”).Cells(1, 1).CurrentRegion 24

For i = 1 To r 25

LFtf(Val(LISTS(i, 1))) = True 26

LItf(Val(LISTS(i, 2))) = True 27

Next 28

ReDim LISTS(0 To 0, 0 To 0) 29

ReDim arrRSLT01(1 To max – min + 1, 1 To 2) 30

ReDim arrRSLT02(1 To max – min + 1, 1 To 2) 31

j = 1 32

i = min 33

For i = min To max 34

arrRSLT01(j, 1) = (LFtf(i) = True) * -1 * i 35

arrRSLT02(j, 1) = (LFtf(i) = True) * (LFtf(i) * LItf(i)) 36

j = j + (LFtf(i) = True) * -1 37

Next 38

Set rngCOMPLookF = Sheets(“SORT”).Range(Cells(1, 4), Cells(j – 1, 4)) 39

Set rngCOMPLookFI = Sheets(“SORT”).Range(Cells(1, 5), Cells(j – 1, 5)) 40

rngCOMPLookF = arrRSLT01 41

rngCOMPLookFI = arrRSLT02 42

End Sub

Regards,

Stephan From: info@stephanpaus.nl To: comment+r144dyyzbgkuwe4sfy-fyomqh4wqdumqrepv1pich2f@comment.wordpress.com Subject: RE: [New comment] Comparing Two Lists – VBA UDF shootout between Linear Search, Binary Search, Collection and Dictionary Date: Wed, 18 Dec 2013 20:48:17 +0000

Hi,

Now I understand, that is to say, not exactly, but nevertheless how. It seems that the time spent on this operation is mainly dependant on the time consumed by printing the array with the results. Yet another question: the numbers with the prefixes may be split up in non-numerical data and numerical data to process and then to be joint again to present the result? If necessary I can give each number a different prefix as to show I am not deceiving. I will give it a try, because it is possible.

regards,

Stephan Date: Wed, 18 Dec 2013 17:04:54 +0000 To: info@stephanpaus.nl

Thanks for MicroTimer. Took me from confusion to clarity in 10 lines or less. Just loop 10000 times and divide? Ever hear of a random walk ? Lots of bogus variability in times in consequence. And why wait 10 min to get what MicroTimer tells you instantly.?

Don’t be lazy and use a long loop. User MicroTimer.