Pivot table to count dates from two columns

  • Hi, I have the same question as in this thread but I could not solve it based on the provided answer.

    The question is: how from source data like this..

    Project - Original Date - Real Date
    Project A - 01-01-11 - 01-01-11
    Project B - 01-01-11 - 01-02-11
    Project C - 01-02-11 - 01-02-11

    ..create pivot table like this?

    Date - Count of Original Date - Count of Real Date
    01-01-11 - 2 - 1
    01-02-11 - 1 - 2

    The original thread has an attached file as a solution but I cant understand how to do it. I have these problems with it:

    1. The Alt,D,P to open pivot wizard does not work. It opens Text to Column window instead. How can I open the wizard?

    2. The macro button "refresh" generates new random data in the source but the pivot table does not update. When I press "refresh" button in the ribbon it gives error. Also Change Data Source button gives error.

    3. How to create the "Column" field with [Orig;Real] data points? And how to create the "Value" field with all the dates in it? When I create my own pivot table from the source data I get three columns (Proj;Orig;Real) with data in them from respective columns.

    I use Excel 2010, Czech version, Win7. I wanted to reply in the original thread but it wont let me to post question in other member's thread..

    Thanks in advice for any help!

  • Re: Pivot table to count dates from two columns

    Thanks a lot, that did solve the problem but I ran into new ones..

    My source table is actually larger, it has about 20 columns, not just the 3. The data in the columns is of various data types(dates, values, yes/no, text, blanks..). When i normalize it, it puts all the data into one field called Values. This however limits the usability of the pivot table. For example:

    If I want to group the date counts by month I can't because the Value field contains dates as well as other data types.

    If I want to filter the pivot table by a certain column I can't because the column is inside the Value field.

    There are many other things that would be possible with standard pivot table which aren't with a normalized one. I needed to used the normalized table to count dates from multiple columns, however, if I can't use most of the functionality of standard one, it's not really useful. Any ideas?

    Attached is part of the data that I'm working on with the pivot tables. forum.ozgrid.com/index.php?attachment/37134/

  • Re: Pivot table to count dates from two columns

    All you need to do is to insert a column to make a single field for your unique row identifier before going through normalization process ... and afterwards split the field again. I've done that with your example data, and added a field to split out dates separately, before generating a final pivottable ...

  • Re: Pivot table to count dates from two columns

    I think I'm finally getting to where I needed to be although its a bit more complicated than I expected:) One more question.. Looking at Glenn's Final Pivot table, is it possible to insert a calculated column into the PT, that would be equal to (count of Date:Received)-(count of Date:End)? i.e. for 2011 it would be 7-8=-1

    Thank you again for your time and help. I really appreciate it!

  • Re: Pivot table to count dates from two columns

    And also, is it possible to delete calculated field that I've inserted in a PT?

  • Re: Pivot table to count dates from two columns

    To delete a calculated field, go into the Formula definition dialog, and select the formula name from the drop-down, and press the Delete button.

    As for doing something like the calculation you want ... I recommend doing that outside of the pivottable. Use formulas entered into an area next to the pivottable. I know it's not very flexible, but with the groupings in place I think that's the best solution.

    Cheers, Glenn.

    Beauty is in the eye of the beer-holder.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!