Sorting Help

[eekbat]

I'm not sure how to do this

I want to treat all data from may 1999 as one pieces of data, all from june 1999 as another etc. Currently I have all that data listed by day and i'm loking for an elegant way to clump it together so that i can simply report data by month

hope this is clear?

thanks

[Aladin Akyurek]

On 2002-04-07 14:28, eekbat wrote:
I'm not sure how to do this

I want to treat all data from may 1999 as one pieces of data, all from june 1999 as another etc. Currently I have all that data listed by day and i'm loking for an elegant way to clump it together so that i can simply report data by month

hope this is clear?

thanks

It's not clear what you exactly want to do.

Do you want to total something or what? If so, you need to tell preferably by means of a small sample how your data look like & what kind of summary by month you want to have.

[Anne Troy]

As a quickie solution, you can insert another column. Suppose your dates are in Column B and you inserted Column A as the new column. In A1, you put a new heading called Month. In A2, you could type

=MONTH(B2)

Copy that down. Now, use Data-Subtotals. You'll want to *at each break in* Month, then do totals on whatever column you're trying to group by. Hit OK. You'll see a #1, #2, and #3 to the left of your column headings. Click on #2 to see the totals for each month.

[eekbat]

Bleh

Monday mornings, sorry for not providing more detail

Ok, i'll explain it a bit better

I have a huge list of data such as

17/09/1999 Smith
18/09/1999 Brown
20/09/1999 Willis
22/09/1999 Jones

Now this is far too much info to list for my reports. I simply want to have a field that adds up all the columns that have a data entry for for a month/year and give a single number ie

Sept 1999 4
October 1999 5

Dreamboat was on the right lines i think, but i need to make ths a pretty 'dumb' sheet, in so far as the person that will be using it needs to just be able to paste the data in to it and be able to see the results on a front page.

If I can get the data from day/month/year to just month/year i'm prety sure i can suss it out from there

Thanks for the help
(note im using day/month/year notation)

[Anne Troy]

Eek: I just recorded this macro in a matter of seconds. It takes your worksheet and copies it to a different name. Then it grabs the date column (in this instance, I used column A), and formats it to show only the month, then it sorts the column, runs subtotals on the amount column, and goes to the subtotals view (#2) as I explained previously. I've assigned it a shortcut key of Ctrl-Shift-G (for "go").

Hope it helps:

Sub EekbatMacro()
'
' EekbatMacro Macro
' Macro recorded 04/08/2002 by Dreamboat
'
' Keyboard Shortcut: Ctrl+Shift+G
'
Sheets("OldSheetName").Select
Sheets("OldSheetName").Copy Before:=Sheets(1)
Sheets("OldSheetName (2)").Select
Sheets("OldSheetName (2)").Name = "NewSheetName"
Columns("A:A").Select
Selection.NumberFormat = "mmmm"
Range("A1").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2
Columns("A:A").ColumnWidth = 19
End Sub




Oh yeah. Somebody can probably shorten the code. And I also widened column A so the month names could be completely seen.
_________________
TheWordExpert

[ This Message was edited by: Dreamboat on 2002-04-07 21:32 ]