Returning string matches

[GreetingsJoe]

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

 

[Peter Bartholomew]

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

= 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]

 

[bosco_yip]

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::*=.)]"))

 

[GreetingsJoe]

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