Quote Originally Posted by Rick Rothstein View Post
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...
Code:
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.