How to turn this s/sheet around?

  • Thread starter Thread starter Brian Clarke
  • Start date Start date
B

Brian Clarke

I have a worksheet which is 76 columns by 500 rows. Col A has dates in
it, and the other columns have figures which I want to summarise by
month, possibly in a pivot table.

I don't want to have to drag all 75 column headings across when
designing the table - is there a quicker way?

Alternatively, I'm sure I saw a formula once which worked from the dates
and summarised a column of figures quickly by month, but I can't find it
now.

I would appreciate any help.
 
You are possibly thinking of subtotal.

1) Insert a new column next to your date
2) In the inserted column. enter a formula to pick up the month from your
date (for example, in B2, enter '=month(A2)', without the quote. Copy or
drag to fill the rest of your new column.
3) Highlight your entire data range
4) Click Data, Subtotals
5) In the popup form, set each change in {the name of your month column}.
Check every other column you want summed.
6) Click OK

If my explanation is not up to scratch:

http://office.microsoft.com/en-gb/excel/HA011097981033.aspx


If your data covers more than a year the Month() function will not
differentiate January 2007 from January 2008. To get around this, I use the
Text() function. = Text(A2, "YYYY-MM") will show January 2007 as 2007-01
and January 2008 as 2008-01, maintaining the result in order when sorted.
 
may be ??

use helper column - put this formula =Text(A1,"mmmm") and drag it
u will get month's for the specified dates in col A.
 
Many thanks for your help. I had never really got familiar with the
=TEXT function before. That has allowed me to crack the problem.
 
If you've created the pivottable using the dates, you can group by month (or
month and year) within the pivottable without changing your data.

Right click on the Date header in the pivottable.
Choose Group and show detail.
Then choose Group.
And group by months (or months and years).
 
Hi Dave

I read the post somewhat differently
I didn't read the grouping of dates as being the OP's problem.
I thought his concern was having to drag up to 75 fields to different
sections of the PT.
This can be done with a macro, setting all fields to data fields.
the Op could then just drag the date field to the Row area and do grouping
as per your instruction, and remove Date from the data area

Sub AddAllFieldsToDataArea()
Dim pt As PivotTable, pf As PivotField
Dim ws As Worksheet, i As Long
Application.ScreenUpdating = False
Set ws = ActiveSheet
For i = 1 To ws.PivotTables.Count
Set pt = ws.PivotTables(i)
pt.ManualUpdate = True
For Each pf In pt.PivotFields
With pf
.Orientation = xlDataField
.Function = xlSum
End With
Next
pt.ManualUpdate = False
Next i
Application.ScreenUpdating = True
End Sub

Copy the Code above
Alt+F11 to invoke the VB Editor
Insert>Module
Paste code into white pane that appears
Alt+F11 to return to Excel

To use
Select sheet containing the PT's
Alt+F8 to bring up Macros
Highlight the macro name
Run
 
I really wasn't addressing the problem of creating a pivottable. I just wanted
to make sure that the OP knew that dates could be grouped inside the pt.

I'm sure your macro makes it easier to build/modify that pt, though.
 
Back
Top