Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Sorting Help

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,645
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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.

  3. #3
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    ~Anne Troy

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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)

  5. #5
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •