I have the following data which I wish to pivot:
Student JoinDate ReleaseDate John 01/05/2011 05/05/2011 James 02/05/2011 04/05/2011
I would like to create a pivot table which shows me a count of students within a given date range:
01/05/2011 02/05/2011 03/05/2011 04/05/2011 05/05/2011 1 2 2 2 1
Now, the issue I have is that I want the pivot table to somehow expand out the dates, I can't figure out how to achieve this with my current data structure.
I can achieve it with the following structure:
Student JoinDate ReleaseDate PivotDate John 01/05/2011 05/05/2011 01/05/2011 John 01/05/2011 05/05/2011 02/05/2011 John 01/05/2011 05/05/2011 03/05/2011 John 01/05/2011 05/05/2011 04/05/2011 John 01/05/2011 05/05/2011 05/05/2011 James 02/05/2011 04/05/2011 02/05/2011 James 02/05/2011 04/05/2011 03/05/2011 etc...
But this technique doesn't scale very well given the amount of data in use, I'm easily hitting the maximum number of rows for a single sheet in the workbook.
Does anyone have any suggestions as to how I can create the desired pivot table without having a row for each date the student is 'active'..?