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

Select Top (x) per category no duplicates

B

barmacost

New Member
I'm hoping someone has had this problem before and can help me with a strategy or formula but I have a list of processes and the names of people who have completed those processes and a composite score assigned to each individual. Each person is capable of completing multiple processes and therefore will show up in the list multiple times. I need to find a way to select the top 5 people per process without duplicates. So if someone has a score that ranks them as #1 for one process and #3 for another process then they should only show up in the list one time.
 
Hi Brian ,


Is it possible you can upload a sample worksheet , which will make it easier for others to offer a solution in the shortest possible time ?


Narayan
 
Hi, barmacost!

Tried functions SMALL and/or LARGE?

Maybe you need an auxiliary/helper column to simulate grouping by.

Regards!
 
Here is a link to a similar looking file. Hope it works...first time using RapidShare site.


https://rapidshare.com/files/1046479104/Book1.xls
 
Hi Brian ,


Thanks for the sample worksheet.


Referring to your initial post , Susan is #1 in task 2 , and #4 in task 1 ; should she not appear in the top 5 for task 1 , or in the top 5 for task 2 ?


Narayan


P.S. Don't you think that a table will give more information ? Check out the following link :


https://skydrive.live.com/#!/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21154


Please do not click on the hyperlink ; copy + paste the entire address in your browser.
 
I would be fine if the first task the person appears in is the one that gets priority but the difficulty is that there are 187 of these tasks with varying lengths of users (some have hundreds some only a few) so Susan may appear again for another task that only has two people in which case she has to be selected for that task because each task needs at least one person selected.
 
Hi Brian ,


Do you think this will be better ?


https://skydrive.live.com/#!/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21155


Please do not click on the hyperlink ; copy + paste the entire address in your browser.


There is a problem in handling two persons with the same score ; I hope I can solve this , but tell me what you think of this format. The tasks are in rows , since you say there may be hundreds of them.


Narayan
 
That's originally how I started to work the problem but to be honest that's where I got stuck. I couldn't figure out a sophisticated formula that would give me the top 5 and take into consideration all the other names already selected. Every place holder for each task would need such a formula in order to account for all the changes that will have happened between task 2 and task 180.
 
You must log in or register to reply here.
Back
Top