Sorting Help

eekbat

New Member
Joined
Apr 6, 2002
Messages
2
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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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.
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top