Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I make a Binary Search Algorithm on Excel?
Basically I have to search a data set via customer codes using a binary
search algorithm and have no idea how to do it? I'm very new to VB so would appreciate some simple words :) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I make a Binary Search Algorithm on Excel?
The simplest way is to use the Excel MATCH or VLOOKUP functions with
the sorted option, they already use Binary Search. If you want to do it from VBA then use something like vRow=application.WorksheetFunction.Match(ValuetoFi nd,RangetoSearch,1) If for some reason you want to program it yourself in VB then I would Google for Binary Search VB6 or VBA regards Charles On Wed, 10 Mar 2010 09:26:01 -0800, NewToVB wrote: Basically I have to search a data set via customer codes using a binary search algorithm and have no idea how to do it? I'm very new to VB so would appreciate some simple words :) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I make a Binary Search Algorithm on Excel?
Hi,
In summary a binary search works on sorted data and starts from the mid-point of that data and; unless your lucky and find what you want, continues the search at the mid-point of the data above or below the start point. It does this iteratively until the required data are found. Is the creation of binary search code an end in itself i.e. is this some sort of assignment or are you simply supposing that's the best way to search through data? Unless the data set are very large; and in Excel that's not really likely, and if it is your probably using the wrong software. Perhaps you could give us some more detail on what your trying to achieve. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "NewToVB" wrote: Basically I have to search a data set via customer codes using a binary search algorithm and have no idea how to do it? I'm very new to VB so would appreciate some simple words :) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I make a Binary Search Algorithm on Excel?
Hello:
I use binary searches quite frequently. As you know, you can search an extremely large number of rows very quickly. A couple of cautions: Before you do a binary search in VBa, make sure you sequence check the rows. If you use the Excel sort feature in VBA... and you are not careful... you can sort it in such a manner that numbers are sorted as numbers, and it will not be in sequence as far as "IF" compares. Here's my typical approach: 1) Format the rows using a Cstr() command to convert any numbers to strings. 2) Use the Excel Sort feature from VBA (it is hundreds of times faster than a bubble sort) 3) Sequence check the column 4) Then do the binary search The following is all the code you will need to be able to set up a test case and make modifications for your own application: Option Explicit Option Base 1 Sub TestBinarySearch() Dim strSearchArray(15) As String Dim strValueArray(15) As String Dim intTheUpperLimit As Long Dim strTheSearchFor As String Dim booFound As Boolean Dim strFoundValue As String Dim strWhatImLookingFor As String strSearchArray(1) = "A" strSearchArray(2) = "C" strSearchArray(3) = "E" strSearchArray(4) = "G" strSearchArray(5) = "I" strSearchArray(6) = "K" strSearchArray(7) = "M" strSearchArray(8) = "P" strSearchArray(9) = "R" strSearchArray(10) = "T" strSearchArray(11) = "V" strSearchArray(12) = "W" strSearchArray(13) = "X" strSearchArray(14) = "Y" strSearchArray(15) = "Z" strValueArray(1) = "1" strValueArray(2) = "2" strValueArray(3) = "3" strValueArray(4) = "4" strValueArray(5) = "5" strValueArray(6) = "6" strValueArray(7) = "7" strValueArray(8) = "8" strValueArray(9) = "9" strValueArray(10) = "10" strValueArray(11) = "11" strValueArray(12) = "12" strValueArray(13) = "13" strValueArray(14) = "14" strValueArray(15) = "15" Do Until strTheSearchFor = "Exit" strTheSearchFor = InputBox("Enter Search Argument") intTheUpperLimit = 15 strFoundValue = "*" Call BinarySearch(strSearchArray, strValueArray, intTheUpperLimit, strTheSearchFor, booFound, strFoundValue) MsgBox ("Found = " & booFound & " Value = " & strFoundValue) Loop End Sub Sub TestSequenceCheck() Dim strSearchArray(15) As String Dim intTheUpperLimit As Long Dim booInSequence As Boolean strSearchArray(1) = "A" strSearchArray(2) = "C" strSearchArray(3) = "D" strSearchArray(4) = "E" strSearchArray(5) = "H" strSearchArray(6) = "K" strSearchArray(7) = "M" strSearchArray(8) = "P" strSearchArray(9) = "R" strSearchArray(10) = "T" strSearchArray(11) = "V" strSearchArray(12) = "W" strSearchArray(13) = "X" strSearchArray(14) = "Y" strSearchArray(15) = "a" intTheUpperLimit = 15 Call SequenceCheck(strSearchArray, intTheUpperLimit, booInSequence) MsgBox ("Sequence Check Proved " & booInSequence) End Sub Sub BinarySearch(SearchArray() As String, _ ValueArray() As String, _ intUpperLimit As Long, _ SearchFor As String, _ Found As Boolean, _ FoundValue As String) Dim Left, Right, Middle As Integer Found = False Left = 1 Right = intUpperLimit Do Until Found If Left Right Then Exit Do End If Middle = (Left + Right) / 2 If SearchArray(Middle) = SearchFor Then Found = True FoundValue = ValueArray(Middle) ElseIf SearchFor SearchArray(Middle) Then Left = Middle + 1 Else Right = Middle - 1 End If Loop End Sub Sub SequenceCheck(SearchArray() As String, _ UpperLimit As Long, _ InSequence As Boolean) Dim strLastValue As String Dim intArrayPointer As Long InSequence = True strLastValue = SearchArray(1) For intArrayPointer = 1 To UpperLimit If strLastValue SearchArray(intArrayPointer) Then InSequence = False Exit For Else strLastValue = SearchArray(intArrayPointer) End If Next intArrayPointer End Sub -- Rich Locus Logicwurks, LLC "NewToVB" wrote: Basically I have to search a data set via customer codes using a binary search algorithm and have no idea how to do it? I'm very new to VB so would appreciate some simple words :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MS Help-search algorithm sucks | New Users to Excel | |||
search algorithm sucks | Excel Discussion (Misc queries) | |||
Solver returns non binary answer in binary constrained cells | Excel Worksheet Functions | |||
Binary operations (left/right shift, binary and/or, etc.) | Excel Programming | |||
Newbee Q: How to make search field in excel sheet? | Excel Programming |