Split value into new rows, based on other Columns

[Sagittariu5]

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!!

[Ken Puls]

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,

[Sagittariu5]

Thank you Ken

https://www.mrexcel.com/board/thread...lumns.1146400/

[Ken Puls]

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

[Sagittariu5]

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.

[Ken Puls]

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.

[Sagittariu5]

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.

[Ken Puls]

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,

[Sagittariu5]

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.

[Ken Puls]

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.