Excel 2010 Pivot Table: show unique occurrances

[Katrin620]

Hi everyone,

I have been trying to solve this problem for weeks and have not been able to find a solution.
I hope someone might have an idea that has escaped me. I have searched through old posts but cannot find a solution.

I am using Excel 2010 on a computer running Windows 7.

I have an extensive table which lists all information regarding trainings that are run at a company.
It includes many more fields than I have included below, but I hope this can demonstrate the problem.
The table feeds several pivot tables which provide statistics and forecasts.

I am trying to create a pivot table which shows scheduled training (training status: 'scheduled').
I have been able to create one which shows scheduled trainings, in a monthly format, like a calendar,
but it shows participant number, not unique trainings.

How can I show unique trainings?

I have read I could do this by making an extra column, and then counting unique values, but i canno think of any way
to create such values that are unique for each training yet are the same for all participants of one particular training.
The problem is that several trainings can run at once, even for the same topic. There can he between 1 and 6 participants per training.

Participant	Training	Training Status	Training Start	Training End
John Jones	Topic 1	Scheduled	01.10.2014	05.10.2014
Tim James	Topic 2	waiting list
Tom Michaels	Topic 2	Completed	01.04.2014	05.04.2014
Dawn Marks	Topic 3	waiting list
Tim James	Topic1	Scheduled	16.12.104	21.12.2014
Jeff Nichols	Topic 1	Scheduled	01.10.2014	05.10.2014
Dawn Marks	Topic 1	Scheduled	16.12.104	21.12.2014

<tbody>
</tbody>

I would appreciate any advice.

Kind regards,
Kat

 

[Jerry Sullivan]

Hi Kat and Welcome to MrExcel,

Could you post a small screen shot of your desired result?

Is it correct that a unique scheduled training occurrence would be one that has a unique combination of values in the fields: Training, Training Start, and Training End (along with Training Status="Scheduled")

 

[Katrin620]

Hi Jerry,

here is a screenshot of the table as it is now and below an explanation of how I would like it to appear:

Currently the numbers shown are the individual participants and not the unique trainings (i.e. it should be 1 training in Location 2 in August, one in September, and 2 in October. Total for that location: 4).

I have made a column into my table which combines a lot of the information, and I am now certain it is unique, but still cannot find a way to process this further.

Generally I am not slow on the uptake but this one really has me stumped!

Thanks so much and kind regards,
Kat

 

[Jerry Sullivan]

Kat, You could use a formula like the one shown in F2 to accomplish that objective.

Place field "Sum of UniqueCount" in the Values area of your PivotTable.

Excel 2013
	A	B	C	D	E	F
1	Participant	Training	Request Status	Training Start	Training End	UniqueCount
2	Tom Michaels	Location 1	Scheduled	01.04.2014	04.04.2014	1
3	John Jones	Location 1	Scheduled	01.10.2014	04.10.2014	1
4	Dawn Marks	Location 1	Scheduled	20.12.2014	23.12.2014	1
5	Dawn Marks	Location 1	Completed	04.01.2014	07.01.2014	0
6	Tim James	Location 1	Scheduled	16.12.2014	19.12.2014	1
7	Dawn Marks	Location 2	waiting list			0
8	Dawn Marks	Location 2	Scheduled	01.10.2014	04.10.2014	1
9	Jeff Nichols	Location 2	Scheduled	01.10.2014	04.10.2014	0
10	Tim James	Location 2	Scheduled	01.10.2014	04.10.2014	0
Sheet1

Cell Formulas
Range	Formula
F2	=IF(COUNTIFS(C$1:C2,"Scheduled",B$1:B2,B2,D$1:D2,D2,E$1:E2,E2)=1,1,0)

If you have some familiarity with SQL, another approach would be to use a query to aggregate unique trainings, then use the result of that query as the PivotTable data source. That method has the benefit of not requiring a helper column in your data source range.

 

[Katrin620]

Hi Jerry,

ok this works, but only if the excel file is sorted by training status.
Is there any way to make this work even if the table is not sorted?

I wish I could could show the original table, but I would need to black out so much data that it would'd help.
I have 23 columns but that shouldn't matter I hope.

Thank you for your help and wishing you a relaxing weekend,
Kat

 

[Jerry Sullivan]

ok this works, but only if the excel file is sorted by training status.
Is there any way to make this work even if the table is not sorted?

Kat, It works regardless of whether the data is sorted. The formula marks the first unique combination of fields Training, Start and End where Status = "Scheduled".

The number of columns in your data source shouldn't matter, provided you aren't using filters other than the ones you show in Post #3. The approach would break down for example if you were applying a filter for Business = "Business A" because the first record for that unique training might be for "Business B".

 

[Katrin620]

I guess that would be why it isn't working then. The table is constantly filtered by valious criteria depending who is looking at it and for what reason.
I think I am giving up! Have spent way too much time on it.
Thanks for the suggestions.