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

Calculate aggregates in related 2d arrays

D

David Ocampo

New Member
Hi all,

For this question, please refer to the attached workbook.

I have two arrays (matrices):
  1. Users
  2. Jobs
In the Users tab, I have all my users (column A) and their associated skills (columns B:D). Each skill can have a score between 0 and 10.

In the Skills tab, I have jobs (column A) and which skills apply to qualify for said jobs (columns B:D). In columns E:G, I would like to have the top 3 users that qualify based on the skills that apply. For example, for inventory control, skills A and C apply and, therefore, whomever has the highest score for skills A and C would be returned.

I think this problem calls for an array formula, which is an opportunity for growth for me. Any assistance is appreciated.
 

Attachments

Hi:

The way you have set up your data will not give what you are looking for. You have 3 skill set and I assume each skill set is independent of each other, in that case which skill set will get priority over the other. For eg: your Skill B the highest score is for Henry and for Skill A highest score is for David, supposing if a job uses both Skill A & B whom do you recommend (David Or Henry) as your first choice to do the job.

Thanks
 
NARAYANK991 said:
Hi ,

See if this is acceptable.

Narayan
Click to expand...

Wow! :awesome:

I didn't think it was possible. The one thing is that there are no values returned when all three are "Y", but I can try to reverse engineer your solution (as I try to learn a new function, MMULT)

Thank you so much for taking the time to build this!

David
 
Nebu said:
Hi:

The way you have set up your data will not give what you are looking for. You have 3 skill set and I assume each skill set is independent of each other, in that case which skill set will get priority over the other. For eg: your Skill B the highest score is for Henry and for Skill A highest score is for David, supposing if a job uses both Skill A & B whom do you recommend (David Or Henry) as your first choice to do the job.

Thanks
Click to expand...

Nebu, I was thinking of using the LARGE function to return the respective aggregate. So, if skills A & B are required, we would aggregate those two and the top three users with those skills would be returned.

David
 
David Ocampo said:
Thank you so much for taking the time to build this!
David
Click to expand...
Hi ,

Actually the problem is when two people have the same score ; in this case , the two names are not output , instead the same name is repeated twice.

In your existing data , this happens when all three skills are selected "Y" ; in such a case , both Jeff and Henry have identical scores , but the formula repeats Jeff , instead of outputting both the names.

If you don't mind using helper cells , it becomes easier , and a formula can be given to output the correct names even when there are duplicates.

Narayan
 
John Jairo V said:
Hi to all!

Similar idea with single formula and no CSE. Blessings!
Click to expand...
One thing: duplicates still appear if users have the same score. For instance, if David and Henry have an "8" for Skill A, David appears twice.

I see that you used an array in the second input of the MMULT function, was that to generate unique results?
 
David Ocampo said:
One thing: duplicates still appear if users have the same score. For instance, if David and Henry have an "8" for Skill A, David appears twice.

I see that you used an array in the second input of the MMULT function, was that to generate unique results?
Click to expand...

Ok... check this new formulae. The "Row part" sums a little increment for avoiding duplicates. Blessings!
 

Attachments

You must log in or register to reply here.
Back
Top