Sort/Group Data by Year, then by Month

cloudypen

New Member
Joined
Jan 18, 2023
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hello! I am very new to this forum and I couldn't get the XL2BB extension to work so excuse the formatting since I just copied and pasted the data from my excel file.

I have data that is recorded every hour every day from 1999-11-01 to 2023. I only pasted a small portion of the cells as it has almost a million rows:

DateMonthDayHourValue
1999-11-01 1:00​
11​
1​
1​
3.388​
1999-11-01 4:00​
11​
1​
4​
2.47​
1999-11-01 6:00​
11​
1​
6​
2.449​
1999-11-01 7:00​
11​
1​
7​
2.501​
1999-11-01 8:00​
11​
1​
8​
2.549​
1999-11-01 9:00​
11​
1​
9​
2.611​
1999-11-01 10:00​
11​
1​
10​
2.673​
1999-11-01 11:00​
11​
1​
11​
2.727​
1999-11-01 12:00​
11​
1​
12​
2.773​
1999-11-01 13:00​
11​
1​
13​
2.802​
1999-11-01 14:00​
11​
1​
14​
2.813​
1999-11-01 15:00​
11​
1​
15​
2.808​
1999-11-01 16:00​
11​
1​
16​
2.795​
1999-11-01 17:00​
11​
1​
17​
2.777​
1999-11-01 18:00​
11​
1​
18​
2.753​
1999-11-01 19:00​
11​
1​
19​
2.73​
1999-11-01 20:00​
11​
1​
20​
2.717​
1999-11-01 21:00​
11​
1​
21​
2.72​
1999-11-01 22:00​
11​
1​
22​
2.754​
1999-11-01 23:00​
11​
1​
23​
2.824​
1999-11-02 2:00​
11​
2​
2​
2.928​
1999-11-02 3:00​
11​
2​
3​
3.285​
1999-11-02 6:00​
11​
2​
6​
3.383​
1999-11-02 7:00​
11​
2​
7​
3.527​
1999-11-02 8:00​
11​
2​
8​
3.53​
1999-11-02 9:00​
11​
2​
9​
3.513​
1999-11-02 11:00​
11​
2​
11​
3.484​
1999-11-02 12:00​
11​
2​
12​
3.38​
1999-11-02 13:00​
11​
2​
13​
3.319​
1999-11-02 14:00​
11​
2​
14​
3.25​
1999-11-02 15:00​
11​
2​
15​
3.174​
1999-11-02 16:00​
11​
2​
16​
3.108​
1999-11-02 17:00​
11​
2​
17​
3.049​

I would like to sort/group the values first by year, then by month in separate sheets. For example, the sheet "2005" would have the column "Jan" with "Value" next to it. Each row below the headers would be the date and hour and the hourly values. Then to the right of it (or below the last entry for Jan) would be "Feb" and "Value", so on and so forth, or something of a similar fashion.

A pivot table grouped it in a way I described but I don't want the sum of the values as I just want it to show each value as it is. I also tried XLookup but it didn't work the way I wanted it to. I want the data displayed that way as I need to make graphs of the hourly data for every month.

If anyone knows how to do this or can guide me in the right direction, it would be greatly appreciated! Thank you!
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Have you tried using >Data (tab) >Sort & Filter options?
 
Upvote 0
Have you tried using >Data (tab) >Sort & Filter options?
Yes but I was wondering if there is a better way of doing it? For example I filtered for 2000 so I'm left with all the rows for 2000, but I still have to manually copy the values for January into a Jan column, and the February values into the Feb column, so I have to do it 12 times for 24 years
 
Upvote 0

Forum statistics

Threads
1,223,885
Messages
6,175,184
Members
452,615
Latest member
bogeys2birdies

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