Learn Excel - Pivot Group by Week Descending - Podcast 1939

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Nov 19, 2014.
After grouping a pivot table by week, you want to show the weeks with the most recent at the top. This does not work because the grouped weeks are text and September (9) sorts before December (12). Today, a workaround.
maxresdefault.jpg


Transcript of the video:
The MrExcel podcast is sponsored by “Easy-XL”!
Learn Excel from MrExcel podcast episode 1939 – Pivot Table Group by Week, Latest to Earliest!
Hey, welcome back to the MrExcel netcast, I’m Bill Jelen.
Boy, I hope that someone in the YouTube comments comes up with an easier way to do what we have to do today, because this is just ugly!
So a question came in to my YouTube channel, take a Pivot table by date, group this up, well let’s first copy that date there, and format it to see what day of the week it is, it is Tuesday, alright.
So we want weeks starting on Monday, so I have to start on January 31st.
We want to group this up to weekly data, so Group Field, choose just Days, uncheck Months, change this to 7 days, and then it’ll be 12/31/2012 because that’s the first Monday, alright, click OK.
Alright, good enough, it is appearing in earliest-to-latest sequence and everything is fine, the problem comes in when we decide that we want to sort that with the most recent at the top.
As soon as I click Z-A, it goes really bad because it’s text.
And so text-wise September 9 comes way before down here, this December because December starts with a 12, alright, it’s just so literal, so I’m going to undo that, Ctrl+Z.
Here’s my solution, which is really, really ugly, I admit!
I’m going to take those dates, I’m going to copy the dates, come out here to the right-hand side and paste, Ctrl+V, I’ll call this Original Text, and over here I’ll call it Sequence.
So here, =ROW(), double-click to copy that down, which gets us sequential numbers, Ctrl+C, convert to values, and now sort descending to get these into the right sequence.
Alright, once I have that, I’m going to select all of that text, File, Options, Advanced, scroll almost all of the way down, choose Edit Custom Lists, this is where they store Sunday, Monday, Tuesday, and so on.
And I selected that range, D4:D107, I’ll Import, we now have a Custom List that shows Excel what sequence we want that data in!
Alright, now you would think, usually custom lists will automatically sort the Pivot tables, but because there must be some bug where they’re not sorting after they group this data.
So, you have to come here, More Sort Options, you want to say- we’re actually going to say Ascending because the list is already in the right sequence.
But then you have to go like More Options, you HAVE to uncheck that box, Sort automatically, and then the First key sort order is going to be the custom list that we created.
Now the nightmare here is if you’re getting new data every single week, if this is a once-a-year kind of thing, not bad.
If you’re getting data every week, then you have to constantly keep up updating that custom list, you can imagine how frustrating that would be.
But there, we now have the data with the most recent week at the top, and going backwards.
Boy, I wish there was some easier way, and there probably is some easier way, let me know in the YouTube comments.
Alright well hey, I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel!
 

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

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