Page 1 of 2 1 2 LastLast
Results 1 to 10 of 15

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

  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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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,404
    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,404
    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,404
    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.

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

    Thank you for your prompt response.
    Please find attached the updated pbix file, with the data table in question.

    I donít think I explained myself properly.
    The Monthly Rental Fee should be based on the last Date the rental fee was paid or if none then 12/1/19.

    For example R1099 case it would have been 12/1/19 and R4125 it would have been 3/6/20.
    Also, the rental fee is not always paid for all the missed months. for example RA445 Rental Fee paid in 7/21/20 would be for Apr Ė Jul.
    Attached Files Attached Files

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

    Okay, so I'm going to need to get a much clearer picture of the rules here, as I'm not following this.

    Getting the data cleaned up is possible, providing we have a clear idea of what needs to be done, but keep in mind that I don't know your data at all. Things like "Date was paid" doesn't help me when looking at your data. I assume that means the "Date" column, but I need to you be sure on that.

    When I limit the data to the cases you suggested, I do not have a clear picture of the 'after' needed. There are about 6 ways I could build the logic to generate the first output screen you provided, but they are all different. I'm trying to take the records from R4125, and can see what the input it, but I don't know what they output should look like and why it should look that way compared to the output of R1099.

    Adding RA445, things get very confusing for me. You say that this should go from Apr - July, but there is nothing in this data set that has a date of April - or March, or May - to base that off. I could assume that you are trying to allocate $80 in $20 increments going backwards (for Apr, May, Jun, Jul), but not all of your data is in increments of $20.

    I want to help you, but I don't have time to guess and build solutions that aren't going to work here.

    I've attached an Excel workbook that shows three data table extracts based on the ID's above. If you can give me two things, I'll try and help build you a solution. Those are:
    • An expected output table
    • An explanation of WHY the table looks that way (what is the logic for the distribution) for each table


    Thanks,
    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.

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


    Thank you for getting back to me.
    I've attached the Rules spreadsheet with the expected outcome.

    Also, some background knowledge, the Rental fee are usually $20 and in an ideal world all Cab's would pay the $20 fee for the Terminals they are using on a monthly basis.
    However, in reality the rental fee is being paid/collected unevenly for some Cabís.

    Hence, am trying to allocate the payments that are divisible by 20 (e.g. 40, 60, 80) to the respective prior months.

    Thanks in advance and please let me know if you need further clarification.
    Attached Files Attached Files

  10. #10
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,404
    Articles
    45
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Just dropping a note to let you know that I haven't forgotten about this, I'm just super busy. I probably won't have a chance to get to this today, but will attempt to circle back on it tomorrow.
    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.

Page 1 of 2 1 2 LastLast

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
  •