Data Analysis

[TimB]

I am analyzing a large database. In column “A†is a list of employee names,
each appearing multiple times according to how many jobs they have done. In
column “B†is the type of jobs they have done, of which there are three
types, indicated by letters: “Câ€, “W†and “Iâ€.
What I am trying (But failing) to accomplish is to determine how many times
employee “Joe Employee†has done each type of job. Any help would be
appreciated.
The output that I am looking for would look something like this:

Employee C W I
Joe Employee 25 10 15

 

[Bernard Liengme]

A) using SUMPRODUCT
I will assume your employee names are in A1:A100, and the codes are in
B1:B100 (adjust my answer as needed)
In K1, L1, M1 enter the three job codes: "C", "W" and "I".
Starting in cell J2 and going down the column, enter a list of employee
names (once each)
In K2 enter =SUMPRODUCT(--(A1:A100=$J2),--(B1:B100=K$1))
Copy this across to M2 and down to row 100
Done
For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html

B) Pivot Table - takes a little while to get you head around this but it is
the best way. You will not need to type in the list of unique employee
names - the Pivot Table wizard does this!
See one or more of these

http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm
http://www.techonthenet.com/excel/pivottbls/index.htm
http://www.dicks-blog.com/archives/2005/06/23/download-pivottable-parameters/


best wishes

 

[Shane Devenshire]

Hi,

Best way to handle this is with a pivot table.

1. Select your data with one row of titles
2. Choose Data, PivotTable and PivotChart Report,
3. Click Next twice
4. Choose Layout and drag the Employee field button to the row area and the
Type field button to the Column Area, and then drag the Type field button to
the Data area (so it is in two locations)
5. If the data button reads Sum of Type then double-click it and change to
Count. Click OK once or twice
6. Click Finish

If this helps, please click the Yes button

Cheers,
Shane Devenshire