• 0

Your basic list comparing utility. Need help.


Question

I need some help with an algorithm. Well, basically I know one way of doing it, but I'm wondering if there is a smarter way of doing it.

Say i have a list of possible (or rather, known) values for a field, and I want to compare the current value of the field against all the known values to come up with some suggestions to change the current value into a known value. Much like how a spell checker works, only with phrases that contain more then 1 word. My basic algorithm looks something like:

Step 1. Start with the first 2 characters, and grab everything out of the known list with those first 2 characters.

Step 2. Compare the first 3 characters with the first 3 characters of everything that was grabbed in step 1, move those to the top of the list.

Step 3. Repeat Step 2, with 4 characters and so forth until the entire length of the given string has been gone through, with the most likely matches at the top of the list.

Some background:

I have this HUGE Excel spreadsheet full of testing data on different non-metal materials. I need to get all that data into an Access Database. I'm currently making a custom dialog box that will crank through all the "Trade Names" and compare them to what "Trade Names" we already have in our Access Database. If no close match can be found, then the user has the option to insert the new trade name into the database.

If you can think of a better method of comparing what is in the spread sheet against the possible values in the database, i'd like to know! Thanks!!

-Nic

Link to comment
Share on other sites

2 answers to this question

Recommended Posts

  • 0

Soundex. Cool, I'll look into it. I think I've figured out something that will work for me now though.

In visual basic for applications:

Sub reorder(curfield As String, ByRef thearray() As String, compare_number As Integer)
    'this subrouting will reorder "thearray" based on the compare_number characters of the curfield
    Dim i As Integer    'our index while working through the array.
    Dim k As Integer    'our top position pointer.
    k = -1   'initialize our k to an "unitialized" value of -1
    For i = 0 To UBound(thearray)
        If LCase(Left(curfield, compare_number)) = LCase(Left(thearray(i), compare_number)) Then
            If Not k = -1 Then
                swap thearray, k, i
                k = k + 1
            End If
        Else
            If k = -1 Then   'our pointer has not been initilized yet set it to the current value of i
                k = i
            End If
        End If
    Next
End Sub
Sub swap(ByRef thearray() As String, index1 As Integer, index2 As Integer)
    Dim temp As String
    temp = thearray(index2)
    thearray(index2) = thearray(index1)
    thearray(index1) = temp
End Sub

Where "thearray" is an array of possible values that match "curfield" off at least 2 characters.

reorder subrouting will compare as many characters as compare_number, and brings closure matching to the top of the array. The k index is a little confussing, but it basically points to a spot in the array that is the next possible position for a matched spot. So if compare_number=4, then k will always point to a spot in the array that does not match up to 4 characters and can be swapped.

Seems resonable. I could probably clean it up a bit though to make it mroe efficient.

-nic

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.