Sorting large data by date

**(T_T)** · 06-28-2022, 05:10 AM

Hi

I need to sort this excel based on the date. First, separate each year and put the data in a new sheet, and then separate the months and then days. You can see this in the example I uploaded. Can someone give me some ideas on how to do it?

Thanks

**carlmon** · 06-28-2022, 06:37 AM

Highlight Row #2 by clicking the 2 on the row headings -> data tab -> filter

Use the dropdowns however you see fit. Click the top left triangle in the header to select all and copy and paste to a new sheet.

**(T_T)** · 06-28-2022, 04:17 PM

Originally Posted by carlmon

Highlight Row #2 by clicking the 2 on the row headings -> data tab -> filter

Use the dropdowns however you see fit. Click the top left triangle in the header to select all and copy and paste to a new sheet.

Thanks!

but the data is already sorted in a sheet however it's too big (every hour of every day from 1980 to 2021) which makes it impossible to analyze or plot anything. That's why I need it to be in separate sheets.

**carlmon** · 06-29-2022, 07:32 AM

It sorts and filters. If you want to look at a specific year, only select that one year. By having 20+ sheets, you'll make your job even harder.

**carlmon** · 06-29-2022, 07:34 AM

I can do that if you still want a code to make a new sheet for every year. But excel's built-in-functionality is useful.

**Kaper** · 07-01-2022, 09:53 AM

As you are using Excel 2013 you shall be able to use Power Query.
And it's wise idea to use PQ, as you have large datasets to be analyzed.
(If you have not installed it yet, see for instance https://www.excelcampus.com/install-power-query/ )

And as a first step made a query which filters only data for given year (from a closed file).
See attached file. You can prepare your graphs, extra statistics etc for a selected year. save it, change a year and ... all calculations are ready for the other year.
The basic method would be to edit a query. A bit more advanced method is to make a query responding to change in the sheet. I did it writing a year in cell B1 and refering to this cell in a query.
To make it running just once you install PQ and open the file I made also location of file with data passed to PQ from the sheet (cell D1).

To force the query to refresh after change in B1 or D1 I used an event handler in code of Sheet1 sheet. But it's extra feature. If you don't want to use macros - just save the file as xlsx (no macros) and you can manualy refresh the output table with right-click on it and selecting Refresh.

The only modification to source data is that I used a single row header (all headers in row 1, no merging of cells). See screenshot.
So after opening the file and allowing the macros (or not) change the folder and filename of the sorce data in D1 (and if macros are disabled manualy refresh the query).