Creating a pivot table from dates where daily dates stretch across the top of your data set. This episode compares the pain of adding multiple value fields to a pivot table versus unpivoting using multiple consolidation range pivot tables.
Fabien sends in an intriguing pivot table question. I show one mildly acceptable way to solve the problem using the existing data and then a way to spin the data to make the problem easier to solve. Episode 1105 shows you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Fabien sends in an intriguing pivot table question. I show one mildly acceptable way to solve the problem using the existing data and then a way to spin the data to make the problem easier to solve. Episode 1105 shows you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Welcome back to the MrExcel netcast, I'm Bill Jelen.
Ah, great data set sent in today by Fabien from France.
He wants to create a PivotTable from this, with Users as the filter field.
Flow going across the columns and then Dates going down the side.
And while it's possible to do this, I'm not really happy with the process that we have to go through.
So we take Users and put that up in the Report Filter, Flow, either IN or OUT, is in Column Labels, all right, good enough, there.
And then what we end up doing is taking each individual date and putting it down in the Values field.
One at a time, let's see, I wonder if I go to check them here and have them go to the right place, yep.
And now we want those dates to go down the side, so we take this sum Values field and move it over here to Row Labels.
And now the final step, we go to just one User and we get to see their IN and OUT totals for each.
Grand Total, probably don't want that here, so we got a PivotTable Options and turn off Grand Total for the rows, click OK and we're close.
But you know, I have a feeling, this is probably a sample data set and there's probably many, many more dates and it would get real tedious to do all that going down.
Let me try another method here, that might end up being a lot worse than what we've done so far.
I'm going to create a new column here called Key, that Key is going to be the User’s name, ampersand and then a comma, and then another ampersand, and the direction IN or OUT (=A2&”,”&B2).
Copy that down to convert those for most of values and I'm going to create a special PivotTable from this data over here.
It's the multiple consolidation range, so I have to use Alt, D, P to get to the old PivotTable Wizard, Multiple consolidation ranges, click Next, I'll create the page fields, click Next, my range is the data over here, click Add.
And now just click Finish, and we get the identical data set with Totals at the bottom.
And the right hand side, you say well, what good is that?
Check this out, we're going to zoom in on the Grand Total.
Grand Total, and now we get one brand new data set with all of the dates going down.
Let's see if this is any better.
I'm going to insert a new column here and try and use Data, Text to Columns.
So Data, Text to Columns, delimited by a comma, click Finish.
So now what we have here is User and then Flow, Date and Count.
Let's try and build a PivotTable from this.
Insert, PivotTable, click OK.
So we take our User up to the Report Filter, Dates going down the side, Flow going across the top and Count in the heart of the PivotTable.
Now, when we choose one User, we get to see their Totals.
I think, if you really have more than seven dates, if you really have 14 or 21, or 28, or 150 dates, this is probably worth the extra effort to take that original data, spin it in the correct format and then just simply drag the Date field down to Row Labels, rather than taking the individual dates one at a time down to the Value section.
There you have it, a couple of cool ways to go.
I want to thank Fabien for sending that question in, want to thank you for stopping by, I will see you next time for another netcast from MrExcel.
Ah, great data set sent in today by Fabien from France.
He wants to create a PivotTable from this, with Users as the filter field.
Flow going across the columns and then Dates going down the side.
And while it's possible to do this, I'm not really happy with the process that we have to go through.
So we take Users and put that up in the Report Filter, Flow, either IN or OUT, is in Column Labels, all right, good enough, there.
And then what we end up doing is taking each individual date and putting it down in the Values field.
One at a time, let's see, I wonder if I go to check them here and have them go to the right place, yep.
And now we want those dates to go down the side, so we take this sum Values field and move it over here to Row Labels.
And now the final step, we go to just one User and we get to see their IN and OUT totals for each.
Grand Total, probably don't want that here, so we got a PivotTable Options and turn off Grand Total for the rows, click OK and we're close.
But you know, I have a feeling, this is probably a sample data set and there's probably many, many more dates and it would get real tedious to do all that going down.
Let me try another method here, that might end up being a lot worse than what we've done so far.
I'm going to create a new column here called Key, that Key is going to be the User’s name, ampersand and then a comma, and then another ampersand, and the direction IN or OUT (=A2&”,”&B2).
Copy that down to convert those for most of values and I'm going to create a special PivotTable from this data over here.
It's the multiple consolidation range, so I have to use Alt, D, P to get to the old PivotTable Wizard, Multiple consolidation ranges, click Next, I'll create the page fields, click Next, my range is the data over here, click Add.
And now just click Finish, and we get the identical data set with Totals at the bottom.
And the right hand side, you say well, what good is that?
Check this out, we're going to zoom in on the Grand Total.
Grand Total, and now we get one brand new data set with all of the dates going down.
Let's see if this is any better.
I'm going to insert a new column here and try and use Data, Text to Columns.
So Data, Text to Columns, delimited by a comma, click Finish.
So now what we have here is User and then Flow, Date and Count.
Let's try and build a PivotTable from this.
Insert, PivotTable, click OK.
So we take our User up to the Report Filter, Dates going down the side, Flow going across the top and Count in the heart of the PivotTable.
Now, when we choose one User, we get to see their Totals.
I think, if you really have more than seven dates, if you really have 14 or 21, or 28, or 150 dates, this is probably worth the extra effort to take that original data, spin it in the correct format and then just simply drag the Date field down to Row Labels, rather than taking the individual dates one at a time down to the Value section.
There you have it, a couple of cool ways to go.
I want to thank Fabien for sending that question in, want to thank you for stopping by, I will see you next time for another netcast from MrExcel.
