Numbers rather than by dates and we certainly can do that we have a few basic choices for how we can make the grouping work so let's take a look and see what we can do.
To get started i've created an excel workbook with my fairly familiar list of movies and i've already written some basic code to create a simple pivot table and assign some fields to it so we.
Can see that in the visual basic editor there's the simple code i'm not going to go into detail about how this part works simply because we've already covered that in a previous.
Playlist so if you need a quick reminder of how to create basic pivot tables this is your reference point i'll drop a link in the video description so you can just download.
This basic code and data set all i'm going to do here is run the subroutine to create a new worksheet called pivot with this simple pivot table assigning the runtime field to the row groups the.
One separate row for each unique value in that column to do that i've got to reference one single cell in this column and i always think it makes sense to start with the.
First cell so i'm going to get a reference to cell a5 first let's head back to the visual basic editor and i'm just going to create a new subroutine at the top of the module.
To do this we'll keep the create movie pivot table subroutine as it is and create a new sub called group by numbers if i know that i'm already on the correct worksheet i can simply refer to.
The cell i'm interested in so let's say range a5 and then look for and apply the group method now there are a few optional parameters.
Of the group method if i type in a space immediately after that you can see their names but they're all optional they're all listed in square brackets so i'm just going to see what happens when we.
Omit all of the optional parameters
Let's just run that subroutine head back to the excel worksheet and find the excel decided to group the numbers in that range in sets of 10 so.It's starting with the lowest possible value it found in that column 68 and then grouped them in units of 10. now there are various things we can do using these optional parameters to.
Change the way this grouping works first of all let's try to change the size of the buckets that the values are grouped in so rather than grouping by 10 let's say we want to group by 50..
To do that we can head back to the visual basic editor and then at the end of the group method type in a space and i want to reference the buy parameter so i'm going to type in by colon equals.
And then i'm going to specify that i want to group by 50 rather than by 10 and if we simply run the subroutine again head back to the excel worksheet we'll.
Find that we've achieved what we wanted we can also change which value excel uses as the starting point for the grouping by default it uses the minimum value it finds in the runtime column 68..
I want to change that to start at 51. so to do that we can head back to the visual basic editor head to the end of the group line type in a comma and this time we'll reference the start parameter.
So start colon equals and then the value 51. we could also optionally provide an end value so if we didn't want to carry on grouping until the maximum value of that.
Field we could add an end value there as well but i'm going to leave that one off for the time being so having done that we can run the subroutine again and if we switch back to the excel worksheet.
We'll find that our grouping now begins
At 51 and it all looks a little bit more pleasing next i'd like to look at a slightly better way to get a reference to the.First cell in the row label section at the moment we're relying on the fact that this is always going to be cell a5 but that might not necessarily be the case.
So let's head back to the visual basic editor i'm going to start by declaring a couple of extra variables just to help so i'm going to start with dim pt as pivot table.
And then dim pf as pivot field and then going to set pt to be equal to worksheets the name of the worksheet is called pivot.
And then i want to refer to the first pivot table on there i've also given the pivot table a name by the way so it's called movie pivot so i could reference this pivot table in a couple of.
Different ways i could either say pivot tables one or i could use its name movie pivot so either will work happily then i want to use the pf variable to.
Get a reference to the pivot field whose name is run time so let's say set pf equals pt dot pivot fields and then refer to the runtime.
Field by name okay now that we've done that i can get a reference to that particular pivot fields label range and i want to capture a reference to.
That in another variable so let's head up to the top again and say dim r as range and then i can say set r equals.
P f dot label range now that particular property references the cell containing the word row labels so in this case it's cell a4 i want to reference the cell immediately.
Below that so i'm going to add a dot offset property to the end of the label range one row down and zero columns to the right then what i can do is change the range.
A5 to say r so r dot group and just to demonstrate that it is still working let's change our numbers let's let's group by 100 and we'll start at a value of 1 rather.
Than 51. so having done all that we can run the subroutine again and we'll find that we've changed the grouping using a completely different way to reference.
The first cell in the row label section so this technique is a little more flexible because it doesn't now matter where we create our pivot table on the new worksheet.
If we head back to the visual basic editor and maybe make a modification to the create movie pivot table subroutine let's set our table destination to sell what should we go for.
C5 why not cell c5 will do so if we run this subroutine again to create a new movie pivot table it removes the existing worksheet first.
So our row labels this time begins in cell c7 if we head back to the visual basic editor and we run our group by number subroutine again we'll still see that our table is.
Grouped correctly because we're referencing the row label cell relatively rather than by absolute cell reference one thing that our current code doesn't.
Handle very well is if we decide to change which field we use for the row groups so again if we head back to the visual basic editor and we change the grouping.
Field the row field from runtime let's change this one to the budget field instead if we run this subroutine now it will create a new pivot table showing the.
Values of the budget field but if we then headed back and tried to run our group by number subroutine because this time it's trying to refer to the runtime field it will fail it.
Can't get the label range of the runtime field if it's not displayed in the pivot table so in order to fix this let's just hit the end button here.
I'm going to change the way we refer to the range r so we're not going to bother with this pivot field variable in fact let's comment out the set pf and the dim pf lines.
I'm also just going to comment out the set r line there i'll keep that in there just for reference and instead i'm going to say set r equals pt dot row range.
So the row range refers to the block of cells which makes up the row labels area including the top cell there cell c6 in this case the one with row labels in it i want to refer to the cell that is in.
The first column and the second row of the row labels uh group or the uh the row range so rather than just saying row range i'm going to say row range dot cells.
And then in some round brackets go for row number two and column number one so having done that we haven't fixed this to any particular field any longer.
I think i should probably update the way the grouping works perhaps rather than going in groups of 100 maybe something like 100 million might be more appropriate and again.
We'll start at a value of one so if we run this everything again and have a look back at the excel worksheet there we go we've got our data now grouped by.