Excel query

[Alex H]

I have a large spreadsheet covering data from six years. Is there a quick way to show the data from each month? By that I mean all the data from January of each year (not just Jan 2007, or Jan 2008). My dates are formatted as dates, in order of newest to oldest in Column A.

Long winded way would be to separate date into year, month, day columns and stick a filter on but I'm lazy and don't want to have to bastardise the sheet each time I import the latest csv rows.

Anyone got any ideas?

 

[Al_G]

Why are you not using access?

 

[George Welsh]

Pivot tables but again you will need text to columns to seperate the month/year. As said before access is your friend..

 

[Alex H]

I would if I could, but stuck with Excel for now.

 

[ThomasBrentnall]

I would if I could, but stuck with Excel for now.

Why? Just import the data.

Will make your life loads easier!

 

[roberto.]

Stick a filter on and only select January?

 

[Alex H]

Need to keep it in excel for a client. They don't use access and aren't very accepting of change. It wasn't long ago they switched from paper records!

 

[ThomasBrentnall]

Need to keep it in excel for a client. They don't use access and aren't very accepting of change. It wasn't long ago they switched from paper records!

Oooh! I feel for you mate!

 

[ndrinkwater]

Can use pivot tables, but don't need text columns - should be able to just group the data and select the month

 

[The Boosh!]

The quickest way would be to add an extra column somewhere, and simply type in the below formula, then filter on that column. A1 should reference what ever cell your date is in.

Failing that, you could create a 'front' sheet, where you select a month from a drop down list, then a series of lookups would fill in the data for you based on that particular month.

=TEXT(A1,"mmm-yy")

 

[Deeg]

The quickest way would be to add an extra column somewhere, and simply type in the below formula, then filter on that column. A1 should reference what ever cell your date is in.

Failing that, you could create a 'front' sheet, where you select a month from a drop down list, then a series of lookups would fill in the data for you based on that particular month.

=TEXT(A1,"mmm-yy")

This.

But if it's in date format, you should able to Pivot and summarise it in pivot tables at a month level.

 

[Chrisgti6]

This.

But if it's in date format, you should able to Pivot and summarise it in pivot tables at a month level.

problem with pivoting it is that it limits the columns to 256, so if the data is daily 6 years is too many. Not seen the data though, so it's possible to just pivot if few enough dates are involved. You can then use the combine option in the pivot to combine at year level.

probably just quickest to text to columns and separate the year out, about a 30 second job!