Posts by samaria

    Hi Carim,
    I have changed the variables, but the cells where the negative values still remain after offsetting the positive value still retain the issue. I have also noticed that the issue affects those particular cells, even if you move them within the worksheet. So, for all the other rows, the macro is perfect, but for particular cells, if a pre-payment completely offsets a positive value (receivable), that cell will retain the difference between the pre-payment and the receivable, instead of having the value zero

    Hi Carim,


    Many thanks for you assistance, I really appreciate. The macro works fantastically. However, upon application of the formula to a much larger database, some cells still retain the negative (pre-payment) values, although those pre-payments have totally cleared the debt in that cell (please refer to the message inboxed)

    Hi Carim,


    Many thanks for your assistance. It's greatly appreciated.


    However, there is a slight problem. The formula should be dynamic, so that it checks the whole row for any negative numbers. Any negative number (pre-payment) offsets the oldest positive debt. If the pre-payment is greater than the oldest debt, then the remaining pre-payment offsets the next oldest debt, and so on, until all pre-payments are exhausted


    I have attached the file with the comment and an example, hopefully to make it clearer.


    Regards [ATTACH]n1216073[/ATTACH]

    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"]

    [tr]


    [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]

    [/tr]


    [tr]


    [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]

    [/tr]


    [tr]


    [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]

    [/tr]


    [tr]


    [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]

    [/tr]


    [tr]


    [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]

    [/tr]


    [tr]


    [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]

    [/tr]


    [tr]


    [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]

    [/tr]


    [/TABLE]

    Required results [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 446"]

    [tr]


    [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]

    [/tr]


    [tr]


    [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]

    [/tr]


    [tr]


    [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]

    [/tr]


    [tr]


    [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]

    [/tr]


    [tr]


    [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]

    [/tr]


    [tr]


    [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]

    [/tr]


    [tr]


    [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]

    [/tr]


    [/TABLE]

    Regards