Sum and Allocate Amounts to first row of key with dups. only

[ISW]

Hi. Another question for you guys.


For key 1, for example, the total value is 19, and the max allocation value is 10. So instead of how the values are currently entered (column C), I need to change to an allocation of 10 for the first row only.

For key 2, the total value is 19, but the max allocation value is 12. So in this case, I'd need to have the value be 12 in the first row only.

Long story short, I need to sum up the values for each key, then distribute them based on the allocation values in column B but only to the first row. I am going to have to be doing this with large sheets of data and I don't want to improve my 10 key.

 

[shadow12345]

are the keys always in order? and does the amount to be depleted = the maxium value for d?

 

[ISW]

yes and yes

 

[shadow12345]

In that case D should be =IF(A2<>A1,IF(SUMIF($A$2:$A$10,A2,$C$2:$C$10)<B2,SUMIF($A$2:$A$10,A2,$B$2:$B$10),B2),"")



Then copy down

* can't display the formula.... hang on

 

[shadow12345]

Not sure what is going on here... I can't display the formula correclty. ARGGGGGGGG

 

[ISW]

I was wondering if I was missing something. Although, I don't see the issue. I'm going to keep at it.

 

[shadow12345]

=if(a2<>a1,if(sumif($a$2:$a$10,a2,$c$2:$c$10) < b2,sumif($a$2:$a$10,a2,$b$2:$b$10),b2),"")


Ha, got it... stick that in D2 and copy down

 

[ISW]

You are the man.