# Extract data on 1st occurrence date comparison

#### alexorfa

##### New Member
 A B 1 3-Jan-17 6,500 2 3-Feb-17 8,000 3 16-Feb-17 9,000 4 3-Mar-17 10,000 5 3-Apr-17 9,500 6 3-May-17 9,000 7 3-Jun-17 8,500 8 3-Jul-17 8,000 9 3-Aug-17 7,500 10 3-Sep-17 7,000

<tbody>
</tbody>

<tbody>
</tbody>

I have a series of irregular payments that are listed on column A (date) and B (amount).

I also have a series of regular payments (E) that become effective on specific dates (D) EVERY year.

 D E F 1 02-Jan -5,000 H2 Interest (of pr year) 2 05-Jan -1,000 Envelope fee 3 02-Jul -5,000 H1 interest 4 5 16-Jan -10,000 Capital payment 6 16-Mar -10,000 Capital payment 7 16-Jul -10,000 Capital payment 8 16-Oct -10,000 Capital payment

<tbody>
</tbody>

I am looking for a formula (G) that will ‘sum’ the two tables (payments & Expenses) based on the closest payment date following the expense date. So an expense charge on 2-Jan will be subtracted from the next payment of 3-Jan-17 and Col G1 will show a value of 1,500 (6,500-5,000).

Also, when the expense is larger than the next payment then 0 will show in col G and the negative balance will be subtracted from the following payment. For example, payment B2 of 8,000 on 3-Feb-17 is not enough to cover the expenses that became due on 5-Jan (-1,000) and 16-Jan (-10,000). So G2=0 and G3=-3,000+9,000=6,000.

Note that the series runs for payments and expenses over a period of 30+ years.

I have tried hard to come up with a formula that will single out the first payment date after the relevant expense date, store the difference if negative and carry it to the next payment amount (also taking into consideration that since payment dates are irregular u may end up with 1 payment date having to cover for 2 or more expense dates). No luck. I am a good excel user and understand complex functions if any of you can come up with the formula that will make this work.

Thanks a lot
Alexander

PS. I have deepened my knowledge of excel by viewing posts in this forum and I thank u all very much.

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Replies
1
Views
64
Replies
8
Views
116
Replies
6
Views
122
Replies
2
Views
258
Replies
2
Views
147

1,127,617
Messages
5,625,883
Members
416,141
Latest member
Bartek9q

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

### Which adblocker are you using?

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

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