Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Looping Through String Using Excel VBA!

  1. #1
    Junior Member
    Join Date
    Sep 2014
    Posts
    7
    Rep Power
    0

    Looping Through String Using Excel VBA!

    Hello!


    I hope someone can help me with this.

    Assume:
    String A contains "5, 45, 100, 113, 160"
    String B contains "2, 4, 45, 160, 189"

    If want to compare the two strings to check whether they have two integers in common, in this case it will be "45" and "160".
    How I have approached my solution is to use the split function to split each string into 2 arrays. Then using two For-loops I compare each token of each string array to the tokens of the other array. Then using a counter if there is a match then I increment the counter. If the counter is >=2 then it means the two share at least two numbers.

    This approach works fine except I will be doing this for two very large sets of strings and it is very time-consuming in execution. Is there a faster VBA method or data structure to use instead of breaking the two strings up into two arrays and creating two For-loops?

    Any suggestions in VBA code would be appreciated.

    Thanks!
    Jdean

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=QjEWAJ3d-jw
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgwCGCesYkcmCcv7tzx4AaABAg
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgwCGCesYkcmCcv7tzx4AaABAg.9wbCfWMaaLa9wbLcU jbPCV 3
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgwCGCesYkcmCcv7tzx4AaABAg.9wbCfWMaaLa9wbLma sNyaX 1
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgzxkJD1jksXet8AZYB4AaABAg.9p3jaxCq0AG9wbF__ jtm9w 2
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxePNoJ9lMOZZIxSI54AaABAg.9n_K6OLzSGt9wbFsa Pa2ym 1
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgwUIM7LhCvJkBpHL4N4AaABAg.9j-vSfzAHrw9wbFzCwVRUo 1
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgwQ_hGXSa1PNKbT-r94AaABAg.9hmiz-Qc-bq9wbG1qa8wKO 1
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgwqWxGrYGjtUAJG6aF4AaABAg.9hI9sgAhykQ9wbG4K JfN91 1
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJL5BeSLbJ-m7BWW54AaABAg.9euWbYmFb169wbG8eMb5Wb 1
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgwaEHwTeazYGD7xHmN4AaABAg.9eWJC0jtPrJ9wbGCR m3IO6 1
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgySibJeWUXeEn3qez14AaABAg.9dj9CcZAzcq9wbGH5 FhlqO
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgyrMrxE5-AP81sgU8V4AaABAg.9aoKBx9yaE89wbGOGcNnKy 1
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=Ugw5b6kCEckEbGTccxp4AaABAg.9_Sbwexq-co9wbGW8LbhKp 1
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgyCQp_ShaVxQui5hJh4AaABAg.9ZBRfgBVmcd9wbGdP 0tnCi 2
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=Ugz_lKW2DNBax4Aemst4AaABAg.9Xjhb-fv4pt9wbGgysEibx
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxguKtw3d8jE8bkGTB4AaABAg.9UuGKC386629wbGl3 2wvjC 1
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgwLt2hK6AcHVnVlaUl4AaABAg.9HKd-ioHqxM9wbH2o6HYsJ 1
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=Ugw-IPT7RwxyRo4cbqd4AaABAg.9GqtD5j30Wp9wbH6q7RTJa 1
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgzLnQG1_LQtmvLQoot4AaABAg.9FvawuMTb-k9wbHFrsug5Z 1
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=Ugys6Ur7BNsRFbH_f_B4AaABAg.9DhZy5EEpKY9wbHfy JkVMG 3
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wbILDvziWr 2
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgwgzeOLschepoIO3gx4AaABAg.97v7ND4_6p298-gyUz3MY7 2
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 11-02-2023 at 05:22 PM.

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    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
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Junior Member
    Join Date
    Sep 2014
    Posts
    7
    Rep Power
    0
    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

  4. #4
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    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.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  5. #5
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    Where do those strings reside ? ( in cells, in ranges, txt-files, clipboard, somewhere else..)
    Why is this an Excel question in your opinion ?

  6. #6
    Junior Member
    Join Date
    Sep 2014
    Posts
    7
    Rep Power
    0
    Quote Originally Posted by snb View Post
    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.

  7. #7
    Junior Member
    Join Date
    Sep 2014
    Posts
    7
    Rep Power
    0
    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.

  8. #8
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    Is this correct ?

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

  9. #9
    Junior Member
    Join Date
    Sep 2014
    Posts
    7
    Rep Power
    0
    Quote Originally Posted by snb View Post
    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.

  10. #10
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    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.
    Attached Files Attached Files
    Last edited by snb; 09-08-2014 at 12:09 AM.

Similar Threads

  1. Replies: 15
    Last Post: 08-23-2013, 12:03 PM
  2. VBA Looping Input Range and Output Range
    By Whitley in forum Excel Help
    Replies: 7
    Last Post: 04-25-2013, 09:02 PM
  3. Looping through Each Files and Folders
    By Rajan_Verma in forum Rajan Verma's Corner
    Replies: 0
    Last Post: 04-18-2012, 12:12 AM
  4. Replies: 10
    Last Post: 04-07-2012, 05:33 AM
  5. Looping Through Each Internet Explorer
    By Rajan_Verma in forum Rajan Verma's Corner
    Replies: 3
    Last Post: 03-27-2012, 07:30 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •