Formula for percent match to a multi select column

eceisin
eceisin ✭✭✭✭✭✭
edited 12/09/19 in Formulas and Functions

Hi, I'm working matching mentors and mentees.  We would like to match them based on attributes they both selected in a multi select column.  As in, choose your top three competencies.  Now I would like to make a formula to determine the percent match of a mentee to a mentor's selection. That is Mentee 1 selected A, B, and C. Mentor 1 selected A, D, E; mentor 2 selected A, B, E; Mentor 3 selected A, B, C.  So mentor 1 should be 33%, mentor 2 should be 66%, and mentor 3 should be 100%. What is the best way to do this with the new multi select drop down columns?

 

Thanks,

Liz

Comments

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    It would honestly be much easier if you were to have either the mentors or the mentees use 3 single select columns as opposed to both using multi-select. Is that an option?

    thinkspi.com

  • eceisin
    eceisin ✭✭✭✭✭✭

    The applications are complete. I could break out into 3 columns after the fact.  Is there a good way to break out the three selections then?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ok. Will it ALWAYS be 3 selections that are made, or is there the possibility someone will only choose one or two?

    thinkspi.com

  • eceisin
    eceisin ✭✭✭✭✭✭

    There are times where users selected one or two.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 11/19/19

    Ok. And which way will you be making the comparison? The mentor is a ##% match to the mentee or the mentee is a ##% match to the mentor?

     

    EDIT FOR CLARIFICATION:

     

    Basically I am asking...

     

    If the Mentee only selects 1 option, but the Mentor has selected 3...

     

    The Mentee would only be a 33% match to the mentor, but the mentor would be a 100% match to the Mentee.

    .

    It could also be asked the other way around... If the Mentor only selected one, but the Mentee selected three.

     

    Where do you want your priority?

    thinkspi.com

  • eceisin
    eceisin ✭✭✭✭✭✭

    Mentor match to the mentee

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ok. HERE is a link to a published sheet. In this sheet you will see where I parsed out the Mentee selections. I then generated a number based on how many matches between mentor and mentee there were and divided that into how many selections the Mentee made. This gave me a percentage of how good a fit the mentor was for the mentee based on what the mentee felt was important.

     

    Please let me know if you need further clarification or help modifying it to fit your exact needs after reviewing.

    thinkspi.com

  • eceisin
    eceisin ✭✭✭✭✭✭

    THIS IS AMAZING!!! Thank you so much!

  • Pam Ferguson
    Pam Ferguson Overachievers

    @Paul Newcome Do you have some time to connect on this? We have a need but I am wondering about the scale of your solution and want to know more.