• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Returning string matches

GreetingsJoe

New Member
Hello all,

I'm in need of some assistance with a project I'm working on. I have a list of project managers that are posting skill sets they are looking for in individuals to assist their teams. The solution (cells C3:C5) will search a list of employees and skills and return the Top 3 names of the employees with the best matches. The matches will always be exact as this is from a MS Form. The only difference is that I believe one list may have comma separated versus semi colon. I'm thinking that doesn't matter regarding how the formula would be composed.

Top 3 is determined by count of number of matches. In cell C3, Barb is listed first because she had 2 of the 3 skills required. Kim is listed as 2nd and also has 2 of the 3 skills required. I don't need a tie breaker, grabbing the first match is ok. Jackson is listed as 3rd because he only has 1 skill match.

The Employee list may contain a few hundred employees etc. The solution would return the top 3 from that list per each project manager.

Any questions, please ask.

Joe
 

Attachments

  • Excel skill matching.xlsx
    9.5 KB · Views: 13
That is a bit of a nightmare, made worse by the fact that the employee skills have been concatenated. The function TEXTSPLIT has just been released for beta testing in 365, but it hasn't come my way yet. As things stand, I think it would be easier to rearrange your data layout than write a formula you might choose to live with. Using 365, I have
Code:
= MAP(SkillsRequired,
     LAMBDA(skillsReq,
       LET(
         skillsRq,      TRIM(SPLIT.TEXT(skillsReq,{",",";"})),
         skillsAv,      TRIM(SPLIT.TEXT(SkillsAvailable,{",",";"})),
         skillsLocated, ISNUMBER(XMATCH(skillsAv,skillsRq)),
         identified,    BYROW(skillsLocated, LAMBDA(located, OR(located))),
         names,         FILTER(Employees, identified),
         TEXTJOIN(", ", , names)
       )
     )
   )
and I still need to select the 'top 3'.
[TEXT.SPLIT is from Charles Williams's FastExcel add-in as a stopgap]
78120
 
Try this longer formula, work for Excel 2019 and above version.

In C3, array ("Ctrl+Shift+Enter") formula copied down :

=TEXTJOIN(", ",,FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,IFERROR(INDEX($G$3:$G$7,N(IF({1},AGGREGATE(15,6,ROW($A$1:$A$5)/((LEN($H$3:$H$7)-LEN(SUBSTITUTE($H$3:$H$7,TRIM(MID(SUBSTITUTE(","&B3,",",REPT(" ",50)),50,50)),"")))>0),ROW($A$1:$A$3))))),""),IFERROR(INDEX($G$3:$G$7,N(IF({1},AGGREGATE(15,6,ROW($A$1:$A$5)/((LEN($H$3:$H$7)-LEN(SUBSTITUTE($H$3:$H$7,TRIM(MID(SUBSTITUTE(","&B3,",",REPT(" ",50)),99,50)),"")))>0),ROW($A$1:$A$3))))),""),IFERROR(INDEX($G$3:$G$7,N(IF({1},AGGREGATE(15,6,ROW($A$1:$A$5)/((LEN($H$3:$H$7)-LEN(SUBSTITUTE($H$3:$H$7,TRIM(MID(SUBSTITUTE(","&B3,",",REPT(" ",50)),150,50)),"")))>0),ROW($A$1:$A$3))))),""))&"</b></a>","//b[not(preceding::*=.)]"))

78130
 
Try this longer formula, work for Excel 2019 and above version.

In C3, array ("Ctrl+Shift+Enter") formula copied down :

=TEXTJOIN(", ",,FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,IFERROR(INDEX($G$3:$G$7,N(IF({1},AGGREGATE(15,6,ROW($A$1:$A$5)/((LEN($H$3:$H$7)-LEN(SUBSTITUTE($H$3:$H$7,TRIM(MID(SUBSTITUTE(","&B3,",",REPT(" ",50)),50,50)),"")))>0),ROW($A$1:$A$3))))),""),IFERROR(INDEX($G$3:$G$7,N(IF({1},AGGREGATE(15,6,ROW($A$1:$A$5)/((LEN($H$3:$H$7)-LEN(SUBSTITUTE($H$3:$H$7,TRIM(MID(SUBSTITUTE(","&B3,",",REPT(" ",50)),99,50)),"")))>0),ROW($A$1:$A$3))))),""),IFERROR(INDEX($G$3:$G$7,N(IF({1},AGGREGATE(15,6,ROW($A$1:$A$5)/((LEN($H$3:$H$7)-LEN(SUBSTITUTE($H$3:$H$7,TRIM(MID(SUBSTITUTE(","&B3,",",REPT(" ",50)),150,50)),"")))>0),ROW($A$1:$A$3))))),""))&"</b></a>","//b[not(preceding::*=.)]"))

View attachment 78130
Wow. This is amazing and works great. Thank You soo much
 
Back
Top