+ Reply to Thread
Results 1 to 7 of 7

Need to Transform Data Derived from Row Values into Multiple Rows for Billing Schedule

  1. #1
    Registered User
    Join Date
    07-22-2012
    Location
    Austin, TX USA
    MS-Off Ver
    Excel 2007
    Posts
    7

    Need to Transform Data Derived from Row Values into Multiple Rows for Billing Schedule

    So here's my problem.

    I have a spreadsheet with rows of data that have a start and end date and monthly amounts to charge for the months in that range.

    So my data looks like this:
    Customer Contract# Bill Start Date Bill End Date # of Payments Monthly Amt to Bill
    PepsiCo 10001 7/1/2012 6/1/2012 12 $1,000

    I'm trying to get to this:
    Customer Contract# Bill Start Date Bill End Date Monthly Amt to Bill
    PepsiCo 10001 7/1/2012 7/31/2012 $1,000
    PepsiCo 10001 8/1/2012 8/31/2012 $1,000
    PepsiCo 10001 9/1/2012 9/30/2012 $1,000
    PepsiCo 10001 10/1/2012 10/31/2012 $1,000

    I know this can be accomplished in VBA w/ loops and manipulating ranges but I need some direction.

    Can anybody help?

    Thanks!

    -Gogie

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Need to Transform Data Derived from Row Values into Multiple Rows for Billing Schedule

    Hi

    1) Do you have multiple lines of data, so the extra entries will need to be inserted between existing entries. Or can the new data be put onto a new sheet?
    2) Should the end date for the example line be 6/1/2013?

    rylo

  3. #3
    Registered User
    Join Date
    07-22-2012
    Location
    Austin, TX USA
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Need to Transform Data Derived from Row Values into Multiple Rows for Billing Schedule

    Thanks for responding rylo.

    Yes. There are multiple lines of data. For example,

    If there's 2 lines of data with one line having 9 # of payments and the second line having 5 # of payments, then the code would need to generate 9 lines and then 5 lines beneath that.

    And sorry, my bad. The end date should be 6/1/2013.

    Thanks!

    -Gogie

  4. #4
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Need to Transform Data Derived from Row Values into Multiple Rows for Billing Schedule

    Hi

    This assumes that you have your data on sheet1. It will insert a new sheet, and output to that sheet.

    Please Login or Register  to view this content.
    rylo

  5. #5
    Registered User
    Join Date
    07-22-2012
    Location
    Austin, TX USA
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Need to Transform Data Derived from Row Values into Multiple Rows for Billing Schedule

    Exactly what I wanted. You rock man!

    Thank you!!!!

    -Gogie

  6. #6
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Need to Transform Data Derived from Row Values into Multiple Rows for Billing Schedule

    @ Phoenix1975

    Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
    Thanks.

    Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

  7. #7
    Registered User
    Join Date
    07-22-2012
    Location
    Austin, TX USA
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Need to Transform Data Derived from Row Values into Multiple Rows for Billing Schedule

    Thanks Cutter. I'll keep that in mind for the future.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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