ClioSport.net

Register a free account today to become a member!
Once signed in, you'll be able to participate on this site by adding your own topics and posts, as well as connect with other members through your own private inbox!

Excel query



Alex H

ClioSport Club Member
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?
 
  172 rag
Pivot tables but again you will need text to columns to seperate the month/year. As said before access is your friend..
 

Alex H

ClioSport Club Member
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!
 
  197, R26 F1, Cat'm 7
Can use pivot tables, but don't need text columns - should be able to just group the data and select the month
 

The Boosh!

ClioSport Admin
  Clio 4 R.S.
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.

Code:
=TEXT(A1,"mmm-yy")
 

Deeg

ClioSport Club Member
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.

Code:
=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.
 
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!
 


Top