Single instance of column item in Pivot table

[Audrey1]

OK so I have this planification sheet on classes and I need to figure out how busy the teachers will be. Here's the data I have (see attached sheet for correct formatting!)

nb credits class will be given on class will be given again on class will be given again on
class1 teacherA 2 1st trimester 2017 1st trimester 2018 1st trimester 2019
class2 teacherB 3 2nd trimester 2017 1st trimester 2018 2nd trimester 2019
class3 teacherA 1 1st trimester 2018 2nd trimester 2018 3rd trimester 2019


And the data I'd like to get: (the numbers in the cells are the total of credits in this trimester)
1st trimester 2017 2nd trimester 2017 3rd trimester 2017 1st trimester 2018 etc
teacherA 2 0 0 3
teacherB 0 3 0 3

I'm currently using a Pivot Table, but the columns are listed twice (I have put the 'class will be given on', 'class will be given again on', etc. as columns).

I can't seem to regroup them correctly, using the Grouping option. Any hints on how I could achieve this?

Thanks

[xlnitwit]

Hi,

Unfortunately that is not a good data layout for a pivot table. Your data needs to be put into a normalised structure like this:

Column1	Teacher	nb credits	class will be given on
class1	teacherA	2	1st trimester 2017
class1	teacherA	2	1st trimester 2018
class1	teacherA	2	1st trimester 2019
class2	teacherB	3	2nd trimester 2017
class2	teacherB	3	1st trimester 2018
class2	teacherB	3	2nd trimester 2019
class3	teacherA	1	1st trimester 2018
class3	teacherA	1	2nd trimester 2018
class3	teacherA	1	3rd trimester 2019

The pivot table will then simply require the class trimester to be a column field, teacher a row field and any field as the data counter.

Sum of nb credits	Column Labels
Row Labels	1st trimester 2017	2nd trimester 2017	1st trimester 2018	2nd trimester 2018	1st trimester 2019	2nd trimester 2019	3rd trimester 2019	Grand Total
teacherA	2		3	1	2		1	9
teacherB		3	3			3		9
Grand Total	2	3	6	1	2	3	1	18

[peterrc]

Hi,

Welcome to the Forum.

Had a quick look at your file and would comment as follows:-
You have asked for the following:-
1st trimester 2017 2nd trimester 2017 3rd trimester 2017 1st trimester 2018, but "3rd trimester 2017" isn't in the original Data.
Also, under Pivot Table > Design > Subtotals, have you selected "Do Not Show Subtotals" ?

Regards

peterrc

[Audrey1]

Also, under Pivot Table > Design > Subtotals, have you selected "Do Not Show Subtotals" ?

Great, I didn't have a look at these options. Which made me realize there was also a Timeline available, which looks like what I'm trying to use. I could reformat "1st trimester 2018" as a date format like "Jan 1, 2018" and use it in the timeline. But I only seem to be able to use the timeline as a filter though, not as an embedded column (Ideally, I would get a column per trimester... even though I don't have any data). So maybe using a PivotTable is not the proper way to do it, I'm not sure. How could I get a kind of 'easy gantt chart' that shows a timeline with the credits being given per teacher under? (Edit: just to be clear, I don't want a Gantt chart per say, I'm just interested in the top timeline you have in a Gantt chart... In my case, I'd use trimesters as columns of a timeline.)

Thanks again!