Macro Help - Amount Distributions and Dates

K

KarenMichael1

New Member
Joined
Jul 20, 2016
Messages
4
Hi! I am working on a spreadsheet and I could really use some assistance with developing a macro.

I need to take the period in column A, and match it to the corresponding column starting at column D.

For example,

Period 2016-06, corresponds to June-16.

I need this macro to do this indefinitely, with multiple lines going to the same period.

Alongside this, I also require a macro that will take the amount in column B, distribute it over the number of months that you see in column C, all of which originates at the corresponding month that matches with the period you see in column A.

I have attached an excel spreadsheet that provides a better understanding of what I am looking for.

Thank you very much! :)

A B C D E F G
PeriodAmountMonthsApr-16May-16Jun-16Jul-16
2016-063000036
2016-06400001
2016-06500001
2016-06600001
2016-06700001
2016-058000012
2016-05900000
2016-051000000
2016-051100001
2016-051200000
2016-051300001
2016-041400000
2016-041500000
2016-041600000

<colgroup><col><col><col><col><col span="3"></colgroup><tbody>
</tbody>
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
Hi Karen,

Can you please post a sample of what you wish the result to look like.

In addition can you confirm that column 1 is a date formatted cells and not a text field, likewise for that dates shown on Row 1

Do you have any objection if this is done by formula instead of VBA. Both are options?

Regards

Excelginge
 
Last edited:
Upvote 0
Hi Karen,

Is this what you are looking for?


ABCDEFGHIJKL
101/Apr/201631/May/2019
2
3PeriodAmountMonthsEnd Date2016-042016-052016-062016-072016-082016-092016-102016-11
42016-06300003631/May/2019833.33833.33833.33833.33833.33833.33
52016-0640000130/Jun/201640,000.00
62016-0650000130/Jun/201650,000.00
72016-0660000130/Jun/201660,000.00
82016-0670000130/Jun/201670,000.00
92016-05800001230/Apr/20176,666.676,666.676,666.676,666.676,666.676,666.676,666.67
102016-0590000030/Apr/2016
112016-05100000030/Apr/2016
122016-05110000131/May/2016110,000.00
132016-05120000030/Apr/2016
142016-05130000131/May/2016130,000.00
152016-04140000031/Mar/2016
162016-04150000031/Mar/2016
172016-04160000031/Mar/2016

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Excelginge
 
Upvote 0
Hi Excelginge,

First off thank you very much for your assistance. Yes, this is something that I am looking for, however, if there is a zero in the months column, rather than it being entirely blank, I would like the value to be posted to the next date.

For example, if we take a look at row 10. Instead of it being blank, I would like it to be posted to 2016-06. The only question I have is the "end date" column, is that just the Period + the Months? If so that's fine I could hide that no?

Also, I have no real preference as to whether this is done using VBA or via formula. Whichever one works the easiest is fine with me. The only thing is that I would like to be able to continuously add data to this and have it populate the same way that you have demonstrated.

Once again thank you very much for your assistance!
 
Upvote 0
excelginge said:
Hi Karen,

Is this what you are looking for?


ABCDEFGHIJKL
101/Apr/201631/May/2019
2
3PeriodAmountMonthsEnd Date2016-042016-052016-062016-072016-082016-092016-102016-11
42016-06300003631/May/2019833.33833.33833.33833.33833.33833.33
52016-0640000130/Jun/201640,000.00
62016-0650000130/Jun/201650,000.00
72016-0660000130/Jun/201660,000.00
82016-0670000130/Jun/201670,000.00
92016-05800001230/Apr/20176,666.676,666.676,666.676,666.676,666.676,666.676,666.67
102016-0590000030/Apr/2016
112016-05100000030/Apr/2016
122016-05110000131/May/2016110,000.00
132016-05120000030/Apr/2016
142016-05130000131/May/2016130,000.00
152016-04140000031/Mar/2016
162016-04150000031/Mar/2016
172016-04160000031/Mar/2016

<tbody>
</tbody>

Excelginge
Click to expand...

If you would like to get a better understanding, I have provided a screenshot: Imgur: The most awesome images on the Internet


This one has a little more logic to it. Some of these are things that have started in months past, and I want to be able to input the initial value (Column C) in the first cell matching to the corresponding period, followed by the remainder divided by the number of months (the same way that you have it).

Thank you again!
 
Upvote 0
Hi Karen,

Here is a formula way to achieve the same thing. I will leave it up to you to how you format your sheet as to the row your data starts.

Excel 2012
ABCDEFGH
1Min DateMax Date
22016-042019-05
3PeriodAmountMonthsEnd Date2016-042016-052016-062016-07
42016-06300003631/May/2019833.33833.33
52016-0640000130/Jun/201640,000.00
62016-0650000130/Jun/201650,000.00
72016-0660000130/Jun/201660,000.00
82016-0670000130/Jun/201670,000.00
92016-05800001230/Apr/20176,666.676,666.676,666.67
102016-0590000030/Apr/2016
112016-05100000030/Apr/2016
122016-05110000131/May/2016110,000.00
132016-05120000030/Apr/2016
142016-05130000131/May/2016130,000.00
152016-04140000031/Mar/2016
162016-04150000031/Mar/2016
172016-04160000031/Mar/2016

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1​

