Looking up clients

Here is my dilemma.

My boss wants me to create a "table" where i generate WHO the employee's clients ARE and the hours that the employee spent with them. I have all of the raw data, where the columns are as follows:

A=DATE
B=EMPLOYEE NAME
C=CLIENT
D=ACTIVITY DESCRIPTION
E=HOURS
F=DEPARTMENT

So in short the table would work as: Type in employee name>>>>Table looks up name, then one column generates a client list, then the next column generates the total amount of hours of each client. A=Client, B=Hours

Is this wishful thinking or can this be done?

PS if this can be done, he would probably make me have the hours broken down by department. So it would be A=Client, B-G=Departments, H=Hours

Yes, it can be done. Attach a sample workbook so I can tell you how to do it for your data layout.

Pete

Hi

This sounds like a job for a pivot table.

A quick run through:
Highlight your data range. Go to the Insert tab and click Pivot Table (left hand side).

On the right hand side of the screen, drag the Employee Name in to the Report Filter box. Drag Client in to the Row Labels box, Drag Department in to the Column Labels box, drag Hours in to the Values box.

There's plenty of information online about PTs. They are an extremely useful and powerful tool in Excel. They are easy to set up and well worth learning.

Cheers, Rob.

Sample.xls


Here you go. Thanks Pete

Thanks Rob for the help. However. my boss hates pivot Tables, so unless there is no other option I can't use it.

Ah, I thought you might have done a mock-up of how you would like the secondary table to appear. It will take me a bit longer to set this up.

Pete

Your data shows no variation between Employee and Department, i.e. Ruby is always in Dept. FA, Rosa is in Dept. M, so I'm not really sure how you want the data to look. Can you do a mock-up of the expected output?

Pete

Sample.xls

Pete

Here is the mock up. Changed some numbers and clients to give variation.

That could work, but its Pivot Tables...Boss does not want Pivot Tables

Are you really using Excel 2003? I have it working and was just about to post the file when I noticed your version number in your profile - I've used some functions which will not work in XL2003, so I'll need to change them if you need it to be compatible with that version.

Pete

I have Excel 2003 version and excel 2010 starter. Any problems if its 2010 starter?

No. Try the attached file - just change the employee name using the drop-down in C1 of Sheet2.

Hope this helps.

Pete

EDIT: purely formula-driven, as requested.

Pete

@Popipipo

The Sumproduct helps with the hours. But the company has over 300 clients. I need the table to generate the clients because not every employee works with each client. So this means I have to list 300 clients, which i'm trying to avoid and what the boss doesn't want.

Column G just identifies the employee selected in Sheet2 and for every matching record the formula generates a sequential number, or a hyphen if it doesn't match. The formula in column H identifies unique clients for the selected employee, and again gives them a sequential number. These formulae can be copied down way beyond your data in order to accommodate new data being added.

The formulae in the other sheet just make use of those helper columns to pick up the appropriate data. The clients are listed using an INDEX/MATCH combination looking for those sequential numbers in column H (by means of the ROWS($1:1) function, which increments as it is copied down). The formula in B4 uses a SUMIFS function to add column E on Sheet1 IF column F is the department in B3 AND column C matches the client name in A4 AND column B is the employee selected in the drop-down. The formula is copied across for the other departments, then the block of formulae from A to F can be copied down as far as you may need it. I've used a custom format of General;; to hide any zeros, so that the hours stand out more.

Hope this explains things, and that you can apply it to your real data.

Pete

P.S. If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, I would like to inform you that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

I tried all the formulas and it worked with my data. Thanks Pete and everyone! I will do as you instructed above about properly thanking users here. Cheers