Compare Two Dynamic String Arrays

Question

I'm looking for a little guidance and experience. I have an VBA module that creates two strings. See below. I want to use an array to compare the two stings and write the successful matches or "no match" for the element to a third array or directly to the worksheet. The second part of this is a "percent of" match of Arr2 to Arr1. So the below example would be 88%.

> Arr1 result  
> 726741,439037,X41033X,X0254XX,X47083X,X0252XX,X50047X,XH5815X
> Arr2 result
> 726742,439037,X41033X,X0254XX,X47083X,X0252XX,X50047X,XH5815X

Any advice would be great.

What have you tried for comparing the arrays so far? If you made collections instead then you can use the .Item() to test if a value exists. There are better links but that one would get you started for sure. — Matt
– Matt, Commented Oct 21, 2014 at 0:30

Automate This · Accepted Answer · 2014-10-21 00:44:52Z

Here is one way to accomplish the task using simple for loops.

Sub compareStrings()
    Dim str1 As String
    Dim str2 As String
    str1 = "726741,439037,X41033X,X0254XX,X47083X,X0252XX,X50047X,XH5815X"
    str2 = "726742,439037,X41033X,X0254XX,X47083X,X0252XX,X50047X,XH5815X"

    Dim Arr1 As Variant
    Dim Arr2 As Variant
    Dim ArrResults As Variant

    Arr1 = Split(str1, ",")
    Arr2 = Split(str2, ",")

    Dim countMatches As Integer
    countMatches = 0

    ReDim ArrResults(UBound(Arr1))

    For i = LBound(Arr1) To UBound(Arr1)
        If Arr1(i) = Arr2(i) Then
            ArrResults(i) = "Matches"
            countMatches = countMatches + 1
        Else
            ArrResults(i) = "No Match"
        End If
    Next i

    'Print out the results array in debug window
    For Each entry In ArrResults
        Debug.Print entry
    Next entry

    Dim ratio As Double
    ratio = countMatches / (UBound(Arr1) + 1)

    MsgBox (ratio * 100 & "%")
End Sub

Message box will display this:

enter image description here

Immediate window will display the results array values like this:

enter image description here

L42 · Accepted Answer · 2014-10-21 00:52:33Z

0

Try this:

Sub Test()
    Dim str1 As String, str2 As String
    Dim arr, i As Long, cnt As Long

    str1 = "726741,439037,X41033X,X0254XX,X47083X,X0252XX,X50047X,XH5815X"
    str2 = "726742,439037,X41033X,X0254XX,X47083X,X0252XX,X50047X,XH5815X"

    For i = LBound(Split(str1, ",")) To UBound(Split(str1, ","))
        If Not IsArray(arr) Then
            arr = Array(IIf(Split(str1, ",")(i) = _
                Split(str2, ",")(i), "Match", "NoMatch"))
        Else
            ReDim Preserve arr(UBound(arr) + 1)
            arr(UBound(arr)) = IIf(Split(str1, ",")(i) = _
                Split(str2, ",")(i), "Match", "NoMatch")
        End If
    Next

    '~~> Check the array
    For i = LBound(arr) To UBound(arr)
        Debug.Print arr(i)
        If arr(i) = "Match" Then cnt = cnt + 1
    Next
    '~~> output the percentage
    MsgBox Format(cnt / (UBound(arr) + 1), "0.00%")
End Sub

answered Oct 21, 2014 at 0:52

L42

19.8k11 gold badges47 silver badges70 bronze badges

1 Comment

RL001 Over a year ago

Thanks for the response PortlandRunner and L42. I didn't think to split the array. Both your solutions should get me in the right direction.

Collectives™ on Stack Overflow

Compare Two Dynamic String Arrays

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related