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 table below

ITEM (ColumnA)</SPAN> <TBODY> </TBODY>	TRANSACTION_DATE(ColumnB)</SPAN> <TBODY> </TBODY>	Sum of Value (ColumnD)</SPAN> <TBODY> </TBODY>	Maximum for Each Item (ColumnE)</SPAN> <TBODY> </TBODY>	Value Expected (</SPAN>ColumnF) <TBODY> </TBODY>
0433AT62A0020^JOHANSON-CSP1</SPAN> <TBODY> </TBODY>	11-Jul-2012 11:59:59 PM</SPAN> <TBODY> </TBODY>	603.84</SPAN> <TBODY> </TBODY>	905.75</SPAN> <TBODY> </TBODY>	603.84</SPAN> <TBODY> </TBODY>
0433AT62A0020^JOHANSON-CSP1</SPAN> <TBODY> </TBODY>	24-Jul-2012 11:59:59 PM</SPAN> <TBODY> </TBODY>	905.75</SPAN> <TBODY> </TBODY>	905.75</SPAN> <TBODY> </TBODY>	301.92</SPAN> <TBODY> </TBODY>
0685610014^MOLEX-DFC1</SPAN> <TBODY> </TBODY>	30-Jul-2012 11:59:59 PM</SPAN> <TBODY> </TBODY>	25.45</SPAN> <TBODY> </TBODY>	25.45</SPAN> <TBODY> </TBODY>	25.45</SPAN> <TBODY> </TBODY>
0685610014^MOLEX-DFC1</SPAN> <TBODY> </TBODY>	30-Aug-2012 11:59:59 PM</SPAN> <TBODY> </TBODY>	63.63</SPAN> <TBODY> </TBODY>	25.45</SPAN> <TBODY> </TBODY>	0.00</SPAN> <TBODY> </TBODY>
100BN.HMWG 909752^INTEL_MIC</SPAN> <TBODY> </TBODY>	16-Jul-2012 11:59:59 PM</SPAN> <TBODY> </TBODY>	6,647.30</SPAN> <TBODY> </TBODY>	6,647.30</SPAN> <TBODY> </TBODY>	6,647.30</SPAN> <TBODY> </TBODY>
100BN.HMWG 909752^INTEL_MIC</SPAN> <TBODY> </TBODY>	10-Aug-2012 11:59:59 PM</SPAN> <TBODY> </TBODY>	6,647.30</SPAN> <TBODY> </TBODY>	6,647.30</SPAN> <TBODY> </TBODY>	0.00</SPAN> <TBODY> </TBODY>
100-CG2266^AMD</SPAN> <TBODY> </TBODY>	26-Jul-2012 11:59:59 PM</SPAN> <TBODY> </TBODY>	39.87</SPAN> <TBODY> </TBODY>	29.24</SPAN> <TBODY> </TBODY>	29.24</SPAN> <TBODY> </TBODY>
100-CG2266^AMD</SPAN> <TBODY> </TBODY>	23-Aug-2012 11:59:59 PM</SPAN> <TBODY> </TBODY>	39.87</SPAN> <TBODY> </TBODY>	29.24</SPAN> <TBODY> </TBODY>	0.00</SPAN> <TBODY> </TBODY>

<TBODY>
</TBODY>

Thanks in Advance.​

 

[rallcorn]

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))

 

[gurusanthanam]

Hi,

Thanks a ton
It worked !!!!!!!!!!!!
Excellent...great work.

I am trying to understand the formula
I hope you added 0.01 in formula for avoiding rounding issues.

Thanks once again.

 

[rallcorn]

FYI, in your example the first item the amount you have "allocated" between the 2 rows is 905.76 (which is 1 cent more than the amout you say can be allocated).

The $ is used to anchor the first segment but leave the 2nd segment dynamic - i.e. to get a "running" total from row 2 through whatever the current row number is.

I can walk you through the formula, and will do so, but have you ever used Formula Auditing/Evaluate formula ? It is a handy little gadget that steps you through each calculation in the order in which it is approached by the formula. You can really learn alot by seeing how each piece is evaluated.

IF(SUMIF($A$2:A2,A2,$C$2:C2) sums column C through current row for all items that match column A item in current row
< D2+0.01,C2 if the sum is less the maximum +.01 then use the item value (+.01 is used because if we just used < maximum item values EQUAL to the maximum would be ignored)

So if the sum of all same items is NOT less than maximum we have to determine how much of the maximum has already been used:
IF((SUMIF($A$2:A2,A2,$C$2:C2)-C2) < D2+0.01 so we take the sum and subtract back off the current row's amt and check to see if it is less than or equal to the maximum
If the maximum has NOT already been used, then decide how much remains and use it D2-(SUMIF($A$2:A2,A2,$C$2:C2)-C2)
Otherwise (i.e. the maximum HAS already been used) enter ZERO