Subtracting Values from a Ranked List

[Mal]

I have a column of costs that I have ranked from highest to lowest. (1 being
lowest)
The column is totaled $8
I have a Spend of $4
I want to reduce the total from $8 to $4 by subtracting the lowest cost
items until the column total = the Spend.
e.g.
Spend $4
Cost Rank Result
$1 3 $0
$3 1 $3
$2 2 $1
$1 5 $0
$1 4 $0
--- -----
$8 $4
--- -----

Ant help appreciated.


Mal

 

[Mal]

Anyone?
I'm really struggling with this.
Thanks,
Mal

 

[Mal]

Sorry Ron.
What I want to do is reduce the "Cost" $8 to the "Spend" $4 by reducing the
lowest cost items on the list. That is why I have ranked the list.
So in this example the difference between the Cost and the spend is $4 so we
reduce the three $1 items to $0 and take $1 from the $2 item reducing it to
$1.
We then end up with the $3 item unchanged, the $2 item reduced to $1 and
each of the $1 items reduced to $0 with the Spend $4.
One point. If the ranking is equal as the 3 x $1 items in the example are,
and only $2 needed to be deducted, it is does not matter which $1 items are
deleted to end up at the Spend amount.
Hope this clarifies.
Thanks,
Mal

 

[Mal]

OK . Well I went down the VBA route and the code I ended up with is shown
below.
It seems to work. So I am happy.
I don't really know the VBA code so if there is a better VBA alternative, I
am happy to use it.
Thanks for the help.
Mal


Sub Test()

Set rng1 = Range("c5:c9"): 'Cost range
Set rng2 = Range("d5:d9"): 'Rank Cost
Set rng3 = Range("e5:e9"): 'Revised Cost
Set rng4 = Range("b2"): 'Spend
Set rng5 = Range("c11"): ' Total "Cost"
MaxVal = Application.Max(rng2)
aa = rng4.Value: ' Spend
bb = rng5.Value: ' Total "Cost"
cc = bb - aa
rng3.ClearContents
dd = MaxVal

For i = 1 To dd
ee = rng2(i).Value
If ee <> MaxVal Then GoTo Counter

For j = i To i

ff = rng1(j).Value: gg = rng2(j).Value: hh = rng3(j).Value

hh = ff

Do While hh > 0 And cc > 0
hh = hh - 1: cc = cc - 1
Loop
MaxVal = MaxVal - 1

rng3(j).Value = hh
i = 0
Next j
Counter:
Next i

End Sub

 

[Mal]

Thanks Ron.

Mal

Perfect is the enemy of good enough. If it is working and suits your
needs, you're done.