Advanced Lookup Quadry?

doop

New Member
Joined
Feb 14, 2005
Messages
1
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(!)
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
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

Well-known Member
Joined
Sep 18, 2004
Messages
560
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

Board Regular
Joined
Jun 23, 2004
Messages
160
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
You can run Pivot Tables on your data to obtain desired results. Below an example with Top N set to 3...
Book4
ABCDEFGH
1EmpDepAttendanceDepDepC
2billDepA200
3damonDepB300Sum of Attendance
4jonDepA255EmpTotal
5brianDepC300brian300
6tomDepA245carla300
7zackDepB255thomas300
8juanDepC265
9olgaDepA255
10sarahDepB245
11carlaDepC300
12bobDepA245
13danDepB265
14thomasDepC300
Sheet1


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

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

per department.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,315
Messages
5,600,908
Members
414,415
Latest member
joshuaba

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top