SOLVED

Searching list of names within a cell

Copper Contributor

Hi there,

 

I would need to produce a scorecard for the company. One of the criteria is google review.

What I had done:

Using "Name Manager" to assign all the staff name as StaffName.

Using below formula to search the name within the comment cell

=IFERROR(INDEX(StaffName,MATCH(TRUE,(ISNUMBER(SEARCH(StaffName,T2,1))),0),1),"") 

Above formula got me the name and I perform count if to find out how many comments each staff got.

 

The problem I'm facing is there are some comments mentioning multiple names. For example "Mary, Peter, John all did a great job"

 

The result I'm looking for is Mary, Peter and John all gets one count for this comment. How can I achieve that?

 

Thanks in advance for your time and patience.

 

Bowen

3 Replies

@Bowen0520

Could you make a sample workbook (without sensitive data) available via OneDrive, Google Drive, Dropbox or similar?

best response confirmed by Bowen0520 (Copper Contributor)
Solution

@Bowen0520 

Using 365

= LET(
    isMentioned, SIGN(ISNUMBER(SEARCH(staffName, TOROW(comments),1))),
    BYROW(isMentioned, LAMBDA(crit, SUM(crit)))
  )

Using legacy Excel

= MMULT(
    SIGN(ISNUMBER(SEARCH(staffName,TRANSPOSE(comments),1))),
    SIGN(ISTEXT(comments))
  )

entered with CSE.

image.png

 

 

A big THANK YOU for @Peter Bartholomew

 

1 best response

Accepted Solutions
best response confirmed by Bowen0520 (Copper Contributor)
Solution

@Bowen0520 

Using 365

= LET(
    isMentioned, SIGN(ISNUMBER(SEARCH(staffName, TOROW(comments),1))),
    BYROW(isMentioned, LAMBDA(crit, SUM(crit)))
  )

Using legacy Excel

= MMULT(
    SIGN(ISNUMBER(SEARCH(staffName,TRANSPOSE(comments),1))),
    SIGN(ISTEXT(comments))
  )

entered with CSE.

image.png

 

 

View solution in original post