Formula for percent match to a multi select column

eceisin

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

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?

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

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

eceisin

There are times where users selected one or two.

Paul Newcome

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?

eceisin

Mentor match to the mentee

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.

eceisin

THIS IS AMAZING!!! Thank you so much!

Pam Ferguson

@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.