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:
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!
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:
| Date | Month | Day | Hour | Value |
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!
