+ Reply to Thread
Results 1 to 5 of 5

Macro for converting aggregate to case level data

  1. #1
    Registered User
    Join Date
    10-25-2010
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003
    Posts
    12

    Macro for converting aggregate to case level data

    I'm struggling to convert my aggregate data into case level data. I want to create a macro that inserts duplicate rows based on the number of cases listed in the case count column (column E).
    i.e. if I have an entry that says on October 25, 2010 (date variable, column A) there were 15 cases (case count, column E), then I would like to insert 14 additional rows that are duplicate entries for the reference date. Eventually I will delete the case count column and insert a case ID value (i.e. 1 to 15) for each of the cases on each particular date.

    I'm completely stumped on how to do this and would really appreciate your input!

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,255

    Re: Macro for converting aggregate to case level data

    Hello epi,

    Welcome to the Forum!

    The IDs can be added automatically as the rows are expanded. Does the number 1 get assigned to the earliest date and 15 to the oldest?
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Registered User
    Join Date
    10-25-2010
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Macro for converting aggregate to case level data

    Hey Leith,
    thanks for the warm welcome!

    I'd prefer if the ID variable was reset for each date (I have data from 2006 to 2010), but if this is too complicated, I don't mind if it does this automatically based on oldest to newest dates.

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,255

    Re: Macro for converting aggregate to case level data

    Hello epi,

    Okay, I'll take this a step a time. It would be easier to follow and to write the code if I had a workbook. Can you post a copy of the workbook?

  5. #5
    Registered User
    Join Date
    10-25-2010
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Macro for converting aggregate to case level data

    Hi Leith,
    Sorry its been a while!
    I've attached my workbook as requested. I'd really appreciate your help!
    Thanks!
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1