Count unique??

[CzechCzar]

Hi all,

I need help with an employee database request. I have three columns of data: employee ID, job number/classification, and job title. In my organization, job titles can tie to one job number, or several. Example: all Accountant I's within our organization have the same job number, because they do the same job. Engineer I's can have different job numbers, for the different jobs Engineer I's can do (Software Engineers I, Database Engineers I, etc.). An example of job number is 546459 - they are quite long.

I have a list of all employees IDs (unique for all employees), and their corresponding job numbers and job titles. What I want is a list of how many instances of different job numbers correspond to each job title. To take the example above, Accountant I would have one job number, because all Accountant I's have the same job number. Engineer I would have 25 or so job numbers, because we have a whole lot of Engineer I's.

Pivot table doesn't seem to be able to do this, as it counts the number of individuals in a job number, rather than the number of job numbers that would correspond to a title.

The lay out of the spreadsheet is:

Emploee ID | Job Title | Job Classificatiom
001 | Accountant I | 1234
002 | Accountant I | 1234
003 | Engineer I | 2223
004 | Engineer I | 8888

Any help is much appreciated as always!

[playaller]

Hello,

Actually your pivot table can do this if you set it up correctly.

Pivot Physical Setup

1. Highlight all data columns
2. Go to Insert tab
3. Click pivot table icon
4. Tic mark existing worksheet
5. Place cursor in Loction: text box
6. Click the cell location on the sheet to place the pivot table
7. Press OK

Pivot Data Setup

1. Drag the label representing the Job Title to Row Labels
2. Drag the label representing the Job Calssification to Row Labels
3. Drag the label representing the Job Calssification to Values

The PivotTable should show the count for each classification per job in the sub categories for the Job title in the pivot so you may have to Expand or Collapse All of the categories in the pivot table.

[CzechCzar]

Well, slap my face and call me stupid. Thanks very much.

[playaller]

lol. You're more than welcome and i'm glad I could help!