Pivot table - combining two sets (columns) of dates into one set of row labels

**Angie178** · 05-16-2012, 12:46 PM

Hi all,

What I would like to do is take all the dates included in two different columns and combining them into one Row Labels list, then having two pivot columns that count matches against

Here's my source data.
Type Ticket # CP Availability Date Prod Deploy Date
Packaged 1143340 3/14/2012 4/19/2012
Packaged 1187676 3/30/2012 6/3/2012
Packaged 1143326 4/11/2012 5/13/2012
Packaged 1162725 4/11/2012 5/13/2012

Here's what I want.

Date Count of CP Availability Date Count of Prod Deploy Date
3/14/2012 1 0
3/30/2012 1 0
4/11/2012 2 0
4/19/2012 0 1
5/13/2012 0 2
6/3/2012 0 1

I can't figure out how to combine the two sets of dates, without them showing up together and the pivot table linking 3/14 and 4/19.

Is this even possible? I can do this with some formulas and not use a pivot table, but a pivot table will help as my source data grows.

Thanks in advance!

**NBVC** · 05-16-2012, 01:50 PM

Not easily possible with the main data setup you have, I don't think....

A way around....

Add another table on the side to summarize.

e.g. in J2 enter formula:

=IFERROR(SMALL(C:D,ROWS($A$2:$A2)),"")

copied down as far as you want to include possible future additions.

in K2 then:

=IF(OR($J2="",$J2=$J1),"",COUNTIF(C:C,$J2))

copied to next column and down the same length as J.

Add headers in J1:L1

Then use that table to create Pivot table...

Use Sum of CP availability and Pro Deploy....

And you can filter out the blank row in Row Labels.