I've had a look at this but it's zzzzs time for me now so I thought I'd show how far I got:
I used in cell D3 of sheet OM the formula:
=VLOOKUP(A3,SD!$A$3:$A$86401,1,FALSE)
and autofilled down. Waited for calculation to finish and copied/pasted values in situ.
I ran your CompareUsingBinarySearch macro, then filtered for Y in column B of sheet OM.
The first disparity was on row 6112 of OM where it failed to find
020-SMT in sheet SD. It is present in SD on row 6109.
So I added the line:
Debug.Assert x <> 6112
after
Do While Not Cells(x, 1) = ""
to stop it there to step through it.
I also added the ONE line:
Debug.Print strArray(lngMiddle) & " < " & strSearch, strArray(lngMiddle) < strSearch, lngFirst, lngMiddle, lngLast
directly after:
lngMiddle = (lngFirst + lngLast) \ 2
but disabled it with a comment-apostrophe.
I re-ran the CompareUsingBinarySearch macro. When it stopped on the Debug.Assert line, I re-enabled the Debug.Print line and stepped through the code.
tHEN IN THE iMMEDIATE PANE:
?strSearch
020-SMT
?strArray(6106)
020-SMT
Just to confirm what was being sought and where it was to be found (6106 in StrArray).
then I got this print out in the immediate pane:
P00-110490-00712-A000 < 020-SMT False 0 43199 86398
600-00001-0030 < 020-SMT False 0 21599 43198
400-18240-0001 < 020-SMT False 0 10799 21598
020-50202-0001 < 020-SMT True 0 5399 10798
071-27988 < 020-SMT False 5400 8099 10798
040-50058 < 020-SMT False 5400 6749 8098
020-60051-0005 < 020-SMT True 5400 6074 6748
030-50001 < 020-SMT False 6075 6411 6748
030-10578 < 020-SMT False 6075 6242 6410
025-50009 < 020-SMT False 6075 6158 6241
025-00029 < 020-SMT False 6075 6116 6157
020-BT-MMIC-0031 < 020-SMT True 6075 6095 6115
020-MMIC-0031 < 020-SMT True 6096 6105 6115
02-16917-3600 < 020-SMT True 6106 6110 6115
025-00026 < 020-SMT False 6111 6113 6115
025-00004-0009 < 020-SMT False 6111 6111 6112
I'm guessing, but haven't looked too closely, that things are going wrong 3 lines up from the bottom where 6110 is being compared resulting in 6106 being out of the equation in the next line.
G'night!
--- In ExcelVBA@yahoogroups.com, "Scott" <scott@...> wrote:
>
> I'm having a problem with a binary search of three databases. I'm trying to compare 86,000+ strings in one database against two other databases, each with over 86,000 entries as well. When I load the other two into arrays, then perform a binary search of the two arrays, I get 274 "hits" as being missing as compared to the master. But if I search the the same two databases using a FIND function (which takes an hour to run) I only get 85 hits. The two arrays are sorted, which I've double checked with a simple =if(a2<a3.... formula. I just can't figure it out. My binary search is below
> ------------------- CODE ----------------------
> Function BinarySearch(strArray() As String, strSearch As String) As Long
> Dim lngIndex As Long
> Dim lngFirst As Long
> Dim lngLast As Long
> Dim lngMiddle As Long
> Dim bolInverseOrder As Boolean
> lngFirst = LBound(strArray)
> lngLast = UBound(strArray)
> bolInverseOrder = (strArray(lngFirst) > strArray(lngLast))
> BinarySearch = lngFirst - 1
> Do
> lngMiddle = (lngFirst + lngLast) \ 2
> If strArray(lngMiddle) = strSearch Then
> BinarySearch = lngMiddle
> Exit Do
> ElseIf ((strArray(lngMiddle) < strSearch) Xor bolInverseOrder) Then
> lngFirst = lngMiddle + 1
> Else
> lngLast = lngMiddle - 1
> End If
> Loop Until lngFirst > lngLast
> End Function
> ------------------- CODE ----------------------
>
> I'll email the file to the moderator so they can post it in the files section. Thanks for your help.
>
> Scott B)
>
> moderator edit:
> The file, called BinarySearchProblem.xlsm has been added to the
> Files\Files needing help
> section.
>
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar