Pivot table question

[louisp]

Hello,

I have another problem, this time using Pivot tables.

Using Win XP SP2, on Excel 2000 (SP3).

I have a list with the following data sets: employee position, department and name of training session attended. This is a list of employees that have followed a training sessions. (1 empl. can follow more than 1 session)

The desired result is a table displaying the total number of employes who have followed a training session, with the information sorted by position and department. (there can be the same position in different dpts.)

I was able to construct the table but my problem is that I get a total of all training sessions followed - not the number of all employes who have followed a session. My number is much bigger than it should be.

I cannot modifiy the field options to get the numbers of different employes who have followed a session.

See attached pics. Data is an example of the data source, and pivot table is my table with the total counts. Note that employee's position is on the left (catégorie poste) and department is on the top (service). Instead of 868, I should have around 300... (since the table counts total training sessions instead of total number of employees trained)

Can anyone help me???

thank you very much!!!

Louis

[Roger Govier]

Hi Louis

Since in your example data, the same person belongs to more than one
department, it is not surprising to me that the total number counted in
the PT exceeds the number of employees.
If you pulled Emp Code or Last Name to the Row area of the PT, instead
of Department, then your count would be of employees against training
sessions, and, the total would match your number of employees.

You could take Department to a Page field, and then see the total number
of employees from a Department who had attended which course, but the
total of each of these Department totals would come back to the 868
value you currently have.

I hope this helps a little.

--
Regards

Roger Govier


"louisp" <[email protected]> wrote in
message news:[email protected]...
>
> Hello,
>
> I have another problem, this time using Pivot tables.
>
> Using Win XP SP2, on Excel 2000 (SP3).
>
> I have a list with the following data sets: employee position,
> department and name of training session attended. This is a list of
> employees that have followed a training sessions. (1 empl. can follow
> more than 1 session)
>
> The desired result is a table displaying the total number of employes
> who have followed a training session, with the information sorted by
> position and department. (there can be the same position in different
> dpts.)
>
> I was able to construct the table but my problem is that I get a total
> of all training sessions followed - not the number of all employes who
> have followed a session. My number is much bigger than it should be.
>
> I cannot modifiy the field options to get the numbers of different
> employes who have followed a session.
>
> See attached pics. Data is an example of the data source, and pivot
> table is my table with the total counts. Note that employee's
> position
> is on the left (catégorie poste) and department is on the top
> (service).
> Instead of 868, I should have around 300... (since the table counts
> total training sessions instead of total number of employees trained)
>
> Can anyone help me???
>
> thank you very much!!!
>
> Louis
>
>
> +-------------------------------------------------------------------+
> |Filename: DATA.JPG |
> |Download: http://www.excelforum.com/attachment.php?postid=4349 |
> +-------------------------------------------------------------------+
>
> --
> louisp
> ------------------------------------------------------------------------
> louisp's Profile:
> http://www.excelforum.com/member.php...o&userid=25880
> View this thread:
> http://www.excelforum.com/showthread...hreadid=511160
>

[louisp]

hello roger,

thank you for you help. but I still get the same end result.

FYI, an employee can only be in 1 department and position. (one of the examples I gave was "englishized" for comprehension purposes..)

What does increase the number, is the fact that any given employee can follow more than 1 training session. Instead of 868, I should have around 300, which is the total number of participants which have followed a training session.

so if an employee followed 4 sessions, he should be counted as 1 and not 4. That's the crux, I cannot change the count... I was looking into doing a count.if but cannot do so since this is a pivot table, and I could not specify conditions... (could this work????)

I'm now thinking of rearranging the data/table.... Theres also the possibility of creating a calculated field... but this also seems a little complicated...


hoping someone might help me figure this out... thx
louis

[Roger Govier]

Hi Louis

Then go to Table Options and remove the Grand Totals by row and Grand
Totals by column check marks.
The data is the data and will represent the total number of courses
attended. All you can do is tell the PT you don't want totals for the
displayed items.
If you need to check the total number of employees, then still pull Name
into the row area alongside Department.
Click on Department and choose No Subtotals.
Employee name will be in column B.
Insert a new Row 1, above the Pivot table, and in B1 enter
=COUNTA(B6:B500)

--
Regards

Roger Govier


"louisp" <[email protected]> wrote in
message news:[email protected]...
>
> hello roger,
>
> thank you for you help. but I still get the same end result.
>
> FYI, an employee can only be in 1 department and position. (one of the
> examples I gave was "englishized" for comprehension purposes..)
>
> What does increase the number, is the fact that any given employee can
> follow more than 1 training session. Instead of 868, I should have
> around 300, which is the total number of participants which have
> followed a training session.
>
> so if an employee followed 4 sessions, he should be counted as 1 and
> not 4. That's the crux, I cannot change the count... I was looking
> into
> doing a count.if but cannot do so since this is a pivot table, and I
> could not specify conditions... (could this work????)
>
> I'm now thinking of rearranging the data/table.... Theres also the
> possibility of creating a calculated field... but this also seems a
> little complicated...
>
>
> hoping someone might help me figure this out... thx
> louis
>
>
> --
> louisp
> ------------------------------------------------------------------------
> louisp's Profile:
> http://www.excelforum.com/member.php...o&userid=25880
> View this thread:
> http://www.excelforum.com/showthread...hreadid=511160
>

[louisp]

hello again roger,

Your solution does get me the desired result but it is not dynamic; everytime I change departments, i need to redo a count for all positions. (since what you suggested was not part of the PT and the number of rows displayed changes).

I thought there was a way to get just the number of employees who followed a training session - easily. I guess not...

I will continue looking for a solution when I can... in the meanwhile I suppose this will have to do.

I get the impression the data would have to be reformated a little to prevent the sum of all sessions followed...

thank you for your help

louis