Hi,
Am seeking assistance on an Excel macro. The issue is where an entity has debts as well as credits (pre-payments) which have different age profiles. In essence, the pre-payments should go towards clearing the oldest debt first. The screenshot below illustrates this (where pre-payments are the negative amounts). For instance, for Client X below, the prepayments in the 90-180 days and 181-365 days should clear the debt over 365 days. Any ideas will be of great help
Original data [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 446"]
[TD="width: 65"]Client[/TD]
[TD="width: 105"]0-30 days[/TD]
[TD="width: 80"]31-90 days[/TD]
[TD="width: 84"]91 - 180 days[/TD]
[TD="width: 92"]181 - 365 days[/TD]
[TD="width: 84"]>365[/TD]
[TD="width: 84"]Total due[/TD]
X
[/td]
[TD="class: xl63"] $ 30,000.00[/TD]
[TD="class: xl63"] $25,000.00[/TD]
[TD="class: xl63"] $-20,000.00[/TD]
[TD="class: xl63"] $ -5,000.00[/TD]
[TD="class: xl63"] $ 34,900.00[/TD]
[TD="class: xl63"] $ 64,900.00[/TD]
Y
[/td]
[TD="class: xl63"] $ - [/TD]
[TD="class: xl63"] $ - [/TD]
[TD="class: xl63"] $ -4,500.00[/TD]
[TD="class: xl63"] $ -6,000.00[/TD]
[TD="class: xl63"] $ - [/TD]
[TD="class: xl63"] $-10,500.00[/TD]
Z
[/td]
[TD="class: xl63"] $ 4,500.00[/TD]
[TD="class: xl63"] $ 5,790.00[/TD]
[TD="class: xl63"] $ 9,000.00[/TD]
[TD="class: xl63"] $ 2,000.00[/TD]
[TD="class: xl63"] $ - [/TD]
[TD="class: xl63"] $ 21,290.00[/TD]
A
[/td]
[TD="class: xl63"] $ - [/TD]
[TD="class: xl63"] $ - [/TD]
[TD="class: xl63"] $ -4,550.00[/TD]
[TD="class: xl63"] $ 20,000.00[/TD]
[TD="class: xl63"] $-23,500.00[/TD]
[TD="class: xl63"] $ -8,050.00[/TD]
B
[/td]
[TD="class: xl63"] $ 7,000.00[/TD]
[TD="class: xl63"] $ 4,500.00[/TD]
[TD="class: xl63"] $ - [/TD]
[TD="class: xl63"] $ 9,000.00[/TD]
[TD="class: xl63"] $ -8,500.00[/TD]
[TD="class: xl63"] $ 12,000.00[/TD]
[TD="class: xl64"]Total[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl65"] $ 79,640.00[/TD]
[/TABLE]
Required results [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 446"]
[TD="width: 65"]Client[/TD]
[TD="width: 105"]0-30 days[/TD]
[TD="width: 80"]31-90 days[/TD]
[TD="width: 84"]91 - 180 days[/TD]
[TD="width: 92"]181 - 365 days[/TD]
[TD="width: 84"]>365[/TD]
[TD="width: 84"]Total due[/TD]
X
[/td]
[TD="class: xl65"] $ 30,000.00[/TD]
[TD="class: xl65"] $25,000.00[/TD]
[TD="class: xl65"] $ - [/TD]
[TD="class: xl65"] $ - [/TD]
[TD="class: xl65"] $ 9,900.00[/TD]
[TD="class: xl65"] $ 64,900.00[/TD]
Y
[/td]
[TD="class: xl65"] $ - [/TD]
[TD="class: xl65"] $ - [/TD]
[TD="class: xl65"] $ -4,500.00[/TD]
[TD="class: xl65"] $ -6,000.00[/TD]
[TD="class: xl65"] $ - [/TD]
[TD="class: xl65"] $-10,500.00[/TD]
Z
[/td]
[TD="class: xl65"] $ 4,500.00[/TD]
[TD="class: xl65"] $ 5,790.00[/TD]
[TD="class: xl65"] $ 9,000.00[/TD]
[TD="class: xl65"] $ 2,000.00[/TD]
[TD="class: xl65"] $ - [/TD]
[TD="class: xl65"] $ 21,290.00[/TD]
A
[/td]
[TD="class: xl65"] $ - [/TD]
[TD="class: xl65"] $ - [/TD]
[TD="class: xl65"] $ -4,550.00[/TD]
[TD="class: xl65"] $ -3,500.00[/TD]
[TD="class: xl65"] $ - [/TD]
[TD="class: xl65"] $ -8,050.00[/TD]
B
[/td]
[TD="class: xl65"] $ 7,000.00[/TD]
[TD="class: xl65"] $ 4,500.00[/TD]
[TD="class: xl65"] $ - [/TD]
[TD="class: xl65"] $ 500.00[/TD]
[TD="class: xl65"] $ - [/TD]
[TD="class: xl65"] $ 12,000.00[/TD]
[TD="class: xl66"]Total[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl67"] $ 79,640.00[/TD]
[/TABLE]
Regards