ExcelBanter - Please-please-HELP!!! Need to resolve this

I think you posted this before, you must not have liked my suggestions.
However, here is another, a macro. It assumes that you data starts in C6. You
can type A-D in cells A1 to A4 Totals allocated for each are placed in column
B, the order count for each in column D, and Column D of the data shows which
sales-person is allocated the order.

Sub TBonus()
Dim sumA As Double, sumB As Double, sumC As Double, sumD As Double
Dim countA As Long, countB As Long, countC As Long, countD As Long
Dim nr As Long, c As Variant
Dim rng As Range

Count = 4
nr = Range("c6").CurrentRegion.Rows.Count
Set rng = Range(Cells(10, 3), Cells(nr, 3))
'set the 4 teams basic amounts
sumA = CCur(Cells(6, 3)): sumB = CCur(Cells(7, 3)): sumC = CCur(Cells(8,
3)): sumD = Cells(9, 3)
countA = 1: countB = 1: countC = 1: countD = 1
Cells(6, 4) = "A": Cells(7, 4) = "B": Cells(8, 4) = "C": Cells(9, 4) = "D"

'find the rest of the values
For i = 10 To nr
If CCur(sumA) < CCur(sumB) And CCur(sumA) < CCur(sumC) And CCur(sumA)
< CCur(sumD) Then
sumA = sumA + CCur(Cells(i, 3))
countA = countA + 1
Cells(i, 4).Value = "A"
ElseIf sumB < sumA And sumB < sumC And sumB < sumD Then
sumB = sumB + CCur(Cells(i, 3))
countB = countB + 1
Cells(i, 4).Value = "B"
Debug.Print countB
ElseIf sumC < sumA And sumC < sumB And sumC < sumD Then
sumC = sumC + CCur(Cells(i, 3))
countC = countC + 1
Cells(i, 4).Value = "C"
ElseIf sumD < sumA And sumD < sumB And sumD < sumC Then
sumD = sumD + CCur(Cells(i, 3))
countD = countD + 1
Cells(i, 4).Value = "D"
End If
Next
Cells(1, 2).Value = sumA: Cells(2, 2).Value = sumB: _
Cells(3, 2).Value = sumC: Cells(4, 2).Value = sumD
Cells(1, 4).Value = countA: Cells(2, 4).Value = countB: _
Cells(3, 4).Value = countC: Cells(4, 4).Value = countD
Cells(1, 1).Select
End Sub

Perhaps this will suit you better.
Peter

"Chunkey Pandey" wrote:

I have a very pressing need to find a solution to this problem below. Any
help towards this is very much appreciated.

I am having trouble finding a formula that will allocate a number to
various €œbuckets€ based on the seniority of these buckets. Let me exemplify:

I have operating cash flows of $200

Most senior bucket - 1 - Obligation A: $70
Second senior bucket - 2 - Obligation B: $180
Second senior bucket - 2 - Obiigation C: $60

So, the formula should allocate $200 by order of seniority -- allocating $70
to Obligation A since it is most senior. Then the remainder $130 (i.e $200 -
$70) should get allocated to second most senior. Since both Oblig B and C
are equally senior, remainder gets allocated to both equally or to the max of
the obligation. Which means that B will get $70 and C will get $60.

I should also mention that the seniority of obligation is not cell dependent
- i.e. the most senior obligation will not always occur in the first cell or
any one particular cell. All obligations will be in entered and then user
can assign 1 through "n" against the obligation to define which is senior - 1
being the most senior. The following may clarify as well (note B, C, and D
are all equally senior). Any help will be much appreciated! Thanks

Amount Available for distribution $200

Rank* Name Obligation Amt Paid Formula
1 A $70 $70 ?
2 B $40 $40 ?
2 C $55 $55 ?
3 D $30 $5 ?
2 E $30 $30 ?

* User input, random sequence, not dependent on cell €“ i.e. most senior
obligation can occur at the very end of table, for example.