Advanced Lookup Quadry?

[doop]

In column A I have a list of employees.
In column B I have the department code relating to each employee
In columns C through N I have the months of the year and the number of days each employee has worked in each month.
In column O I am summing columns C through N, so that for each employee I have the total number of days that they have worked this year.

What I want to do is to have a new sheet which has the 4 department names in Column A and in Columns B,C,D and E I want to be able to return the names of the the top 5 employees by attendance, for each department, by reference to my main worksheet, descibed above.

Perhaps I am missing something but it is proving a tricky formula to come up with.

I have been working on the basis of using an INDEX, SMALL, ROW formula, but the trouble with this is that I can only select the nth smallest values. This may be the wrong track anyway, as I'm having no luck, and want to return the names of the employees with the 5 largest days attendence.

Any ideas/ help appreciated as it's starting to do my head in(!)

 

[acw]

Hi

This will give a partial solution. The source data is in sheet1 and the new output is in sheet2. It has a problem when there are 2 or more entries with the same number of days.

The following formulas are all array entered.

On sheet2

B2
=INDEX(Sheet1!$A$1:$A$6,MAX(IF( (IF(Sheet1!$B$2:$B$6=Sheet2!$A2,Sheet1!$O$2:$O$6,0))=LARGE(IF(Sheet1!$B$2:$B$6=Sheet2!$A2,Sheet1!$O$2:$O$6,0),1),ROW(Sheet1!$B$2:$B$6),0)))

C2
=INDEX(Sheet1!$A$1:$A$6,MAX(IF( (IF(Sheet1!$B$2:$B$6=Sheet2!$A2,Sheet1!$O$2:$O$6,0))=LARGE(IF(Sheet1!$B$2:$B$6=Sheet2!$A2,Sheet1!$O$2:$O$6,0),2),ROW(Sheet1!$B$2:$B$6),0)))

D2
=INDEX(Sheet1!$A$1:$A$6,MAX(IF( (IF(Sheet1!$B$2:$B$6=Sheet2!$A2,Sheet1!$O$2:$O$6,0))=LARGE(IF(Sheet1!$B$2:$B$6=Sheet2!$A2,Sheet1!$O$2:$O$6,0),3),ROW(Sheet1!$B$2:$B$6),0)))

E2
=INDEX(Sheet1!$A$1:$A$6,MAX(IF( (IF(Sheet1!$B$2:$B$6=Sheet2!$A2,Sheet1!$O$2:$O$6,0))=LARGE(IF(Sheet1!$B$2:$B$6=Sheet2!$A2,Sheet1!$O$2:$O$6,0),4),ROW(Sheet1!$B$2:$B$6),0)))

F2
=INDEX(Sheet1!$A$1:$A$6,MAX(IF( (IF(Sheet1!$B$2:$B$6=Sheet2!$A2,Sheet1!$O$2:$O$6,0))=LARGE(IF(Sheet1!$B$2:$B$6=Sheet2!$A2,Sheet1!$O$2:$O$6,0),5),ROW(Sheet1!$B$2:$B$6),0)))

Adjust the ranges to suit. Then copy down to row5.

HTH

Tony

 

[Dutchy]

I reckon any full solution will be very complex (it would take someone with better excel skills than me to supply you with it!)

I hit the same problem as Tony - if you have duplicate day totals you have a problem

Have you considered just copying the data in to sheet 2 and sorting? If you wanted to make it a bit fancier you could do that with VBA (perhaps with a Worksheet Open Event)

 

[hedgie]

You might be able to do something with a pivot table. There is an option under "field Settings" "advanced" which lets you choose only to display the top X# of results. This alone would not work since the top 20 results (5 per department * 4 departments) is not necessarily the same as the top 20 overall, so you will still need a formula to help sort. Maybe by using a modification of the formulas noted above, you can then assign each employee a rank within their category. The nice thing is that it would then be very simple to modify the pivot table if you want to see top 5, or top 10 etc., plus allow users to drill down for further detail.

 

[Aladin Akyurek]

You can run Pivot Tables on your data to obtain desired results. Below an example with Top N set to 3...

Book4
	A	B	C	D	E	F	G	H
1	Emp	Dep	Attendance			Dep	DepC
2	bill	DepA	200
3	damon	DepB	300			Sum of Attendance
4	jon	DepA	255			Emp	Total
5	brian	DepC	300			brian	300
6	tom	DepA	245			carla	300
7	zack	DepB	255			thomas	300
8	juan	DepC	265
9	olga	DepA	255
10	sarah	DepB	245
11	carla	DepC	300
12	bob	DepA	245
13	dan	DepB	265
14	thomas	DepC	300
Sheet1

Otherwise, you need to apply the formula system described for example in:

http://www.mrexcel.com/board2/viewtopic.php?t=69970

per department.