Mapping assignments to participants based on skills overlap

[manikss]

Hi,
I'm facing an issue with respect to running an index match on a set of datapoints. Description of the problem is as follows:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]Skill1
[/TD]
[TD]Skill2
[/TD]
[/TR]
[TR]
[TD]XYZ
[/TD]
[TD]BPM
[/TD]
[TD]Leadership
[/TD]
[/TR]
[TR]
[TD]ABC
[/TD]
[TD]Content management
[/TD]
[TD]Network operations
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Assignments
[/TD]
[TD]Skill1
[/TD]
[TD]SKill2
[/TD]
[TD]SKill3
[/TD]
[/TR]
[TR]
[TD]Assignment 1
[/TD]
[TD]BPM
[/TD]
[TD]Virtualization
[/TD]
[TD]Leadership
[/TD]
[/TR]
[TR]
[TD]Assignment 2
[/TD]
[TD]Data mining
[/TD]
[TD]Network operations
[/TD]
[TD]Content management
[/TD]
[/TR]
[TR]
[TD]Assignment 3
[/TD]
[TD]Data analysis
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Output

[TABLE="width: 500"]
<tbody>[TR]
[TD]Assginments
[/TD]
[TD]Allocated Name
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Assignment 1
[/TD]
[TD]XYZ
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Assignment 2
[/TD]
[TD]ABC
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The candidates/names get allocated as per overlap between the skills mentioned in the two tables above

I've used sumproduct, countif to count the matches and then used a simple index and match function to develop a possible way.

But, i'm very sure there is a better way. Can somebody please guide me. Thanks

Regards,
Manik

 

[AliGW]

Welcome to Mr Excel!

Please share the formula or formulae that you have tried to date and explain in what way they are not good enough for your requirements.

 

[manikss]

Welcome to Mr Excel!

Please share the formula or formulae that you have tried to date and explain in what way they are not good enough for your requirements.

Hi,

Thank you for such a quick response. I'll try my best to explain the issue i'm facing

I've picked up the 4 skills (assume) that each participant has and compared it to the different assignments. For that, i've used a SUMPRODUCT and COUNTIF (for counting exact matches among two ranges). Here's an example:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Assignments/participants
[/TD]
[TD]Assignment #1
[/TD]
[TD]Assignment #2
[/TD]
[TD]Assignment #3
[/TD]
[/TR]
[TR]
[TD]Maggie
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Rob
[/TD]
[TD]1
[/TD]
[TD]3
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Ken
[/TD]
[TD]0
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[/TR]
</tbody>[/TABLE]

The table above shows the overlap each participant has over a specific assignment (Maggie has 2 skills common with assignment #1 and so on). I've then used a max function to pick up the highest value across a column (For Assignment #1, Max[2,1,0]is 2).

Then I've used, INDEX and Match, to pick up the participant corresponding to that value (Assignment #1 gets allocated to Maggie and Assignment #2 gets allocated to Rob and so on...).

But you'll observe that this logic has a flaw. (Doesn't factor in the participant who's already been allocated an assignment or if match overlap is same i.e Maggie and Rob and Ken have similar number of matches.)

Request you to please provide your thoughts on this.

Appreciate all the help. Thanks

Manik