Excel Macro Help

Chubbly

I have this problem that is probably pretty easy to do but damned if I know how to do it.

I have a CSV sheet that was a 3 columns: Date, Time and Number.

It looks as follows

Date........Time........kWh
1-Jan-04....00:30........68
1-Jan-04....01:00........50
1-Jan-04....01:30........78
etc

(dots are there to separate, not actually there)

The Date column ranges over many years, can be 1 could be 6.
Time column increases in half-hour intervals.
kWh column is arbitrary.

What I want to do is grab a whole month and then sort the kWh column from highest to lowest for that month AND THEN do some calculation for that month (average, highest value, lowest value, etc). And I want to do this for each month in each year. This thing has nearly 100,000 rows so you can see why I want this.

Any tips?

Deebaser

Chubbly wrote: »

I have this problem that is probably pretty easy to do but damned if I know how to do it.

I have a CSV sheet that was a 3 columns: Date, Time and Number.

It looks as follows

Date........Time........kWh
1-Jan-04....00:30........68
1-Jan-04....01:00........50
1-Jan-04....01:30........78
etc

(dots are there to separate, not actually there)

The Date column ranges over many years, can be 1 could be 6.
Time column increases in half-hour intervals.
kWh column is arbitrary.

What I want to do is grab a whole month and then sort the kWh column from highest to lowest for that month AND THEN do some calculation for that month (average, highest value, lowest value, etc). And I want to do this for each month in each year. This thing has nearly 100,000 rows so you can see why I want this.

Any tips?

easiest way i can think of using excel?

-create an additional two columns labeled 'month' and 'year' and use the MONTH and YEAR functions on the date.

-put a filter on and copy/paste each year onto a seperate tab

-sort by month (ascending) and kwh(either)

-throw in SUBTOTALS with either AVG or SUM by month


This will make it easy as hell to pull lowest for each month, highest for each month and either month total or month average. I'm not sure if you can do double subtotals, but worst case scenario if you can't is manually add an AVG function next to the sub total total.

mully

filtering is your best friend, and if your excel spreadsheet is organized (headers and content where it should be), all you need to do is hit the filter button - it's automatic. from there, you can do whatever you need to do. custom filter will help you do like the previous sir said and sort in column orders as you desire.

that being said, i wouldn't necessary make MONTH and YEAR columns necessarily - if your dates are like 15/03/02, it will sort it perfectly in order by month and then by year. after you filter something, it will give you check boxes to show only those items (and hide everything else without deleting it) so if you just wanted to see march items, you'd just make sure march was checked off.

Jeice

Look up how to use Pivot Tables.

Pivot Tables will summarize all you're asking for in a nice table form. And, using the pivot table wizard, it's pretty simple.

Also, it makes things easier if you do add in 2 extra columns, a month and year column. you can use the excel functions =month() and =year(). Now, =month by default will return a number from 1 to 12, so change the format to a custom format of 'mmm' to make it show Jan instead of 1.

Next you go to Data -> Pivot Table and Pivot Chart Report
Under where is the data, select microsoft excel list
under what kind of report, select pivot table
hit next
highlight the data you want to use
On step 3 of 3, go to layout

this is where you can mess around on exactly how you want it, but based on what you asked you can go:

Grab the month, year, and kWh and drag it to the row section
put kWh in the data section
you can put kwH in the data field more than once and if you double click on that field, you can change the function it will do. By default it will use sum, but you can change sum to average, max, min etc.