Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Hampton, New Hampshire, USA
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help with a repetitive task in Excel (MS Excel 200

    Every month I get an excel file filled with raw data that I then have to format for distribution to my managers. The data is an aged receivables listing of our customers, each of which will have anywhere from one to many entries (see file attached, sheet labeled "Original Raw Data"). My task is to sort through the data and reduce each customer's entry to a single line (see file attached, sheet labeled "Formatted Data"). I have tried, unsuccessfully, to do this with a macro. I believe that it is due to the variable nature of the number of entries that each customer can have. Of course it could be because I don't understand macros all that well. Based on the many postings I have viewed I think that this might better be done with VB, alas this is another area that I am untutored in.

    Can any of you kind Excel wizards <img src=/S/starstruck.gif border=0 alt=starstruck width=15 height=15> suggest a solution to my dilemma <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45> or should I just resign myself to always having to trudge through this? <img src=/S/igiveup.gif border=0 alt=igiveup width=31 height=23>
    <font face="Comic Sans MS">MickeyMouse</font face=comic>
    How best to describe the Lounge-
    "Coming together is a beginning, staying together is progress, working together is success"

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Help with a repetitive task in Excel (MS Excel 200

    Pivot table can get the data in that form

    select a point in the data set
    Data-pivot table report
    <Next>
    Drag "Code" into row
    Drag "P" under the code in row

    Drag the various fields to sum into the data area
    <Finish>

    You can format it and also add a calculated field for the balance:
    (right click) - formulas - calculated field
    use the formula:
    ='Up to 30' +'31-60' +'61-90' +'Over 90'

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Hampton, New Hampshire, USA
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with a repetitive task in Excel (MS Excel

    Hi Steve,

    Sorry for the late response, I have been out of commission due a knee injury. I am trying your suggestion and will contact you if I run into any problems with trying your suggestion of using Pivot Tables. I haven't used them before so it will probably take me some time to get it to work.

    Thanks again for the help.
    <font face="Comic Sans MS">MickeyMouse</font face=comic>
    How best to describe the Lounge-
    "Coming together is a beginning, staying together is progress, working together is success"

Posting Permissions

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