No subtotaling but adjusting customers accounts between two months

Hi All,

Here is my task, company has many clients with their accounts operating for many credit and debit transactions. The sheet i provided is of form adjusting those debit and credits between company (me) and clients.

I have sheet of client details with amount i owe them and they owe me...

here no immediate settlments happen... what i do is in month of september sheet with details owe and pay details of that month, i do is just adjust the august months owe and pay amount in september and it will carry on month by month....

So i created formula to carry out, but it not working perfectly.. Hence i request this forum great excelers help....

Please Login or Register  to view this content.

Please find attachment with all neccesary details.... If am not clear please do come back...

Thanks for your time and consideration.

Waiting...Any help is appreciated... Thanks...

Why does your design require you to consolidate the data from two sources on one of the two sources?

It would be considerably easier to make a third sheet and pull both as data sources, and consolidate there. What's the reason for showing all three lines?

Hello daffodil11,

Thanks for time....

Yeah the way of approach to use a third sheet and consolidating both is fine... ultimately we need results right...

The reason for three lines and separate entries for each account is because, each customer has single account number but their use of our service (product is different), in data product is not mentioned but the corresponding credit and debit is provided. ( Its not so necessary, but its management requirement for having three lines for single account, even i have 5 lines for an account, in sample i provided less)....

So we need something closer to actual data. I'll make up some products and attach.

Your math is throwing me off. You say it should be adjusted as (30, 5, 0) but 55-35 = 20.

Hi daffodil11,

Thanks for your kind help....will be waiting eagerly for the same.

the math calculation i said is, in august einstein owe me -35$ and in september i owe him 55$, hence i will adjust in with my debt and now i owe him 20$ which i will adjust in month of october....

Thanks for your precious time...

please come back if you need any information regarding this....

Okay, I understand all of the logic.

You want to sequentially deduct the total sum from each account line until the sum of the backpage is zero.

Sheet2: 35

Sheet1:

30 - 30 = 0

10 - 5 = 5

15 - 0 = 15

Almost done.

Try this in F2, then copied down:

=MIN(MAX(0, C2 + SUMIF($B$1:$B1, B2, $C$1:$C1)+SUMIF(T:T, B2,U:U )), C2)

or

=MIN(MAX(0, C2 + SUMIF($B$1:$B1, B2, $C$1:$C1)+SUMIF(Sheet2!B:B, B2,Sheet2!C:C )), C2)

Hi JBeaucaire,

Thanks but its not deducting sequentially.... i want to adjust each name each line item amount to be adjusted by that names sub total amount in sheet 2....

if it can't adjust then it should put comment as not adjusted and if partially adjusted, then comment as partially adjusted...

it was mind cracking for me.... but, friends like you and daffodil are well exposed in excel.... pls help....am waiting....

Hi Daffodil.... am waiting...

Actually, JBeaucaire's formula does what you need. He just gave you the final product, instead of the only the middle step.

In F2:

=MIN(MAX(0, C2 + SUMIF($B$1:$B1, B2, $C$1:$C1)+SUMIF(T:T, B2,U:U )), C2)-E2

and copy down

This will give you the sequential numbering.

Thanks daffodil...solving the post...