Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Analysis
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Analysis
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/2...le-parameters/ best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "TimB" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Analysis
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 "TimB" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Analysis Toolpak-Confidence Level and data analysis questions | Excel Worksheet Functions | |||
Data Analysis | Excel Discussion (Misc queries) | |||
Why " data analysis plus " override " data analysis " once instal. | Excel Worksheet Functions | |||
Analysis ToolPak installed but no Data Analysis option | Excel Discussion (Misc queries) |