Pivot Table with merged date fields

Question

I have a source data sheet, each data item having two date fields, startDate and endDate. What I would like to to in excel is generate a pivot table with row headers for each date from either of these columns, and two summary columns, one for Count Started, the other Count Ended.

For example, the following source data:

ItemId  |  startDate  |  endDate

1       |  6/1/16     |  6/2/16

2       |  6/2/16     |  6/3/16

3       |  6/1/16     |  6/3/16

Would produce a pivot table like this:

Date    |  Started    |  Ended

6/1/16  |  2          |  0

6/2/16  |  1          |  1

6/3/16  |  0          |  2

pnuts pnuts · Accepted Answer · 2017-04-23T03:44:46

I doubt I would choose a PivotTable solution for this (that's unlike me!) but I think possible with a PT:

1) Create a PT from multiple consolidation ranges (example here) with ranges A:B and A:C (assuming ItemID is in A1).

2) After 7. select ColumnsA:C (in the new sheet) and apply Remove Duplicates (with all Columns checked).

3) Create a new PT from what remains (Column for COLUMNS, Value for ROWS, Count of Row for VALUES)

4) Right-click on startDate, Move, and click on first option.

5) In PivotTable Options..., Totals & Filters uncheck both Grand Totals and in Layout & Format, Format, check For empty cells show and enter 0.

6) Adjust labels to suit.

Pivot Table with merged date fields

1 Answers