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

 

[DanteAmor]

Try:
Fit Sheet2!$A$2:$A$100 to the range of your named cells on sheet2.

Dante Amor
	R	S
1
2	dam; amor; xd; ana; dante	3
3	pedro; john; gloria; david; jean	2
Sheet1

Cell Formulas
Range	Formula
S2:S3	S2	=SUM(IF(ISNUMBER(MATCH(TRIM(MID(SUBSTITUTE(R2,";",REPT(" ",255)),255*(ROW(INDIRECT("1:" & LEN(R2)-LEN(SUBSTITUTE(R2,";",""))+1))-1)+1,255)),Sheet2!$A$2:$A$100,0)),1))
Press CTRL+SHIFT+ENTER to enter array formulas.

Dante Amor
	A
1	NAMES
2	ana
3	amor
4	daniel
5	gloria
6	dante
7	maría
8	pedro
9
10
Sheet2

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[chive90]

Try:
Fit Sheet2!$A$2:$A$100 to the range of your named cells on sheet2.

Dante Amor
	R	S
1
2	dam; amor; xd; ana; dante	3
3	pedro; john; gloria; david; jean	2
Sheet1

Cell Formulas
Range	Formula
S2:S3	S2	=SUM(IF(ISNUMBER(MATCH(TRIM(MID(SUBSTITUTE(R2,";",REPT(" ",255)),255*(ROW(INDIRECT("1:" & LEN(R2)-LEN(SUBSTITUTE(R2,";",""))+1))-1)+1,255)),Sheet2!$A$2:$A$100,0)),1))
Press CTRL+SHIFT+ENTER to enter array formulas.

Dante Amor
	A
1	NAMES
2	ana
3	amor
4	daniel
5	gloria
6	dante
7	maría
8	pedro
9
10
Sheet2

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

Hi - thank you for taking the time to reply.

This seems to partially work. It is giving me either a 1 or 0 in Column S, indicating whether there is a match or not. But it does not appear to be counting the number of matches.

For example, if there are 5 matches in R2, S2 is displaying 1, rather than 5.
If there are no matches, then it is displaying 0 as expected.

Does something in the formula need amending to count the number of matches?

Maybe the format of the names in Sheet2 are affecting this? I should have said but they are formatted like this: Surname, FirstName (Location)

They are in the same format in Column R of Sheet1, only difference is they have a ; between them.

Thank you

 

[DanteAmor]

=SUM(IF(ISNUMBER(MATCH(TRIM(MID(SUBSTITUTE(R2,";",REPT(" ",255)),255*(ROW(INDIRECT("1:" & LEN(R2)-LEN(SUBSTITUTE(R2,";",""))+1))-1)+1,255)),Sheet2!$A$2:$A$100,0)),1))

Press CTRL+SHIFT+ENTER to enter array formulas.

It is a array formula to enter it you must press the Shift + Control + Enter keys at the same time. That is, edit the formula and there you press the 3 keys, not just Enter.

If entered correctly, Excel will surround with curly braces {}.

Note: Do not try and enter the {} manually yourself.​

 

[chive90]

It is a array formula to enter it you must press the Shift + Control + Enter keys at the same time. That is, edit the formula and there you press the 3 keys, not just Enter.

If entered correctly, Excel will surround with curly braces {}.
View attachment 96160

Note: Do not try and enter the {} manually yourself.​

Ah - thank you, I missed that bit.

Appears to work perfectly. Thanks for your help

 

[chive90]

It is a array formula to enter it you must press the Shift + Control + Enter keys at the same time. That is, edit the formula and there you press the 3 keys, not just Enter.

If entered correctly, Excel will surround with curly braces {}.
View attachment 96160

Note: Do not try and enter the {} manually yourself.​

Hi again - quick question on this. Is the '255' a limit of how much of the string in Column R that the formula is checking?
I ask because in many cases, the string is longer than 255. There may be, for example, 100 names - so I think with this being 255, it is not counting everything. Unless there is another error which is causing an incorrect count.

For example, in Sheet1 R100 I have 11 names. In Sheet2, 4 of these names exist in Column A however, the number in S100 is returning a 2.

Thank you!

 

[DanteAmor]

Is the '255' a limit of how much of the string in Column R that the formula is checking?

It is only a number that we put out of habit, but you can change it, for example, to 1500:

=SUM(IF(ISNUMBER(MATCH(TRIM(MID(SUBSTITUTE(R2,";",REPT(" ",1500)),1500*(ROW(INDIRECT("1:" & LEN(R2)-LEN(SUBSTITUTE(R2,";",""))+1))-1)+1,1500)),Sheet2!$A$2:$A$100,0)),1))

 

[chive90]

It is only a number that we put out of habit, but you can change it, for example, to 1500:

=SUM(IF(ISNUMBER(MATCH(TRIM(MID(SUBSTITUTE(R2,";",REPT(" ",1500)),1500*(ROW(INDIRECT("1:" & LEN(R2)-LEN(SUBSTITUTE(R2,";",""))+1))-1)+1,1500)),Sheet2!$A$2:$A$100,0)),1))

Thanks. I thought this was the case. However, I am having issues when changing to a larger number.

For example, with the original value of 255:

=SUM(IF(ISNUMBER(MATCH(TRIM(MID(SUBSTITUTE(R35,";",REPT(" ",255)),255*(ROW(INDIRECT("1:" & LEN(R35)-LEN(SUBSTITUTE(R35,";",""))+1))-1)+1,255)),Sheet2!$A$2:$A$9000,0)),1))

S35 (where I am entering the above formula) = 10

However, when I change 255 to 1500 (and yes also pressing ctrl+shift+enter):

=SUM(IF(ISNUMBER(MATCH(TRIM(MID(SUBSTITUTE(R35,";",REPT(" ",1500)),1500*(ROW(INDIRECT("1:" & LEN(R35)-LEN(SUBSTITUTE(R35,";",""))+1))-1)+1,1500)),Sheet2!$A$2:$A$9000,0)),1))

S35 (where I am entering the above formula) = 0


I have manually counted Sheet2 Column A and there are 12 matches with R35. So the first code is maybe failing because of the 255 character limit? (There are 787 characters across these 12 names with spaces) and the second code is returning 0 for an unknown reason... it should be returning 12.

Are you able to help? Many thanks

 

[DanteAmor]

I did a test with 950 characters in the cell and a length of 1000 and it works to find 23 names.

Try:

=SUM(IF(ISNUMBER(MATCH(TRIM(MID(SUBSTITUTE(R35,";",REPT(" ",1000)),1000*(ROW(INDIRECT("1:" & LEN(R35)-LEN(SUBSTITUTE(R35,";",""))+1))-1)+1,1000)),Sheet2!$A$2:$A$9000,0)),1))

 

[DanteAmor]

I did a test with 950 characters in the cell and a length of 1000 and it works to find 23 names.

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.