Pivot Table Help - Is this even possible???

spydey

Active Member
Joined
Sep 19, 2017
Messages
314
Office Version
  1. 2013
Platform
  1. Windows
I have a large data set and I am hoping to create a pivot table off of it.
My concern is that I don't think it is possible to do how I am envisioning it. I keep getting a big red "X" and denied sound in my head when I think about how to create this pivot table.
My data table has multiple "Milestone" columns. The milestones are: Sent, Received, Scheduled, Worked, & Completed.
For each of those milestone columns, I have a helper column to indicate where a particular record is at within the different milestones.
For example, Past Due >90, Past Due 61-90, Past Due 31-60, Past Due 1-30, Due in 0-30, Due in 31-60, Due in 61-90, Due in >90. Let's call these the "Time Frame Categories".
So a single record could have one of any of those Time Frame Categories for any of the various Milestones.
I also have each record categorized as Easy or Hard. This is the Type Category.

I need to show the Milestones as columns in my pivot table, and the Type Categories as rows, with the Time Frame Categories as rows too, under the Type Categories.
So a record will be counted 5 times, once for each milestone.

I need it as a pivot table so that I can drill down to the individual levels of the various intersects between the rows and columns.

The image is an example of what I am looking for.

Example Pivot.png



The issue I foresee is that the Time Frame category is not applicable to the record as a whole, but applicable to the milestone of the record, with each record having multiple milestones, and each milestone having a different possible Time Frame than other records for the same milestone. This causes the record to be counted 5 times, whereas a pivot table, to my understanding, only provides data on a record once, based upon the various row, column, and filter selections of the pivot table fields.

I honestly don't think that it is possible, unless I change the layout of my source data for the pivot table, but I really need to keep it the same. I need to be able to drill down based upon a selection from the pivot table and present the data in the order and fashion needed.

I have done many pivot tables in the past, but something of this type has always stumped me.

Any thoughts, ideas, suggestions, recommendations, would be very helpful!!

Thank you!!

-Spydey
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Hello Spydey

Thanks for the good work providing the description.

Also, there needs to be some sample data. Enough records to show how things are in the source table and the corresponding results. Please post some sample source data that can be copied into Excel.

thanks, Fazza
 
Upvote 0
Hi,
A bit difficult to provide assistance without seeing the database.
Just a thought:
"My data table has multiple "Milestone" columns. The milestones are: Sent, Received, Scheduled, Worked, & Completed. "
Usually better to have one (1) column per type, so here one Column for the Milestone type, and another column for the date (if it is indeed a date) would make it easier for a Pivot Table or Query extraction.
 
Upvote 0

Forum statistics

Threads
1,224,816
Messages
6,181,143
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top