Single Rows to Multiple Rows - Complex

J

Jess709

New Member
Joined
Apr 24, 2015
Messages
20
I am trying to provide payment information for a client on a per day basis in Excel. My current payment report has a line for each payment and that payment has a "pay from date" and a "pay through date". For example a payment dated today may have a payment from date of 11/2/17 and a pay through date of 11/8/17.

My client is requesting to have a line for each day of that particular payment for that specific employee. So in the example above instead of one line with a payment of $700 for 11/2/17-11/8/17 we would want 7 lines and each line would be for 1 day of the pay from and through period.

I also need to split out the payment amount among the number of days. In my example on each of the 7 lines I would have a payment amount of $100.

Below is an example of what my original date would look like and what I need the final output to look like. I'm trying to find a way to automate the final report in anyway possible.

Thank you so much for any assistance this great group can provide!!!

[TABLE="width: 815"]
<tbody>[TR]
[TD="colspan: 8"]ORIGINAL DATA[/TD]
[/TR]
[TR]
[TD]Claim Number[/TD]
[TD]EE ID[/TD]
[TD]EE First Name[/TD]
[TD]EE Last Name[/TD]
[TD]Payment Date[/TD]
[TD]Pay From Date[/TD]
[TD]Pay Through Date[/TD]
[TD]Payment Amount[/TD]
[/TR]
[TR]
[TD="align: right"]12345611[/TD]
[TD="align: right"]897045[/TD]
[TD]Susie[/TD]
[TD]Smith[/TD]
[TD="align: right"]11/9/2017[/TD]
[TD="align: right"]11/2/2017[/TD]
[TD="align: right"]11/8/2017[/TD]
[TD="align: right"]$700 [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 8"]End Result of Customer Request - Trying to Automate[/TD]
[/TR]
[TR]
[TD]Claim Number[/TD]
[TD]EE ID[/TD]
[TD]EE First Name[/TD]
[TD]EE Last Name[/TD]
[TD]Payment Date[/TD]
[TD]Pay From Day[/TD]
[TD]Pay Through Date[/TD]
[TD]Payment Amount[/TD]
[/TR]
[TR]
[TD="align: right"]12345611[/TD]
[TD="align: right"]897045[/TD]
[TD]Susie[/TD]
[TD]Smith[/TD]
[TD="align: right"]11/9/2017[/TD]
[TD="align: right"]11/2/2017[/TD]
[TD="align: right"]11/2/2017[/TD]
[TD="align: right"]$100 [/TD]
[/TR]
[TR]
[TD="align: right"]12345611[/TD]
[TD="align: right"]897045[/TD]
[TD]Susie[/TD]
[TD]Smith[/TD]
[TD="align: right"]11/9/2017[/TD]
[TD="align: right"]11/3/2017[/TD]
[TD="align: right"]11/3/2017[/TD]
[TD="align: right"]$100 [/TD]
[/TR]
[TR]
[TD="align: right"]12345611[/TD]
[TD="align: right"]897045[/TD]
[TD]Susie[/TD]
[TD]Smith[/TD]
[TD="align: right"]11/9/2017[/TD]
[TD="align: right"]11/4/2017[/TD]
[TD="align: right"]11/4/2017[/TD]
[TD="align: right"]$100 [/TD]
[/TR]
[TR]
[TD="align: right"]12345611[/TD]
[TD="align: right"]897045[/TD]
[TD]Susie[/TD]
[TD]Smith[/TD]
[TD="align: right"]11/9/2017[/TD]
[TD="align: right"]11/5/2017[/TD]
[TD="align: right"]11/5/2017[/TD]
[TD="align: right"]$100 [/TD]
[/TR]
[TR]
[TD="align: right"]12345611[/TD]
[TD="align: right"]897045[/TD]
[TD]Susie[/TD]
[TD]Smith[/TD]
[TD="align: right"]11/9/2017[/TD]
[TD="align: right"]11/6/2017[/TD]
[TD="align: right"]11/6/2017[/TD]
[TD="align: right"]$100 [/TD]
[/TR]
[TR]
[TD="align: right"]12345611[/TD]
[TD="align: right"]897045[/TD]
[TD]Susie[/TD]
[TD]Smith[/TD]
[TD="align: right"]11/9/2017[/TD]
[TD="align: right"]11/7/2017[/TD]
[TD="align: right"]11/7/2017[/TD]
[TD="align: right"]$100 [/TD]
[/TR]
[TR]
[TD="align: right"]12345611[/TD]
[TD="align: right"]897045[/TD]
[TD]Susie[/TD]
[TD]Smith[/TD]
[TD="align: right"]11/9/2017[/TD]
[TD="align: right"]11/8/2017[/TD]
[TD="align: right"]11/8/2017[/TD]
[TD="align: right"]$100 [/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col><col></colgroup>[/TABLE]
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
Hi
Try
Code: 
Sub Splitrows()

    Dim Cnt As Long
    Dim Rw As Long
    
    For Rw = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
        Cnt = Range("G" & Rw).Value - Range("F" & Rw).Value + 1
        Rows(Rw + 1).Resize(Cnt - 1).Insert
        Rows(Rw).Resize(Cnt).FillDown
        Range("H" & Rw).Resize(Cnt).Value = Range("H" & Rw).Value / Cnt
        Range("F" & Rw).AutoFill Range("F" & Rw).Resize(Cnt), xlFillSeries
        Range("G" & Rw).Resize(Cnt).Value = Range("F" & Rw).Resize(Cnt).Value
    Next Rw

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question Question
Formula to get recent payment date
Replies
4
Views
120
InvoicingNoob
I
I
  • Solved
Formula to lookup a date of payment
Replies
4
Views
138
InvoicingNoob
I
I
  • Question Question
Accrual Worksheet Next Pay date Formula
Replies
0
Views
287
ItalianPlatinum
I
Kellie220
  • Question Question
VBA to Collapse Rows (3 part question)
Replies
1
Views
283
Artik
Artik
M
  • Question Question
HELP IDENTIFY ORIGINAL START DATE BASED ON CONTINOUS SERVICE RULES
Replies
0
Views
569
mrshuck
M

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top