Lookup names in a cell and return number of matches

[chive90]

I have a list of names in Column R on Sheet1. There are multiple names in each cell and they are separated by a semi colon.

I have a list of names in Column A on Sheet2. There are single entries in each cell.

Is there a way for me to lookup the names in Column A of Sheet2 against Column R on Sheet1 and record a count of how many match?
For example: R2 on Sheet1 has 8 names, each separated by ;
2 of these names exist in Column A on Sheet2, so S2 on Sheet1 will record a value of 2

Thanks in advance

 

[chive90]

I guess they are limitations of excel.
If you run into problems with those limits, another alternative is to use a macro.

Try the following macro. The results in column S, if you want them in another column, change the S by the letter of the column you want in this line of the macro:

sh1.Range("S" & c.row).Value = n

Put the following code in a module:

Sub countnames()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim c As Range, f As Range
  Dim nm As Variant, s As String
  Dim n As Long
 
  Set sh1 = Sheets("Sheet1")
  Set sh2 = Sheets("Sheet2")
 
  For Each c In sh1.Range("R2", sh1.Range("R" & Rows.Count).End(3))
    n = 0
    For Each nm In Split(c.Value, ";")
      s = Trim(nm)
      Set f = sh2.Range("A:A").Find(s, , xlValues, xlWhole, , , False)
      If Not f Is Nothing Then
        n = n + 1
      End If
    Next
    sh1.Range("S" & c.row).Value = n
  Next
End Sub

HOW TO INSTALL MACROs
If you are new to macros, 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. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (countnames) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and 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.

Thanks so much. I tried the formula with 1000 and, although it worked for some of the longer strings, it still returned 0 for strings that were clearly too long - like you say, must be a limitation of excel unfortunately, because even with 2000 or 5000, it still returned 0.

I then tried your macro and it worked perfectly. Thanks so much for your help it is much appreciated