Looping Through String Using Excel VBA!

**Jdean** · 09-07-2014, 05:40 AM

**Admin** · 09-07-2014, 10:20 AM

Hi

Welcome to board.

You could try these methods.

Code:

Option Explicit

Sub kTest()
    
    Dim x, y, i As Long, c As Long
    Dim s1 As String, s2 As String
    
    s1 = "5, 45, 100, 113, 160"
    s2 = "2, 4, 45, 160, 189"
    
    x = Split(s1, ",")
    y = Split(s2, ",")
    
    With CreateObject("scripting.dictionary")
        .comparemode = 1
        For i = 0 To UBound(x)
            If Len(Trim(x(i))) Then
                .Item(Trim(x(i))) = 1
            End If
        Next
        
        For i = 0 To UBound(y)
            If .exists(Trim(y(i))) Then
                c = c + 1
            End If
        Next
        MsgBox "There are " & c & " item(s) in common."
    End With
    c = 0
    For i = 0 To UBound(y)
        If InStr(1, ", " & s1 & ", ", ", " & Trim(y(i)) & ", ", 1) Then 'careful about the delimiter
            c = c + 1
        End If
    Next
    MsgBox "There are " & c & " item(s) in common."
    
End Sub

**Jdean** · 09-07-2014, 02:08 PM

Thanks, Admin. I created a function using the Dictionary data structure method you provided. It worked but when i tested it for large datasets (in the hundreds of thousands) it didn't run any faster than the good ole fashioned two For-loops array method I was using.

Your second method using the Instr function looks interesting, but before I try it I would like for you to slowly explain the arguments for me. I really want to understand what is in the Instr() parentheses. I know it has the structure InStr( [start], string, substring, [compare] ) but I couldn't easily sync it up with your coded arguments.
Also, will the number of delimiters stay the same regardless of the length of S1 and S2 for the Instr() method? (Of course, I assume each input string will always contain more than one token).

Jdean

**Admin** · 09-07-2014, 03:02 PM

I used the delimiter to surround the string just to ensure we won't get a incorrect result when you search for a sub-string within the String. For example, if you don't use that delimiter, Instr(1,s1,"4",1) would give you 4 which is incorrect.

**snb** · 09-07-2014, 04:53 PM

Where do those strings reside ? ( in cells, in ranges, txt-files, clipboard, somewhere else..)
Why is this an Excel question in your opinion ?

**Jdean** · 09-07-2014, 09:02 PM

Originally Posted by snb

Where do those strings reside ? ( in cells, in ranges, txt-files, clipboard, somewhere else..)
Why is this an Excel question in your opinion ?

The strings reside in XL Ranges ("A:A") and ("B:B"). So String A and B (or S1 and S2) are just a comparison of two cell values - one from each column range.
My current approach has been to load both into separate vectors (or 1D arrays), iterate each slot, break each slot into tokens and use two For-Loops to check and count common tokens. This is what the Admin suggested two other methods for checking two strings.

**Jdean** · 09-07-2014, 09:07 PM

Okay, thanks, Admin. I tested your Instr() function method and it also worked and ran almost as fast as the two For-loops method I was using. It was definitely faster than the dictionary data structure approach. Still playing around with variations of both to see if the execution time will be better.

**snb** · 09-07-2014, 09:38 PM

Is this correct ?

cell A1 contains: "5, 45, 100, 113, 160"
cell B1 contains: "2, 4, 45, 160, 189"

**Jdean** · 09-07-2014, 10:08 PM

Originally Posted by snb

Is this correct ?

cell A1 contains: "5, 45, 100, 113, 160"
cell B1 contains: "2, 4, 45, 160, 189"

Correct... and I want to count how many tokens A1 and B1 have in common.

**snb** · 09-07-2014, 11:51 PM

Do you want to compare only A1 to B1 or A1 to every string in column B ?

I would split the values in column A to colum A:E (texttocolumns)

Then it's easy to compare the values in A1:E1 to the string in column F and put the result of the comparison in column G

See the attachment.