Looping Through String Using Excel VBA!

[Jdean]

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.

I would like to compare A1 to every string in column B.

[snb]

Did you look at the attachment ?

[Jdean]

Did you look at the attachment ?

Yes, and I am confused. It doesn't look like what I am looking for.
Using text to columns is equivalent to splitting each string into an array with the delimiter "," using the split() function. I executed your code and it still confuses me.

The task I am trying to solve is: I have two strings. I need a quick way to find how many tokens or numbers they have in common.

[p45cal]

If each list conatains say 1000 items, with a nested loop you could be making 1000 x 1000 = 1 million comparisons.
Have you considered using an Exit For/Exit Do once a single match is found?
Are both lists already sorted (it looks like they may be)? If so, instead of comparing all values in one list with all values in the other, you could walk down both lists at the same time:
First compare both first items in the lists and see which one is smaller, then iterate down the list which had the smaller value until you get a match or the value is bigger, then stop running down that list and continue running down the other list until again, a match or bigger value is encountered etc. That way only 2000 comparisons get made.
Eg. using your example:
list A: "5, 45, 100, 113, 160"
list B: "2, 4, 45, 160, 189"

compare 5 and 2. 2 is smaller so run down list B:
compare 5 and 4
compare 5 and 45
stop at the 45 and start running down list A:
compare 45 and 45 (match, so add to the count)
compare 45 and 100
stop at 100 to flip over:
compare 100 and 160
stop at 160 to flip over:
compare 160 and 113
compare 160 and 160 (match, so add to the count)
compare 160 and 189
stop at 189 to flip over (or since you've come to the end of one of the lists there's no need to continue).

Using your example we need only make 8 comparisons instead of 25.

Perhaps post the code you already have?
Perhaps attach a workbook?

edit post posting:
something along these lines (neither debugged nor streamlined):

Sub blah()
s1 = "5, 45, 100, 113, 160"
s2 = "2, 4, 45, 160, 189"
x = Split(s1, ",")
y = Split(s2, ",")
ix = 0: iy = 0
Do Until ix > UBound(x) Or iy > UBound(y) 'put ubounds into variables so that they don't need to be calculated in each iteration.
  'Debug.Print x(ix), y(iy), IIf(CLng(x(ix)) = CLng(y(iy)), "Match!", "")
  If CLng(x(ix)) = CLng(y(iy)) Then Count = Count + 1 'I've assumed whole numbers; requires a tweak for singles or doubles.
  If CLng(x(ix)) >= CLng(y(iy)) Then iy = iy + 1 Else ix = ix + 1
Loop
MsgBox Count & " item(s) in common"
End Sub

[Rick Rothstein]

What about doing it with no loops? Here is a one-liner (albeit a long one) UDF (user defined function) that you can use directly in a formula on a worksheet...

Function CountDupes(R1 As Range, R2 As Range) As Long
  CountDupes = Evaluate("SUM(0+ISNUMBER(TRANSPOSE(FIND("", ""&TRIM(MID(SUBSTITUTE(" & _
               R1.Address & ","", "",REPT("" "",99)),ROW(A1:A" & UBound(Split(R1.Value, _
               ",")) + 1 & ")*99-98,99))&"", "","", ""&" & R2.Address & "&"", ""))))")
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use CountDupes just like it was a built-in Excel function. For example,

="Number of matching numbers = " & CountDupes(A1,B1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

[Rick Rothstein]

What about doing it with no loops? Here is a one-liner (albeit a long one) UDF (user defined function) that you can use directly in a formula on a worksheet...

Function CountDupes(R1 As Range, R2 As Range) As Long
  CountDupes = Evaluate("SUM(0+ISNUMBER(TRANSPOSE(FIND("", ""&TRIM(MID(SUBSTITUTE(" & _
               R1.Address & ","", "",REPT("" "",99)),ROW(A1:A" & UBound(Split(R1.Value, _
               ",")) + 1 & ")*99-98,99))&"", "","", ""&" & R2.Address & "&"", ""))))")
End Function

As a follow up to my one-liner above, I should mention two things. First, although I presented the above function as a UDF (in Message #15), it can be called from other VBA procedures as well, just make sure to pass both arguments as Ranges, not text Strings. Second, there is a worksheet formula equivalent to the one-liner function above... instead of installing the code and calling the CountDupes formula, you can use this array-entered** formula directly on the worksheet

=SUM(0+ISNUMBER(TRANSPOSE(FIND(", "&TRIM(MID(SUBSTITUTE(A1,", ",REPT(" ",99)),ROW(INDIRECT("A1:A"&LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))*99-98,99))&", ",", "&B1&", "))))

** Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself.