Results 1 to 10 of 15

Thread: Split value into new rows, based on other Columns

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Seeker Sagittariu5's Avatar
    Join Date
    Sep 2020
    Posts
    8
    Articles
    0
    Excel Version
    2016

    Question Split value into new rows, based on other Columns

    Hi,
    How can I split the Monthly Rental Fee (usually $20) into new rows of it's respective Month based on Terminal ID and Cab No.

    For example R1099 paid $60 in March, but this is the cumulative Rental Fee for Jan, Feb and March




    More Data



    Appreciate your help!!


  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,408
    Articles
    45
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Sagittariu5,

    Welcome to the board. So listen, I notice that all of these images are hosted in MrExcel.com. While I don't have an issue with posting something on multiple boards, we do request that you provide a link to that post so that people don't end up spending their time answering posts that have already been dealt with.

    Thanks,
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,408
    Articles
    45
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Thanks for the link. Can you clarify... which of the 3 tables is the data input? I.e. is it the first, or the second? And which is the output you're trying to achieve?

    I'm trying to work out the logic for R1099 and I'm not following the flow...
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Seeker Sagittariu5's Avatar
    Join Date
    Sep 2020
    Posts
    8
    Articles
    0
    Excel Version
    2016
    Hello Ken,

    The first table is data input and the second is the output Iím trying to achieve.

    The third table is just more of the original input table.

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,408
    Articles
    45
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Okay, so what I did was:
    • Created a custom function to extract the number of months end periods based on the difference between Inv StartDate and Month Year
    • Used that to pro-rate the Rental Fee into a new column called Rental Fee - Allocated.


    I've attached a pbix file with the queries in place.
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Tags for this Thread

Posting Permissions

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