Amount to be allocated but with Upper Limit

[gurusanthanam]

Hi All,

I have to allocate a specified amount (column E) for each Item(Column A) based on column D.
Amount to be allocated cannot exceed the total amount specified for each Item in Column E.

I have to maintain the same order in which the data is displayed in sample file. Because, the data has been sorted by Transaction date.
I have to allocate the amount which has been transacted first based on transaction date.

I am expecting a formulae to get the Values as in Column F. (i have manually filled up Column F, but i have 13000 Rows for which same calculation has to be done for different Items).

Please do not mind the repeated values in Column E. For one Item (column A) there is always only one Maximum value that can be allocated.

Sorry i could explain in words much. Please refer to the example in the file attached.

Thanks in Advance.

[JieJenn]

Ignore my answer. Didn't read the question thoroughly.

Enter with Ctrl + Shift + Enter together

=MAX(IF($A$2:$A$36=A2,$D$2:$D$36))

[gurusanthanam]

Hi All,

Any idea how to solve this ?

[gurusanthanam]

I got an update from Mr.Excel forum. (by Mr.Rallcorn). The formula is working fine now.

Just updating here for the benefit of all. thanks a lot.

try this in F2 (note placement of dollar signs!), then copy down in column F
=IF(SUMIF($A$2:A2,A2,$C$2:C2) < D2+0.01,C2,IF((SUMIF($A$2:A2,A2,$C$2:C2)-C2) < D2+0.01,D2-(SUMIF($A$2:A2,A2,$C$2:C2)-C2),0))

[Cutter]

In order to comply with forum rules you should have told us that you had posted your question in another forum(s).

http://www.mrexcel.com/forum/excel-q...ml#post3261540

Here is the rule followed by the reason for it:

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).


Also:
Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself.

[gurusanthanam]

Hi,

I understand your point. thanks.
In this case:-
I waited for long time to get a reply.
It looked like no one was working on it and no. of views remained static,though i replied once to bring the thread back into action.
Hence, i had to post in other forum.

Regards,
guru