Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have several thousand rows of date, time, and values in columns.

Each row is contains a date, time for that date, and a value for that time period (hour)

So each 24 rows has the same date with each having the next hour of the day.

I'd like to collapse or group the 24 rows leaving the last row with a subtotal of the value (column D) to the right in column F.

While this can obviously be done manually, several years of data would take a while and there ought to be a way to do this other wise?

share|improve this question
add comment

2 Answers

The fastest way to accomplish this is to convert your data into an Excel Table. Then, create a Pivot Table from your Data Table. Choose Dates for your rows and Sum your Values in the totals section.

Two bonuses with this. First, the Pivot subtotals can update as you update your data table. Second, you can group the dates for subtotals by week, month, quarter, year, etc...

EDIT: Here's step-by-step directions (for Excel 2010, but they're very similar for Excel 2007).

  1. Convert your data into an Excel Table. Highlight a cell in your data and then Insert > Table.

  2. Create a Pivot Table from your data table. Highlight a cell in your new Data Table and then Insert > Pivot Table.

  3. Set-up your Pivot Table for your analysis. Highlight a cell in your Pivot Table and then choose the following:

    • Row Labels = Date

    • Values = Values

  4. Your Values are now subtotaled by Date. From here, feel free to play around with it. You can add more criteria in your rows or columns for a more robust report.

share|improve this answer
    
While I'm not very knowledgable with Pivot tables I'll give it a go. –  Will Oct 7 '12 at 20:33
    
I can't get to it now, but i'll add some detailed directions in the morning. –  dav Oct 8 '12 at 0:27
add comment

Try using Data \ Subtotal after sorting your data on the date. This will automatically insert "Total" rows beneath every change of date value and put each set of rows plus their Total in a collapsible group.

Collapsible group brackets show up on the left side of the spreadsheet with a little - symbol you can click to collapse a particular group. You can collapse all the groups to hide everything but the "Total" rows by clicking the number (usually "2") above the set of groups you want to collapse. If you want to copy just the total rows to a new sheet, first collapse all groups so you see only "Total" rows. Select all "Total" rows. Press Alt+; to deselect hidden rows, then copy. With only the total rows selected you can also add any subtotal function you want in the appropriate columns.

For a longer explanation with pictures, see this article.

share|improve this answer
    
Thank you for the suggestion. I'll do a test with some data in a separate sheet and see what becomes of it. –  Will Oct 7 '12 at 20:32
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.