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

Find Large Where Range will be with Criteria

P

prasaddn

Active Member
Hi,


I have data three columns like in column1 empl name, col2 employee's Job Level and in column3 his/her years of exp.


I need to find the top 10 (or n) employees with max experience, for the specific Job Level will given in some cell D1.


I could easily find top 10 or n employees with help of Large() function based on helper column which will hold exp * row()/100000. And then using Index and match function. I have solved the problem given by chandoo in http://chandoo.org/wp/2009/11/12/topx-chart/


Now, the challenge is to find the top 10 or n employees from specific Job Level. Job level have 3 constant values like "Entry", "Middle", "Senior".
 
Hi prasaddn,


You could do it this way.


Assume your data is in Column A, B and C in the order that you listed.

Sort the Empl Names in alphabetical order A-Z (optional)


In Cell E2 type in the job level that you want to return the top 10 employees.


Cell F2

=LARGE(INDEX(($B$2:$B$16=$E$2)*$C$2:$C$16,,),ROWS($F$2:F2))

drag down so there are 10 or N of these

This formula returns the Largest N Years of Experience if the Job Level is Senior


Cell G2

=INDEX($A$1:$A$16,LARGE(INDEX(($C$1:$C$16=F2)*($B$1:$B$16=$E$2)*ROW($C$1:$C$16),,),COUNTIF($F$2:F2,F2)))

drag down so there are 10 or N of these

This formula returns the employee name based on years of experience from column F.


The formulas in column G will give you the top N. Be sure to extend your ranges to include all of your data (my test data was in Columns ABC and Rows 2 to 16)
 
Hi, prassadn!

Check this file:

http://www.2shared.com/file/YMBF_JV_/Find_Large_Where_Range_will_be.html

Regards!
 
I think the fastest way would be via a PivotTable. Taking your three columns, put Job Level in the Page field spot, Employee name in the Row field, and Years Exp in the Values. Then set the Employee field column to only display Top 10 (or n), and have it sort descending by years.


With this setup, you can easily look at the different job levels.
 
Hi SirJB7,


I am unable to download file from your mentioned link! Can you please email me at: faseeh10@hotmail.com


Thanks

Faseeh
 
Hi, Faseeh!

Just sent. And I'm wondering what might be happening with 2shared service...

Regards!


@prassadn

Hi!

Please tell me if any problem with downloading. Should I stop using 2shared?

Regards!
 
Thank you All!!


This is fabulous support.


I was using the same formula as suggested by Kyle, but only mistake I did was instead of index was using sumproduct and was wondering.. :)


Thank you again.

Regards,

Prasad DN
 
@All

Hi!

Changing from 2shared to hotfile. It seems to works. Please confirm.

http://hotfile.com/dl/144422707/002d4ad/Find_Large_Where_Range_will_be_with_Criteria_(for_prassadn_at_chandoo.org).xlsx.html

Regards!
 
Thank you SirJB,


Appreciate your efforts, but unfortunately I will not be able to open any files from such sites, due to firewall restriction at office. I am sure others should be able to access.


Regards,

Prasad DN
 
Hi prasaddn,


SUMPRODUCT didn't work because it sums the array and returns a single value, whereas the INDEX function passes the whole array to the LARGE function.
 
You must log in or register to reply here.
Back
Top