Worksheet Formulas
CellFormula
B2=MIN($A:$A)
C2=MAX($D:$D)
E3=$B$2
F3=IF($C$2>=EDATE($B$2,COLUMNS($E$3:E$3)),EDATE($B$2,COLUMNS($E$3:E$3)),"")
G3Drag the formula in Cell F3 across to the right suggest as a starter all the way to column BQ extra as you think fit
H3
D4=IF(ISNUMBER($A4),EDATE($A4,$C4)-1,"") Drag this formula down to bottom row + say approx extra 500 rows extra as you think fit
E4=IFERROR(IF(AND(ISNUMBER(E$3),ISNUMBER($A4),E$3<$D4,E$3>=$A4)=FALSE,"",$B4/$C4),"")
F4Drag the formula in Cell E4 across to the right suggest as a starter all the way to column BQ extra as you think fit then drag the formula down to bottom row + say approx extra 500 rows extra as you think fit
G4
H4

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

This should give you the answer you want.

Don't forget that the sheet is now dynamic and will add column data say if the end date changes.


Excelginge
 
Upvote 0
KarenMichael1 said:
Hi Excelginge,

First off thank you very much for your assistance. Yes, this is something that I am looking for, however, if there is a zero in the months column, rather than it being entirely blank, I would like the value to be posted to the next date.

For example, if we take a look at row 10. Instead of it being blank, I would like it to be posted to 2016-06. The only question I have is the "end date" column, is that just the Period + the Months? If so that's fine I could hide that no?

Also, I have no real preference as to whether this is done using VBA or via formula. Whichever one works the easiest is fine with me. The only thing is that I would like to be able to continuously add data to this and have it populate the same way that you have demonstrated.

Once again thank you very much for your assistance!
Click to expand...


This email only just appeared.

Change formula in following cells as follows

Excel 2012
ABCDEFGHI
1Min DateMax Date
22016-042019-05
3PeriodAmountMonthsEnd Date2016-042016-052016-062016-072016-08
42016-06300003631/May/20190.000.00833.33833.33833.33
52016-0640000130/Jun/20160.000.0040,000.000.000.00
62016-0650000130/Jun/20160.000.0050,000.000.000.00
72016-0660000130/Jun/20160.000.0060,000.000.000.00
82016-0670000130/Jun/20160.000.0070,000.000.000.00
92016-05800001230/Apr/20170.006,666.676,666.676,666.676,666.67
102016-0590000031/May/20160.0090,000.000.000.000.00
112016-05100000031/May/20160.00100,000.000.000.000.00
122016-05110000131/May/20160.00110,000.000.000.000.00
132016-05120000031/May/20160.00120,000.000.000.000.00
142016-05130000131/May/20160.00130,000.000.000.000.00

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1​

Worksheet Formulas
CellFormula
B2
C2
E3
F3
G3
H3
I3
D4=IF(ISNUMBER($A4),EDATE($A4,IF($C4>0,$C4,1))-1,"")
E4=IFERROR(IF(AND(ISNUMBER(E$3),ISNUMBER($A4),E$3<$D4,E$3>=$A4)=FALSE,0,IF($C4>0,$B4/$C4,$B4/1)),0)
F4
G4
H4
I4

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Yes it would be updated every time you amend it, all you have to do is make sure that the formulas extend far enough for the extra data.


Good luck





Excelginge
 
Upvote 0
excelginge said:
This email only just appeared.

Change formula in following cells as follows

Excel 2012
ABCDEFGHI
1Min DateMax Date
22016-042019-05
3PeriodAmountMonthsEnd Date2016-042016-052016-062016-072016-08
42016-06300003631/May/20190.000.00833.33833.33833.33
52016-0640000130/Jun/20160.000.0040,000.000.000.00
62016-0650000130/Jun/20160.000.0050,000.000.000.00
72016-0660000130/Jun/20160.000.0060,000.000.000.00
82016-0670000130/Jun/20160.000.0070,000.000.000.00
92016-05800001230/Apr/20170.006,666.676,666.676,666.676,666.67
102016-0590000031/May/20160.0090,000.000.000.000.00
112016-05100000031/May/20160.00100,000.000.000.000.00
122016-05110000131/May/20160.00110,000.000.000.000.00
132016-05120000031/May/20160.00120,000.000.000.000.00
142016-05130000131/May/20160.00130,000.000.000.000.00

<tbody>
</tbody>
Sheet1​

Worksheet Formulas
CellFormula
B2
C2
E3
F3
G3
H3
I3
D4=IF(ISNUMBER($A4),EDATE($A4,IF($C4>0,$C4,1))-1,"")
E4=IFERROR(IF(AND(ISNUMBER(E$3),ISNUMBER($A4),E$3<$D4,E$3>=$A4)=FALSE,0,IF($C4>0,$B4/$C4,$B4/1)),0)
F4
G4
H4
I4

<tbody>
</tbody>

<tbody>
</tbody>

Yes it would be updated every time you amend it, all you have to do is make sure that the formulas extend far enough for the extra data.


Good luck





Excelginge
Click to expand...

Hi Exelginge,

Apologies for the late response. Thank's again for all of the assistance, you've saved me hundreds of hours over the course of the year with this!
 
Upvote 0
You must log in or register to reply here.

Similar threads

airforceone
  • Solved
Search Row 1 for Text if Found Convert Column to Date
2
Replies
11
Views
796
airforceone
airforceone
M
  • Question
HELP IDENTIFY ORIGINAL START DATE BASED ON CONTINOUS SERVICE RULES
Replies
0
Views
361
mrshuck
M
J
  • Solved
Add 1 day to a date within a range as long as 2 conditions are met [without macros]
Replies
4
Views
586
juanam
J
A
  • Solved
Macro getting stuck on average when no data is present
Replies
5
Views
257
amphead
A
C
  • Question
Subtotals and counta I think
Replies
1
Views
557
t0ny84
T

Forum statistics

Threads
1,215,504
Messages
6,125,183
Members
449,212
Latest member
kenmaldonado

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